Re: Privileges; role hierarchy

From: Daniel W. Fink <daniel.fink_at_optimaldba.com>
Date: Thu, 09 Dec 2010 08:54:54 -0700
Message-ID: <4D00FBCE.6050506_at_optimaldba.com>

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.sql

The 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_at_ubs.com 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_at_ubs.com
> _www.ubs.com_ <file://www.ubs.com>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 09 2010 - 09:54:54 CST