CODEWITHSUNDEEP
oracle-databaseamazon-web-servicesamazon-ec2oracle11gamazon-rds
Brian Koey
Brian Koey

Reputation: 203

Run grant execute query for sys object

I need your serious help here! I need to run multiple grant execute query for sys object to one of my user.

grant   execute on SYS.dbms_system  to $(User);
grant   execute on SYS.dbms_flashback   to $(User);

But, as what I know, in order to grant privileges to sys object for Oracle RDS, we need use procedure rdsadmin.rdsadmin_util.grant_sys_object to achieve.

I construct my new query, as following:

begin
    rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SYSTEM', '$(User)');
end;

But, I hit an error,

SQL Error: ORA-20199: Error in rdsadmin_util.grant_sys_object.

ORA-20900: You do not have permission to grant: to SYS object: DBMS_SYSTEM ORA-06512: at "RDSADMIN.RDSADMIN_UTIL", line 234 ORA-20900:

You do not have permission to grant: to SYS object: DBMS_SYSTEM ORA-06512: at line 2

Anyone have clue how do I solve this? Please help!

Upvotes: 1

Views: 5305

Answers (1)

APC
APC

Reputation: 146339

Let's assume you have run the procedure correctly and you have included the execute privilege in the call. This error 

ORA-20900: You do not have permission to grant: to SYS object: DBMS_SYSTEM

means you do not have sufficient permission to grant privileges on DBMS_SYSTEM. You may think you have that privilege but you don't.

Two possible explanations:

  • your granting user doesn't have its permission WITH GRANT OPTION and lacks the GRANT ANY PRIVILEGE power role.
  • your granting user gained its privileges through a role. We cannot use permissions granted through a role in PL/SQL, because of the Oracle security model. To do this permissions must be granted directly to the user.

"there is so many limitation within amazon RDS service ... it is such a huge disappointment from this service. "

It's the difference between IaaS and PaaS. The Amazon RDS offering is managed databases as a service. So it is quite reasonable for them to lock SYS, etc. SYS is for installing, configuring and patching the database, which are tasks undertaken by Amazon. (Many places use SYS for doing regular DBA tasks: they shouldn't.) If you want to do installing, configuring and patching for yourself then you need a different style of cloud service.

Upvotes: 3

Related Questions