Grant roles to newly created oracle db user

Question

Hi all,

Currently I´m doing some research on the aws rds oracle instance related to how the roles work here. According to what I have found i cannot simply add the DBA role to a recently created user for them to administer the instance with me. My question is, how can I grant roles to recently created users to have the same access as the master user that created the db or at least, similar to the db_owner role in other SQL instances.

Regards

YASH · Accepted Answer

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.

For RDS Oracle, this user comes with the below privileges and roles:

1 - System Privileges: ALTER DATABASE LINK, ALTER PUBLIC DATABASE LINK, DROP ANY DIRECTORY, EXEMPT ACCESS POLICY, EXEMPT IDENTITY POLICY, GRANT ANY OBJECT PRIVILEGE, RESTRICTED SESSION, EXEMPT REDACTION POLICY

2- Database Roles: AQ_ADMINISTRATOR_ROLE, AQ_USER_ROLE, CONNECT, CTXAPP, DBA, EXECUTE_CATALOG_ROLE, RECOVERY_CATALOG_OWNER, RESOURCE, SELECT_CATALOG_ROLE

If you wish to have another user with the same privileges, you can grant the above-mentioned privileges and roles the new user.

```
SQL> GRANT ALTER DATABASE LINK, ALTER PUBLIC DATABASE LINK, DROP ANY DIRECTORY, EXEMPT ACCESS POLICY, EXEMPT IDENTITY POLICY, GRANT ANY OBJECT PRIVILEGE, RESTRICTED SESSION, EXEMPT REDACTION POLICY TO  newuser;
```

Furthermore, if you wish to grant individual privileges to a user you can use "rdsadmin.rdsadmin_util.grant_sys_object" as follows:

begin
		rdsadmin.rdsadmin_util.grant_sys_object(
			p_obj_name  => '',
			p_grantee   => '',
			p_privilege => '');
	end;
	
For details on master user privileges, please refer to the following documentations.

[+]Master user account privileges - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.MasterAccounts.html

[+]Granting SELECT or EXECUTE privileges to SYS objectshttps://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.System.html#Appendix.Oracle.CommonDBATasks.TransferPrivileges

[+]How do I manage user privileges and roles in my Amazon RDS for Oracle DB instance? - https://repost.aws/knowledge-center/rds-oracle-user-privileges-roles

Grant roles to newly created oracle db user

Relevant content