How do I manage user privileges and roles in my Amazon RDS for Oracle DB instance?

Short description

Because Amazon RDS is a managed service, you can't use SYS and SYSTEM users by default.

For the list of roles and privileges that the RDS for Oracle database grants to the master user, see Master user account privileges. For a list of privileges that RDS for Oracle database doesn't grant to the database administrator (DBA) role, see Limitations for Oracle DBA privileges.

Resolution

To grant privileges on SYS objects in Amazon RDS, use the rdsadmin.rdsadmin_util.grant_sys_object Amazon RDS procedure. The procedure grants only privileges that the master user is already granted.

To grant SELECT privileges on the V_$SQLAREA object to the EXAMPLE-USERNAME user, log in as the RDS master user. Then, run the following command:

EXECUTE rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name  => 'V_$SQLAREA',p_grantee   => 'EXAMPLE-USERNAME', p_privilege => 'SELECT');

To grant SELECT privileges on the V_$SQLAREA object to the EXAMPLE-USERNAME user with the grant option, run the following command:

EXECUTE rdsadmin.rdsadmin_util.grant_sys_object( p_obj_name  => 'V_$SQLAREA',p_grantee   => 'EXAMPLE-USERNAME', p_privilege => 'SELECT', p_grant_option => true);

Note: Unless you created the user with a case-sensitive identifier, use uppercase to define all parameter values.

To grant the SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE roles to the EXAMPLE-USERNAME user with the admin option, run the following queries:

SQL> GRANT SELECT_CATALOG_ROLE TO EXAMPLE-USERNAME WITH ADMIN OPTION;
SQL> GRANT EXECUTE_CATALOG_ROLE TO EXAMPLE-USERNAME WITH ADMIN OPTION;

The EXAMPLE-USERNAME user can then grant access to SYS objects that are granted to SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE.

To view the grants that are associated with SELECT_CATALOG_ROLE, run the following queries:

SELECT type, owner, table_name, privilege, grantor, grantable FROM dba_tab_privs WHERE grantee = upper('SELECT_CATALOG_ROLE') UNIONSELECT 'SYS' AS type, NULL as owner, NULL as table_name, privilege, NULL, admin_option AS grantable FROM dba_sys_privs WHERE grantee = upper('SELECT_CATALOG_ROLE')
UNION
SELECT 'ROLE' AS type, NULL AS owner, NULL AS table_name, granted_role AS privilege, NULL, admin_option AS grantable FROM dba_role_privs WHERE grantee = upper('SELECT_CATALOG_ROLE')
ORDER BY type, owner, table_name, privilege;

For more information, see Granting SELECT or EXECUTE privileges to SYS objects.

To revoke privileges on a single object, use the rdsadmin.rdsadmin_util.revoke_sys_object RDS procedure.

To revoke SELECT privileges from EXAMPLE-USERNAME on V_$SQLAREA, run the following command:

EXECUTE rdsadmin.rdsadmin_util.revoke_sys_object( p_obj_name  => 'V_$SQLAREA', p_revokee   => 'EXAMPLE-USERNAME', p_privilege => 'SELECT');

For more information, see Revoking SELECT or EXECUTE privileges on SYS objects.

To troubleshoot, complete one of the following procedures for the issue that you're experiencing.

Reset the master user privileges

If you revoked the roles and privleges of the master user, then you can reset them. For more information, see How do I reset the admin user password for my Amazon RDS DB instance?

Grant RDS_MASTER_ROLE to non-master users

You can't grant RDS_MASTER_ROLE to non-master users. When you create the DB instance, SYS creates RDS_MASTER_ROLE by default. You can grant RDS_MASTER_ROLE only to the master user. To list users that you granted RDS_MASTER_ROLE to, run the following query:

SQL> SELECT * FROM sys.dba_role_privs WHERE granted_role = 'RDS_MASTER_ROLE';

Example output:

GRANTEE        GRANTED_ROLE        ADM      DEL     DEF     COM     INH
--------       ---------------     ---      ---     ---     ---     ---
MASTER         RDS_MASTER_ROLE     NO       NO      YES     NO      NO
SYS            RDS_MASTER_ROLE     YES      NO      YES     YES     YES

Because the master user doesn't have the admin option, you can't grant RDS_MASTER_ROLE to any other user.

For more information, see Granting privileges to non-master users.

Revoke privileges granted to PUBLIC

It isn't a best practice to revoke PUBLIC privileges to key DBMS_* and UTL_* packages because several Oracle applications are designed to rely on these privileges. The key DBMS_* and UTL_* packages include UTL_TCP, UTL_HTTP, HTTPURITYPE, UTL_INADDR, UTL_SMTP, DBMS_LDAP, DBMS_LOB, UTL_FILE, DBMS_ADVISOR, DBMS_OBFUSCATION_TOOLKIT, DBMS_BACKUP_RESTORE, and DBMS_SYS_SQL.

Resolve the "invalid schema" error when you create a role with a password

For example, in the following query, you use rdsadmin_util.grant_sys_object to create a role with password and grant privileges:

SQL> CREATE ROLE ROLE_NAME IDENTIFIED BY EXAMPLE-PASSWORD;
SQL> EXEC rdsadmin.rdsadmin_util.grant_sys_object('DBMS_JOB', 'ROLE_NAME');

In the query's output, you get the following error:

ORA-20199: Error in rdsadmin_util.grant_sys_object. 
ORA-44001: invalid schema
ORA-06512: at "RDSADMIN.RDSADMIN_UTIL", line 268
ORA-44001: invalid schema

To resolve this issue, create a role without a password:

-- Create a role without a password
ALTER ROLE ROLE_NAME NOT IDENTIFIED;

Resolve the "ORA-01031: insufficient privileges" error

The following are example use cases that can result in the ORA-01031 error.

You run the ALTER SYSTEM SET query

When you run the following query, you get the ORA-01031 error:

SQL> ALTER SYSTEM SET processes=200 scope=spfile;

Instead, modify the parameter values in a custom DB parameter group. You can't change the parameter values in a default DB parameter group.

You use a database trigger and your changes to the underlying table structure caused the trigger status to become INVALID

The next time the trigger is released, the implicit compilation of the trigger fails with the following error:

"ORA-04045: errors during recompilation/revalidation of SCOTT.ERROR_LOG_TRIGORA-01031: insufficient privileges"

To resolve this issue, explicitly grant the administer database trigger privilege to the trigger's owner so that the owner can alter the database trigger:

SQL> GRANT ADMINISTER DATABASE TRIGGER TO example-owner;
SQL> ALTER TRIGGER example-owner.log_errors_trig COMPILE;
Trigger altered.

You run a stored procedure

You get the ORA-01031 error because you can't use privileges that are acquired from roles in named stored procedures that run with definer's rights. You can use privileges acquired from roles in SQL Plus and anonymous PL/SQL blocks.

Example of a failed stored procedure:

SQL> CREATE USER EXAMPLE-USERNAME IDENTIFIED BY EXAMPLE-PASSWORD;SQL> GRANT connect, resource TO EXAMPLE-USERNAME

-- Connect as EXAMPLE-USERNAME
SQL> CREATE TABLE dept (deptno NUMBER, deptname VARCHAR2(30));
Table DEPT created.

-- Drop the table and try to create the same table using the stored procedure. When you run the procedure, you get the error ORA-01031.
SQL> DROP table DEPT;
SQL> CREATE OR REPLACE PROCEDURE test_proc AS
BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE DEPT (DeptNo number, DeptName varchar2(30))';
END;
/
Procedure TEST_PROC created
SQL> EXEC TEST_PROC
Error report -
ORA-01031: insufficient privileges

To resolve this issue, connect as the master user and explicitly grant the privileges.

Example of a successful stored procedure:

-- Connect as master user and grant the CREATE TABLE privilege.
SQL> GRANT CREATE TABLE TO test_user;
-- Connect as EXAMPLE-USERNAME
SQL> EXEC TEST_PROC
PL/SQL procedure successfully completed.

You don't grant the master user privileges with the grant option

If you don't grant the master user a privilege on an object with the grant option, then you get the ORA-01031 error. You also get this error when the master user tries to grant the privilege to another user.

Example of a failed grant:

SQL> SHOW USER;USER is "EXAMPLE-USERNAME"

SQL> SELECT * FROM SYS.DBA_TABLESPACE_USAGE_METRICS;

ERROR:
ORA-04043: object "SYS"."DBA_TABLESPACE_USAGE_METRICS" does not exist

-- Grant fails on the object DBA_TABLESPACE_USAGE_METRICS because master user is not granted the SELECT privilege on DBA_TABLESPACE_USAGE_METRICS with the grant option.

SQL> SHOW USER;
USER is "ADMIN"

SQL> GRANT SELECT ON SYS.DBA_TABLESPACE_USAGE_METRICS to EXAMPLE-USERNAME;
GRANT SELECT ON SYS.DBA_TABLESPACE_USAGE_METRICS to EXAMPLE-USERNAME
                    *
ERROR at line 1:
ORA-01031: insufficient privileges

To resolve this issue, explicitly grant the privilege to the master user with the grant option.

Example command:

SQL> EXECUTE  rdsadmin.rdsadmin_util.grant_sys_object(  p_obj_name => 'DBA_TABLESPACE_USAGE_METRICS', p_grantee => 'ADMIN', p_privilege => 'SELECT', p_grant_option => true);

SQL> GRANT SELECT ON SYS.DBA_TABLESPACE_USAGE_METRICS to EXAMPLE-USERNAME;

-- After the privileges are granted, connect to EXAMPLE-USERNAME and then query DBA_TABLESPACE_USAGE_METRICS.
SQL> SELECT * FROM SYS.DBA_TABLESPACE_USAGE_METRICS;

Related information

Configuring privilege and role authorization on the Oracle website