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;
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!!!