ords.enable_schema fails with “ORA-06598: insufficient INHERIT PRIVILEGES privilege”

This is issue I always hit settitng up a test environment using Oracle REST Data Services (ORDS).  Using SYSTEM user to call ords.enable_schema throws ORA-06598.  According to the documentation it should succeed (note the SYSTEM user has DBA role) .

Only database users with the DBA role can enable or disable a schema other than their own.

[oracle@localhost ~]$ sql system/oracle

SQLcl: Release 19.1 Production on Sat Feb 15 02:20:33 2020
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Last Successful login time: Sat Feb 15 2020 02:20:36 -05:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SHOW USER
USER is "SYSTEM"

SQL> select role from session_roles where role = 'DBA';
   ROLE
_______
DBA
SQL> BEGIN
  2     ords.enable_schema(
  3        p_enabled             => TRUE,
  4        p_schema              => 'HR',
  5        p_url_mapping_type    => 'BASE_PATH',
  6        p_url_mapping_pattern => 'hr',
  7        p_auto_rest_auth      => FALSE);
  8
  9    COMMIT;
 10  END;
 11  /
BEGIN
*
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "ORDS_METADATA.ORDS", line 1
ORA-06512: at line 2

The solution is simple, grant inherit privileges on the current user (SYSTEM) to the ORDS_METADATA user.

SQL> show user
USER is "SYSTEM"
SQL> grant inherit privileges on user SYSTEM to ORDS_METADATA;
Grant succeeded.

Once this is completed, the call to ords.enable_schema is successful.

SQL> BEGIN
2 ords.enable_schema(
3 p_enabled => TRUE,
4 p_schema => 'HR',
5 p_url_mapping_type => 'BASE_PATH',
6 p_url_mapping_pattern => 'hr',
7 p_auto_rest_auth => FALSE);
8
9 COMMIT;
10 END;
11 /

PL/SQL procedure successfully completed.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s