Here is a quick tip on Oracle privilege analysis. Frequently I want to find out all of the ways a user can get to an object for any privilege. DBA_TAB_PRIVS and DBA_ROLE_PRIVS are the two views I go to. I want to also see all the privileges that are granted on any object. This is good for starting at the user tracking privileges to the object, it’s also good for starting at an object and walking back to the user.
This query does a pivot on the users and roles to get the path to the object and what privileges are associated with that path.
"'ON COMMIT REFRESH'" OCR,
"'INHERIT PRIVILEGES'" IPRV,
"'QUERY REWRITE'" QR,
FROM (SELECT R.GRANTEE "GRANTEE_TO",
FROM DBA_TAB_PRIVS T,
WHERE T.GRANTEE = R.GRANTED_ROLE (+)
AND t.grantee != 'SYS'
AND t.grantee != 'SYSTEM'
AND R.GRANTEE != 'SYS'
AND R.GRANTEE != 'SYSTEM' )
PIVOT (COUNT(PRIVILEGE) FOR PRIVILEGE IN ('SELECT',
'ON COMMIT REFRESH',
ORDER BY TABLE_NAME;