Does RDS allow EXECUTE on DBMS_SYSTEM?

Question

I am trying to run following through my admin account:

```
begin
    rdsadmin.rdsadmin_util.grant_sys_object(
        p_obj_name     => 'DBMS_SYSTEM',
        p_grantee      => 'USERNAME',
        p_privilege    => 'EXECUTE',
        p_grant_option => false);
end;
```

It gives error saying:

```
begin
*
ERROR at line 1:
ORA-20199: Error in rdsadmin_util.grant_sys_object. ORA-20900: You do not have permission to grant DBMS_SYSTEM
ORA-06512: at "RDSADMIN.RDSADMIN_UTIL", line 221
ORA-20900: You do not have permission to grant DBMS_SYSTEM
ORA-06512: at line 2
```

What is the problem here? And ho do I get a workaround for this?   
Please reply as I'm stuck because of this.

Answer

I'm not sure if this was changed recently or if it depends on the version, but I was able to successfully grant EXECUTE on DBMS_SYSTEM on my SE2 12.2 RDS instance:  
  
SQL> exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_SYSTEM','IDS_GOV_WPD','EXECUTE');  
PL/SQL procedure successfully completed.  
  
SQL> select privilege from dba_tab_privs where grantee = 'IDS_GOV_WPD' and table_name = 'DBMS_SYSTEM';  
  
PRIVILEGE  
----------------------------------------  
EXECUTE

Answer

Hi there,  
  
Execute privileges on DBMS_SYSTEM are not available to the master user in RDS Oracle. Note that this is an undocumented internal package that may change between versions. However, depending on what you are trying to accomplish, there is a probably a way to do it in another way.   
  
For example, session tracing can be enabled with DBMS_SESSION (same session) or DBMS_MONITOR (another session), and events can be set in a session directly with "alter session" or via a login trigger. While Oracle provides no supported method for writing to the alert log, you can use UTL_FILE to write to your own log files in database directory objects (directories can be created using the RDSADMIN.RDSADMIN_UTIL.CREATE_DIRECTORY procedure).  
  
Hope this helps.  
  
Thanks,  
Michael

Does RDS allow EXECUTE on DBMS_SYSTEM?

PRIVILEGE

関連するコンテンツ