How can I find out who granted a privilege, and when it was granted by querying the database?
Printable View
How can I find out who granted a privilege, and when it was granted by querying the database?
To find who granted object privileges you can query column GRANTOR in DBA_TAB_PRIVS and DBA_COL_PRIVS. For system privileges and roles there is no way to find out this information from the dictionary. Also there is no way to find out when particular privilege/role was granted by querying the dictionary. You should turn auditing on or use log miner to keep track of those information.
In this particular case, someone was granted execute on DBMS_SQL. I'm looking for whatever I can find about who/when the grant occurred.
Where should I look for this?
Thanks!
SELECT grantor FROM dba_tab_privs WHERE owner = 'SYS' AND table_name = 'DBMS_SQL' AND grantee = 'SCOTT';
will show who granted execute on SYS.DBMS_SQL to user SCOTT.