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 22.214.171.124.0 - Production Version 126.96.36.199.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.