Re: Privileges; role hierarchy

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: paul.baumgartel@xxxxxxx
  • Date: Thu, 9 Dec 2010 15:58:33 +0000

Pete Finnigan has

On Thu, Dec 9, 2010 at 3:43 PM, <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*
> IB Accounting Solutions
> 400 Atlantic Street
> Stamford, CT 06904
> 203.719.4368
> paul.baumgartel@xxxxxxx
> ****
> Visit our website at
> This message contains confidential information and is intended only
> for the individual named.  If you are not the named addressee you
> should not disseminate, distribute or copy this e-mail.  Please
> notify the sender immediately by e-mail if you have received this
> e-mail by mistake and delete this e-mail from your system.
> E-mails are not encrypted and cannot be guaranteed to be secure or
> error-free as information could be intercepted, corrupted, lost,
> destroyed, arrive late or incomplete, or contain viruses.  The sender
> therefore does not accept liability for any errors or omissions in the
> contents of this message which arise as a result of e-mail transmission.
> If verification is required please request a hard-copy version.  This
> message is provided for informational purposes and should not be
> construed as a solicitation or offer to buy or sell any securities
> or related financial instruments.
> UBS reserves the right to retain all messages. Messages are protected
> and accessed only in legally justified cases.

Niall Litchfield
Oracle DBA

Other related posts: