CODEWITHSUNDEEP
sqloracle-databaseamazon-web-servicesamazon-rds
Ori N
Ori N

Reputation: 748

AWS RDS - Oracle - Grant permissions on sys

I'm trying to create a role and grant select permissions on sys.dba_objects. This is my sql:

grant select on sys.dba_objects to my_role
grant select on sys.dba_objects to my_role;

On RDS I get the following error: Error: ORA-01031: insufficient privileges

I saw that RDS has a dedicated stored procedure for granting permissions on sys. I tried:

call rdsadmin.rdsadmin_util.grant_sys_object('SYS.REGISTRY$HISTORY', 'my_role', 'select')

Error I get:

ORA-44003: invalid SQL name
ORA-06512: at "RDSADMIN.RDSADMIN_UTIL", line 248
ORA-44003: invalid SQL name
ORA-06512: at line 1

Upvotes: 1

Views: 4860

Answers (3)

Ori N
Ori N

Reputation: 748

This is how I made it work: 1. Removed the sys. prefix like suggested above 2. Used uppercase

Example:

call rdsadmin.rdsadmin_util.grant_sys_object('DBA_OBJECTS', 'ASSESSMENT_ROLE', 'SELECT');

Upvotes: 2

doc123
doc123

Reputation: 106

Try following...First you need to grant select to rdsadmin with grant option using SYS.

As SYS grant select on DBA_objects to rdsadmin with grant option;

As rdsadmin

grant select on sys.dba_objects to my_role;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143073

I don't know AWS RDS, but ORA-01031 sounds like a known restriction: privileges granted via roles won't work in PL/SQL procedures - you'll have to grant them directly to user.

Upvotes: 0

Related Questions