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

On Jul 9, 11:33*am, Peter Kallweit <p_kallw... (AT) arcor (DOT) de> wrote:
Well, the user can find out for which roles he is granted ADMIN OPTION
from USER_ROLE_PRIVS, but there seem to be no way to find out to which
users he granted these roles. Actually, DBA_ROLE_PRIVS does not say
who granted particular role to the user either, and this is not really
important as anyone with ADMIN OPTION for this role and role owner
himself can revoke it regardless who granted it.

Regards,
Vladimir M. Zakharychev

On Jul 9, 3:33*am, Peter Kallweit <p_kallw... (AT) arcor (DOT) de> wrote:
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 --

On 09.07.2010 16:28, Mark D Powell wrote:
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

On Jul 12, 3:15*am, Peter Kallweit <p_kallw... (AT) arcor (DOT) de> wrote:
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 --