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 Amazon RDS for Oracle database grants to the master user, see Master user account privileges. For a list of privileges that Amazon RDS for Oracle database doesn't grant to the database administrator (DBA) role, see Limitations for Oracle DBA privileges.

Resolution

Note: In the following sections, replace EXAMPLE-USERNAME with the username that you're granting privileges to or revoking privileges from.

Grant privileges

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

To grant the SELECT privilege on the V_$SQLAREA object to a 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 the SELECT privilege on the V_$SQLAREA object to a 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 a user with the admin option, run the following commands:

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

The user can then grant access to the same SYS objects that the SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE roles can.

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

SELECT type, owner, table_name, privilege, grantor, grantable FROM dba_tab_privs WHERE grantee = upper('SELECT_CATALOG_ROLE') UNION SELECT '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 the user on V_$SQLAREA, run the following commands:

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.

Reset the master user privileges

If you revoked the roles and privileges 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 the master user

You can't grant the RDS_MASTER_ROLE 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 command:

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 PUBLIC role privileges to key DBMS_* and UTL_* packages

It isn't a best practice to revoke PUBLIC role privileges to key DBMS_* and UTL_* packages because several Oracle applications rely on the 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, you used rdsadmin_util.grant_sys_object to create a role with password and grant privileges in the following commands:

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

In the command'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, run the following command to create a role without a password:

SQL> 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 command

The ORA-01031 error occurs when you run the following command:

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

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

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

The next time an event releases the trigger, 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, run the following command to 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;

Expected output:

Trigger altered.

You run a stored procedure

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

The following stored procedure fails because the user uses privileges from a role that's in a named stored procedure to create a table. The user then drops the table and tries to use the stored procedure to create the same table:

SQL> CREATE USER EXAMPLE-USERNAME IDENTIFIED BY EXAMPLE-PASSWORD;  
SQL> GRANT connect, resource TO EXAMPLE-USERNAME  
SQL> CREATE TABLE dept (deptno NUMBER, deptname VARCHAR2(30));  
Table DEPT created.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

The output shows the following error:

"Error report -ORA-01031: insufficient privileges"

To resolve this issue, connect as the master user, and then run the following command to grant the CREATE TABLE privilege:

SQL> GRANT CREATE TABLE TO test_user;

Run the following command to run the stored procedure:

SQL> EXEC TEST_PROC

Expected output:

PL/SQL procedure successfully completed.

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

The "ORA-04043" error occurs when you don't grant the master user a privilege with the grant option on an object.

The following example grant procedure fails because the master user doesn't have the SELECT privilege with the grant option on the DBA_TABLESPACE_USAGE_METRICS object:

SQL> SHOW USER;  
USER is \"EXAMPLE-USERNAME\"  
SQL> SELECT * FROM SYS.DBA_TABLESPACE_USAGE_METRICS;

The output shows the "ERROR: ORA-04043: object "SYS"."DBA_TABLESPACE_USAGE_METRICS" does not exist" error message.

When the master user tries to grant another user the SELECT privilege on the DBA_TABLESPACE_USAGE_METRICS object, the master user receives the "ORA-01031: insufficient privileges" error.

To resolve this issue, run the following commands to explicitly grant the SELECT privilege with the grant option to the master user:

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 you grant the SELECT privileges, the master user can then successfully run the SELECT command:

SQL> SELECT * FROM SYS.DBA_TABLESPACE_USAGE_METRICS;

Related information

Configuring privilege and role authorization on the Oracle website