Oracle: object user grants

Oracle is Oracle… It has good and bad things.
The bad of course… it’s closed and you’ll be crazy before getting info about how to do things.
And the good is that everythings is inside!! just at 1 query distance ;-)

Some days ago I was trying to look for grants given to a user by another user (WITH ADMIN OPTION).
I was logged with sys and I used “my” 2 wonderful querys for obtaining that info (copy/pasted from the internet),

  • Direct grants:
  • SELECT owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv 
      FROM table_privileges
     WHERE grantee = '&theUser'
     ORDER BY owner, table_name;
  • INdirect grants:
  • SELECT DISTINCT owner, table_name, PRIVILEGE 
      FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
     WHERE rp.grantee = '&theUser'
     ORDER BY owner, table_name;

Ressult? 0 rows! WTF!
I know there’s a plenty of it!!!

Continue reading “Oracle: object user grants”