Re: How to find the grantees of a role
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 12 Jul 2010 05:39:23 -0700 (PDT)
Message-ID: <868f8503-7e77-461e-8c2a-b65b2096e977_at_k39g2000yqd.googlegroups.com>
On Jul 12, 3:15 am, Peter Kallweit <p_kallw..._at_arcor.de> wrote:
> On 09.07.2010 16:28, Mark D Powell wrote:
>
>
>
>
>
> > On Jul 9, 3:33 am, Peter Kallweit<p_kallw..._at_arcor.de> wrote:
> >> Hi %,
>
> >> I have a user to which a role has been granted with admin option. So
> >> this user now can grant/revoke this role to/from other users.
>
> >> But how can this user find out, to which other users this role is
> >> currently granted?
>
> >> As a DBA, one would use the view DBA_ROLE_PRIVS. But what, if the access
> >> to the dba_xxx views is not available? I'm missing a view like
> >> ALL_ROLE_PRIVS (not found in 9i, 10g, 11g).
>
> >> Any ideas?
>
> >> Best regards
> >> Peter
>
> > Peter, the link below is to a short article that identifies most the
> > Oracle security related views that you access to see who has access to
> > what:
>
> > How do I find out which users have the rights, or privileges, to
> > access a given object ? http://www.jlcomp.demon.co.uk/faq/privileges.html
>
> > See view dba_role_privs to see who has been granted a role.
>
> > HTH -- Mark D Powell --
>
> Hi Mark,
>
> the view dba_role_privs would do the job, but my user is not permitted
> to access it - dba_role_privs requires the role select_catalog_role.
>
> For most dba_xxx views it exists a comparable all_xxx view, which
> everybody can access and which shows only data you are permitted to see.
> However, for dba_role_privs I'm missing the comparable all_role_privs.
>
> Regards
> Peter- Hide quoted text -
>
> - Show quoted text -
Date: Mon, 12 Jul 2010 05:39:23 -0700 (PDT)
Message-ID: <868f8503-7e77-461e-8c2a-b65b2096e977_at_k39g2000yqd.googlegroups.com>
On Jul 12, 3:15 am, Peter Kallweit <p_kallw..._at_arcor.de> wrote:
> On 09.07.2010 16:28, Mark D Powell wrote:
>
>
>
>
>
> > On Jul 9, 3:33 am, Peter Kallweit<p_kallw..._at_arcor.de> wrote:
> >> Hi %,
>
> >> I have a user to which a role has been granted with admin option. So
> >> this user now can grant/revoke this role to/from other users.
>
> >> But how can this user find out, to which other users this role is
> >> currently granted?
>
> >> As a DBA, one would use the view DBA_ROLE_PRIVS. But what, if the access
> >> to the dba_xxx views is not available? I'm missing a view like
> >> ALL_ROLE_PRIVS (not found in 9i, 10g, 11g).
>
> >> Any ideas?
>
> >> Best regards
> >> Peter
>
> > Peter, the link below is to a short article that identifies most the
> > Oracle security related views that you access to see who has access to
> > what:
>
> > How do I find out which users have the rights, or privileges, to
> > access a given object ? http://www.jlcomp.demon.co.uk/faq/privileges.html
>
> > See view dba_role_privs to see who has been granted a role.
>
> > HTH -- Mark D Powell --
>
> Hi Mark,
>
> the view dba_role_privs would do the job, but my user is not permitted
> to access it - dba_role_privs requires the role select_catalog_role.
>
> For most dba_xxx views it exists a comparable all_xxx view, which
> everybody can access and which shows only data you are permitted to see.
> However, for dba_role_privs I'm missing the comparable all_role_privs.
>
> Regards
> Peter- Hide quoted text -
>
> - Show quoted text -
I question if normal users need access to this information; however, whenever I have needed to provide special access to rdbms dictionary information not normally available via an Oracle provided view such as showing package source to developers I have used the Oracle view code as a base to a home grown view that provides the necessary information.
In tother words If you are sure an Oracle view does not provide the necessary information then write one yourself.
HTH -- Mark D Powell -- Received on Mon Jul 12 2010 - 07:39:23 CDT