Re: Quick question for DBA

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...:( Received on Wed Oct 13 1999 - 11:27:35 CDT