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

Short description

When you create an Amazon RDS for Oracle database instance, the default master user is created and granted the maximum user permissions on the DB instance with some limitations. Use this account for any administrative tasks, such as creating additional user accounts in the database. Because Amazon RDS is a managed service, SYS and SYSTEM users can't be used by default.

For the list of roles and privileges granted to the Amazon RDS for Oracle master user, see Master user account privileges.

Because Amazon RDS is a managed service, the following privileges for the DBA role are not provided:

• ALTER DATABASE
• ALTER SYSTEM
• CREATE ANY DIRECTORY
• DROP ANY DIRECTORY
• GRANT ANY PRIVILEGE
• GRANT ANY ROLE

For more information, see Limitations for Oracle DBA privileges.

Resolution

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

Run a command similar to the following to grant SELECT privileges on the object V_$SQLAREA to the user EXAMPLE-USERNAME:

Log in as the RDS master user and run the following procedure:

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

Run a command similar to the following to grant SELECT privileges on the object V_$SQLAREA to the user EXAMPLE-USERNAME with grant option:

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

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

Run the following queries to grant the roles SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE to the user EXAMPLE-USERNAME with admin option. With these roles, EXAMPLE-USERNAME can grant access to SYS objects that have been granted to SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE.

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

Run the following queries to view the grants associated with the role SELECT_CATALOG_ROLE:

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 RDS procedure rdsadmin.rdsadmin_util.revoke_sys_object.

Run the following command to revoke SELECT privileges on the object V_$SQLAREA from the user EXAMPLE-USERNAME:

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.

Use the following troubleshooting options based on your issue and use case.

Resetting the master user privileges

If you revoked the roles and privileges of the master user, you can reset them by changing the master user password for your RDS DB instance.

Granting RDS_MASTER_ROLE to non-master users

The role RDS_MASTER_ROLE can't be granted to non-master users. This role is created by SYS by default when the DB instance is created. The RDS_MASTER_ROLE must be granted only to the master user. Run the following query to list users that are granted the RDS_MASTER_ROLE:

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

The output looks similar to the following:

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

You might notice from the output of the query that the master user doesn't have the admin option. Therefore, the role RDS_MASTER_ROLE can't be granted to any other user.

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

Revoking privileges granted to PUBLIC

Revoking PUBLIC privileges to key DBMS_* and UTL_* packages is not a best practice because several Oracle applications are designed to rely on these privileges. For more information, see MOSC Doc 247093.1. 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.

Troubleshooting the error when creating a role with password

Suppose that you run the following query to create a role with password and grant privileges using the rdsadmin_util.grant_sys_object:

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

Then, 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;

Troubleshooting the error ORA-01031: insufficient privileges

The following are some examples of use cases when you might get this error:

You ran the ALTER SYSTEM query. For example, the following query fails with the ORA-01031 error:

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

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

You're using a database trigger and your changes to the underlying table structure caused the trigger status to become INVALID. The next time the trigger is fired, you might notice that the implicit compilation of the trigger fails with the following error:

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

To fix this issue, grant the administer database trigger privilege to the trigger's owner explicitly. This privilege is required to alter the database trigger successfully:

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

You ran a stored procedure. You get this error when you run a stored procedure, because privileges acquired using roles don't work in named stored procedures that run with definer's rights. These privileges work in SQL Plus and anonymous PL/SQL blocks.

Example:

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 fix this issue, connect as the master user and grant the required privileges explicitly.

Example:

-- 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.

The master user is not granted the privileges with the grant option. You might get this error when the following conditions are true:

• The master user is not granted a certain privilege on an object with the grant option.
• The master user attempts to grant this privilege to another user.

To fix this issue, grant the required privileges explicitly to the master user with the grant option.

Example:

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

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

Oracle documentation for Configuring privilege and role authorization