Prev: oracle,blobs,cluster big app
Next: NOT IN clause vs IN clause
From: Peter Kallweit on 9 Jul 2010 03:33
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
From: Vladimir M. Zakharychev on 9 Jul 2010 05:08
On Jul 9, 11:33 am, Peter Kallweit <p_kallw...(a)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

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
From: Mark D Powell on 9 Jul 2010 10:28
On Jul 9, 3:33 am, Peter Kallweit <p_kallw...(a)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 --
From: Peter Kallweit on 12 Jul 2010 03:15
On 09.07.2010 16:28, Mark D Powell wrote:
> On Jul 9, 3:33 am, Peter Kallweit<p_kallw...(a)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
From: Mark D Powell on 12 Jul 2010 08:39
On Jul 12, 3:15 am, Peter Kallweit <p_kallw...(a)arcor.de> wrote:
> On 09.07.2010 16:28, Mark D Powell wrote:
>
>
>
>
>
> > On Jul 9, 3:33 am, Peter Kallweit<p_kallw...(a)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 --
 | 
Pages: 1
Prev: oracle,blobs,cluster big app
Next: NOT IN clause vs IN clause