I wrote this for 10g, but it should work fine w/11g (I know it won't work w/9i).
http://www.optimaldba.com/scripts/privilege_report.sqlThe script excludes known accounts, like SYS, SYSTEM, DBSNMP, PERFSTAT, etc. If you want to change the exclude list, just edit the two places where those account names appear.
Usual disclaimers apply...including the one that the resulting output can be rather large.
On 12/09/2010 08:43 AM, paul.baumgartel@xxxxxxx wrote:
I need to identify all privileges, whether granted directly or via role(s), held by a set of users. Of course, because roles can be granted to roles, it's necessary to traverse any role hierarchies in DBA_ROLE_PRIVS to find the ultimate user grantees (I see these as the leaves of a tree with a role at the root and other roles as branches). DBA_TAB_PRIVS must be consulted for the object privileges granted.Before I start rolling my own, I though I'd ask whether anyone has developed a query or procedure to get this information. I'm using 11gR2.Thanks, *Paul Baumgartel* UBS AG IB Accounting Solutions 400 Atlantic Street Stamford, CT 06904 203.719.4368 paul.baumgartel@xxxxxxx _www.ubs.com_ <file://www.ubs.com>