Re: Quick question for DBA

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 13 Oct 1999 14:36:12 -0400
Message-ID: <yNAEOJLapaSltVuf5YMnOIrDsR9a@4ax.com>

A copy of this was sent to "fumi" <fumi_at_tpts5.seed.net.tw> (if that email address didn't require changing) On 13 Oct 1999 16:27:35 GMT, you wrote:

>
>sysdba <sysdba_at_inter.net.il> wrote in message
>news:7tsurp$os3$1_at_news2.inter.net.il...
>> 1. grant create public synonym to X;
>> 2.
>> select * from dba_sys_privs where privilege='CREATE PUBLIC SYNONYM';
>> and for each role u get, see what user got priv for this role:
>> select * from dba_role_prives where granted_role='rolesfromselect';
>> (make from those 2 selects one select, i'm too lazy to do it now...:) )
>>
>> 3. look at table dba_role_privs.
>
>
>Well, the last one can not be right.
>In fact, I did not see a right answer in any published books I've studied.
>The answer is very complicated.
>Consider the following example:
>
>create role app_admin;
>create role app_supervisor;
>grant create public synonym to app_admin;
>grant app_admin to app_supervisor;
>grant app_supervisor to scott;
>
>How to show that scott has the privilege "create public synonym"?
>
>Be ware that a privilege can be granted to a role or a user,
>a role can be granted to another role or a user.
>So, it must use a hierarchical query to answer this question.
>Unfortunately, the view DBA_SYS_PRIVS and DBA_ROLE_PRIVS can not be
>used to perform a hierarchical query, only SYS's tables can be used.
>I've wrote such a script, but I can not find it now...:(
>
>

If you like, you can (as SYS or Internal)

create view user_role_hierarchy
as
select u2.name granted_role
  from ( select *

           from sys.sysauth$
        connect by prior privilege# = grantee#
          start with grantee# = uid or grantee# = 1) sa,
       sys.user$ u2

 where u2.user#=sa.privilege#
union all select user from dual
union all select 'PUBLIC' from dual
/
grant select on user_role_hierarchy to public;

this view will show, for the currently logged in user, all of the roles they have, the roles granted to those roles (and so on...), their NAME, and the word PUBLIC. You can use this view to see base privs you have with:

select * from dba_tab_privs
where grantee in ( select * from sys.user_role_hierarchy ) /

So, if a row is returned from this query, the currently logged in user has that privilege either via the role OR because public can OR because they have been granted the privelege directly.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Oct 13 1999 - 13:36:12 CDT