RDS Oracle database: ORA-01031 while granting privilege

Question

Hi,

Logged on as the m aster user on a Oracle 19 RDS database, I would like to execute the following:

GRANT EXECUTE ON XDB.DBMS_XMLSCHEMA TO appl_owner;

This fails with ORA-01031: insufficient privileges.

I tried this with RDSADMIN package:
EXECUTE rdsadmin.rdsadmin_util.**grant_sys_object**( p_obj_name=>'DBMS_XMLSCHEMA', p_grantee=>'FWD_OWNER', p_privilege=>'EXECUTE');

But that want to grant privileges on SYS objects, so it doesn't find objects in schema XDB.

How can I grant privileges on objects in different schema (xdb) to o different user/schema?
Thanks

Answer

XDB has no active login and connecting to XDB via proxy (alter user xdb grant connect through <>) is not allowed.

But... the problem is no longer an issue really: EXECUTE privilege on XDB.DBMS_XMLSCHEMA was granted to PUBLIC; a detail I overlooked. The query I had to check that showed I had an issue, did not check on privileges granted to PUBLIC. So the application can call this package successfully.

Case closed, thank you for your responses!

Answer

DBMS_XMLSCHEMA does not look like owned by sys but XDB.  XDB should able to give grant to respective owner.

Answer

When you have Database instance in AWS i.e. RDS, you will never be granted the FULL blown Admin as this could be used to elevate access to the underlying operating system.

Please see the table in the link of the Maximum privilege that your master user will be grated. You may NOT grant any access higher than what the master user is granted. You can see in your error message that you cant alter SYSTEM access.. This is because AWS does not allow SYSTEM to be altered.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.MasterAccounts.html

If this answers your question, please be sure to accept so that this helps others and me.

RDS Oracle database: ORA-01031 while granting privilege

相關內容