Amazon-rds – Oracle AWS RDS supermaster user cant grant select on some tables

amazon-rdsawsoracle

I'm dealing with some sort of issue beyond my Oracle knowledge. A customer wants to execute some queries to detect issues, locks, etc in a Oracle RDS database. I assume this queries needs database administration privelege to be executed, in fact, is pretty clear that me, as AWS Oracle supermaster can execute without error any query, for example:

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece;

the same query in normal user returns an error

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 3 Column: 6

so my initial idea was to let normal user to SELECT some tables, one by one, adding privileges one by one. It worked to me with the first query, I was able to add this to the normal user (by sqldeveloper plus)

GRANT SELECT ON v$session to "TESTUSER";

so I decided to continue this approach. Bad for me, next SQL commands to assign privileges are returning an error, for example:

Error starting at line : 14 in command -
GRANT SELECT ON v$sqltext_with_newlines to "TESTUSER"
Error report -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

So is like I'm not "powerful" enough as supermaster of Oracle RDS to assign some privileges to other Users. I tried to use the GUI of sqldeveloper and run "Grant all" and it returns to me the same error. Most of the privileges were not able to be granted.

Is this normal behavior? I can assure I have the supermaster user of AWS RDS Oracle. I double checked before.

Thanks

Best Answer

  1. This will not work:

GRANT SELECT ON v$sqltext_with_newlines to "TESTUSER";

v$sqltext_with_newlines is a public synonym for sys.v_$sqltext_with_newlines. If you want to grant select on that view to someone, use this:

GRANT SELECT ON sys.v$sqltext_with_newlines to "TESTUSER";

And after that, TESTUSER can select from v$sqltext_with_newlines. But:

  1. Just because your user can select from v_$sqltext_with_newlines, that does not mean you can grant this privilege to others. For that, you need to have the select privilege granted with the GRANT OPTION to your user. This restriction can be worked around by having the GRANT ANY OBJECT PRIVILEGE system privilege, for objects in user schemas, but not for objects owned by SYS.

Here is the list of privileges of the master user:

Master User Account 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

AQ_ADMINISTRATOR_ROLE, AQ_USER_ROLE, CONNECT, CTXAPP, DBA, EXECUTE_CATALOG_ROLE, RECOVERY_CATALOG_OWNER, RESOURCE, SELECT_CATALOG_ROLE

So no, the master user does not have unlimited access, there are some things that even that user can not do.

Related Question