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.