Home » SQL & PL/SQL » SQL & PL/SQL » Roles dependency (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
Roles dependency [message #562878] Mon, 06 August 2012 09:42 Go to next message
manubatham20
Messages: 473
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi,

I want to find role dependency on another role. Please consider below scnerios:

1. If its a basic role (made of priviliges), what data dictionary view I should query?
2. If the role is made of some other role and additional priviliges, what query I should fire to find that?
3. Role is granted to which users?

Please help me on that.

Thanks,
Manu
Re: Roles dependency [message #562879 is a reply to message #562878] Mon, 06 August 2012 09:51 Go to previous messageGo to next message
BlackSwan
Messages: 23137
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

URL contains solution
http://www.petefinnigan.com/tools.htm
Re: Roles dependency [message #562882 is a reply to message #562879] Mon, 06 August 2012 10:40 Go to previous messageGo to next message
manubatham20
Messages: 473
Registered: September 2010
Location: Champaign, IL
Senior Member

Ummm, Link is very useful, I will read that.

But for now, I want to fetch role dependency in dba_role_privs data dictionary view through connect by clause.

I am not very familiar with connect by, thats why I am asking here.

[EDIT]: where grantee is a role, not the user (can find in DBA_ROLES table)

Thanks,
Manu

[Updated on: Mon, 06 August 2012 10:43]

Report message to a moderator

Re: Roles dependency [message #562883 is a reply to message #562882] Mon, 06 August 2012 10:53 Go to previous messageGo to next message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
is ROLE_ROLE_PRIVS the view you want? Roles granted to roles?
Re: Roles dependency [message #562889 is a reply to message #562882] Mon, 06 August 2012 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Ummm, Link is very useful, I will read that.

But for now, I want to fetch role dependency in dba_role_privs data dictionary view through connect by clause.


And if you just read the second link you will see how to do it... of course, it needs you make a little effort like clicking on 2 links.
Will it be possible? ./fa/1600/0/

Regards
Michel
Re: Roles dependency [message #562897 is a reply to message #562878] Mon, 06 August 2012 12:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2077
Registered: January 2010
Senior Member
manubatham20 wrote on Mon, 06 August 2012 10:42
3. Role is granted to which users?


To check if user 'user_name' is granted (directly or indirectly) role 'role-in-question':

SELECT  DECODE(COUNT(*),0,'Role is not granted','Role is granted.') "And the answer is"
  FROM  DBA_ROLE_PRIVS
  WHERE GRANTED_ROLE = 'role-in-question'
  START WITH GRANTEE = 'user_name'
  CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
/


To check if who is granted (directly or indirectly) role 'role-in-question':

SELECT  DISTINCT GRANTEE
  FROM  DBA_ROLE_PRIVS
  WHERE CONNECT_BY_ISLEAF = 1
  START WITH GRANTED_ROLE = 'role-in-question'
  CONNECT BY GRANTED_ROLE = PRIOR GRANTEE
/


You might need to adjust it for role chain not granted to any user:

 SELECT  GRANTEE
   FROM  DBA_ROLE_PRIVS
   WHERE CONNECT_BY_ISLEAF = 1
   START WITH GRANTED_ROLE = 'role-in-question'
   CONNECT BY GRANTED_ROLE = PRIOR GRANTEE
MINUS
 SELECT  ROLE
   FROM  DBA_ROLES
/


SY.

[Updated on: Mon, 06 August 2012 12:27]

Report message to a moderator

Re: Roles dependency [message #562945 is a reply to message #562897] Tue, 07 August 2012 03:49 Go to previous messageGo to next message
manubatham20
Messages: 473
Registered: September 2010
Location: Champaign, IL
Senior Member

Thanks for the pointer John.

I am trying below to find role-to-role dependency, where first level role are made up of priviliges, and second level role are composed of roles only.

First level role (test1) <-- privilige assigned (create any table)
First level role (test5) <-- privilige assigned (connect)

Second level and onward role <-- privilige assigned (any role)

create role test1;

grant create any table to test1;

create role test2;

grant test1 to test2;

create role test3;

grant test2 to test3;

create role test4;

grant connect to test4;

create role test5;

grant test1, test2, test3 to test5;

create role test6;

grant test3, test5 to test6;

select * from role_role_privs
start with granted_role = 'TEST1'
  connect by prior role = granted_role;



Right now I am trying to get role dependency in top-down and bottom-up hierarical format. Please modify my query to achieve that.

Thanks,
Manu
Re: Roles dependency [message #562946 is a reply to message #562945] Tue, 07 August 2012 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 06 August 2012 18:57
...
And if you just read the second link you will see how to do it... of course, it needs you make a little effort like clicking on 2 links.
Will it be possible? ./fa/1600/0/
...


I now know the answer: no it is not possible. You are not only unable to write a SQL from what has been given to you, you are even unable to click on a link.

Regards
Michel

[Updated on: Tue, 07 August 2012 04:04]

Report message to a moderator

Re: Roles dependency [message #562969 is a reply to message #562946] Tue, 07 August 2012 06:15 Go to previous messageGo to next message
manubatham20
Messages: 473
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi Michel,

I already gone through the second link.

See my test case below:

create role test1;

grant create any table to test1;

create role test2;

grant test1, create any directory to test2;

create role test3;

grant test2, drop any directory to test3;

create role test4;

grant connect to test4;

create role test5;

grant test1, test2, test3 to test5;

create role test6;

grant test3, test5 to test6;

create user scott identified by tiger;

grant test6 to scott;


I am using below procedure from the link:

create or replace procedure get_privs (pv_grantee in varchar2,lv_tabstop in out number) is
        --
        lv_tab varchar2(50):='';
        lv_loop number;
        --
        cursor c_main (cp_grantee in varchar2) is
        select  'ROLE' typ,
            grantee grantee,
            granted_role priv,
            admin_option ad,
            '--' tabnm,
            '--' colnm,
            '--' owner
        from    dba_role_privs
        where   grantee=cp_grantee
        union
        select  'SYSTEM' typ,
            grantee grantee,
            privilege priv,
            admin_option ad,
            '--' tabnm,
            '--' colnm,
            '--' owner
        from    dba_sys_privs
        where   grantee=cp_grantee
        union
        select  'TABLE' typ,
            grantee grantee,
            privilege priv,
            grantable ad,
            table_name tabnm,
            '--' colnm,
            owner owner
        from    dba_tab_privs
        where   grantee=cp_grantee
        union
        select  'COLUMN' typ,
            grantee grantee,
            privilege priv,
            grantable ad,
            table_name tabnm,
            column_name colnm,
            owner owner
        from    dba_col_privs
        where   grantee=cp_grantee
        order by 1;
    begin
        lv_tabstop:=lv_tabstop+1;
        for lv_loop in 1..lv_tabstop loop
            lv_tab:=lv_tab||chr(9);
        end loop;
        for lv_main in c_main(pv_grantee) loop
            if lv_main.typ='ROLE' then
                dbms_output.put_line(lv_tab||'ROLE => '
                ||lv_main.priv||' which contains =>'); 
                get_privs(lv_main.priv,lv_tabstop);
            elsif lv_main.typ='SYSTEM' then
                dbms_output.put_line(lv_tab||'SYS PRIV => '
                    ||lv_main.priv
                    ||' grantable => '||lv_main.ad);
            elsif lv_main.typ='TABLE' then
                dbms_output.put_line(lv_tab||'TABLE PRIV => '
                    ||lv_main.priv
                    ||' object => '
                    ||lv_main.owner||'.'||lv_main.tabnm
                    ||' grantable => '||lv_main.ad);
            elsif lv_main.typ='COLUMN' then
                dbms_output.put_line(lv_tab||'COL PRIV => '
                    ||lv_main.priv
                    ||' object => '||lv_main.tabnm
                    ||' column_name => '
                    ||lv_main.owner||'.'||lv_main.colnm
                    ||' grantable => '||lv_main.ad);
            end if;
        end loop;
        lv_tabstop:=lv_tabstop-1;
        lv_tab:='';
    exception
        when others then
            dbms_output.put_line('ERROR (get_privs) => '||sqlcode);
            dbms_output.put_line('MSG (get_privs) => '||sqlerrm);
    end get_privs;
    /


declare
a number:=0;
begin
get_privs('SCOTT',a);
end;
/


Its giving me result, like a user has which privilidge and roles.

Now, my task is to generate ddl for role and privilege granted to current user. Because it can't be done in top-down approach, so I am asking way to go bottom up (as first level role should be created first, then second level role, then third level and so on, because a role can't be created if there don't exist the role on which role to be created is dependent on)

Regards,
Manu
Re: Roles dependency [message #562970 is a reply to message #562969] Tue, 07 August 2012 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Remove the stupid WHEN OTHERS clause.
2/ It is overcomplicated
3/ What prevent you from doing in the opposite way? (Note that it is in the script close to the previous one you copied)

Regards
Michel

[Updated on: Tue, 07 August 2012 06:22]

Report message to a moderator

Re: Roles dependency [message #562978 is a reply to message #562969] Tue, 07 August 2012 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a simple way to do it (I used TEST instead of SCOTT):
SQL> def 1='TEST'
SQL> col ord noprint
SQL> with 
  2    roles as (
  3      select granted_role role
  4      from dba_role_privs
  5      connect by prior granted_role = grantee
  6      start with grantee = '&1'
  7      group by granted_role
  8      order by min(level) desc
  9    )
 10  select 1 ord, 'create role '||role||';' statement
 11  from roles
 12  union all
 13  select 2 ord, 'grant '||p.privilege||' to '||p.grantee||';'
 14  from roles r, dba_sys_privs p
 15  where p.grantee = r.role
 16  union all
 17  select 3 ord, 'grant '||p.granted_role||' to '||p.grantee||';'
 18  from roles r, dba_role_privs p
 19  where p.grantee = r.role
 20  union all
 21  select 4 ord, 'grant '||p.privilege||' to &1;'
 22  from dba_sys_privs p
 23  where p.grantee = '&1'
 24  union all
 25  select 5 ord, 'grant '||p.granted_role||' to &1;'
 26  from dba_role_privs p
 27  where p.grantee = '&1'
 28  order by 1, 2
 29  /
STATEMENT
---------------------------------------------------------------------------------
create role TEST1;
create role TEST2;
create role TEST3;
create role TEST5;
create role TEST6;
grant CREATE ANY DIRECTORY to TEST2;
grant CREATE ANY TABLE to TEST1;
grant DROP ANY DIRECTORY to TEST3;
grant TEST1 to TEST2;
grant TEST1 to TEST5;
grant TEST2 to TEST3;
grant TEST2 to TEST5;
grant TEST3 to TEST5;
grant TEST3 to TEST6;
grant TEST5 to TEST6;
grant TEST6 to TEST;

Regards
Michel
Re: Roles dependency [message #562979 is a reply to message #562978] Tue, 07 August 2012 07:13 Go to previous messageGo to next message
manubatham20
Messages: 473
Registered: September 2010
Location: Champaign, IL
Senior Member

Smile

I looked into those links yesterday only, but not able to customize them according to requirement.

Thanks for all your help, your solution is just what I want. I think I need to work on connect by clause.

Many thanks,
Manu
Re: Roles dependency [message #562982 is a reply to message #562979] Tue, 07 August 2012 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A slighty nicer query is:
SQL> def 1='TEST'
SQL> col ord noprint
SQL> with 
  2    roles as (
  3      select granted_role role
  4      from dba_role_privs
  5      connect by prior granted_role = grantee
  6      start with grantee = '&1'
  7      group by granted_role
  8      order by min(level) desc
  9    )
 10  select 1 ord, 'create role '||role||';' statement
 11  from roles
 12  union all
 13  select 2 ord, 'grant '||p.privilege||' to '||p.grantee||';'
 14  from roles r, dba_sys_privs p
 15  where p.grantee = r.role
 16  union all
 17  select 3 ord, 'grant '||wm_concat(p.granted_role)||' to '||p.grantee||';'
 18  from roles r, dba_role_privs p
 19  where p.grantee = r.role
 20  group by p.grantee
 21  union all
 22  select 4 ord, 'grant '||wm_concat(p.granted_role)||' to '||p.grantee||';'
 23  from dba_role_privs p
 24  where p.grantee = '&1'
 25  group by p.grantee
 26  union all
 27  select 5 ord, 'grant '||p.privilege||' to '||p.grantee||';'
 28  from dba_sys_privs p
 29  where p.grantee = '&1'
 30  order by 1, 2
 31  /
STATEMENT
------------------------------------------------------------------------------
create role TEST1;
create role TEST2;
create role TEST3;
create role TEST5;
create role TEST6;
grant CREATE ANY DIRECTORY to TEST2;
grant CREATE ANY TABLE to TEST1;
grant DROP ANY DIRECTORY to TEST3;
grant TEST1 to TEST2;
grant TEST1,TEST2,TEST3 to TEST5;
grant TEST2 to TEST3;
grant TEST3,TEST5 to TEST6;
grant TEST6 to TEST;

Regards
Michel

[Updated on: Tue, 07 August 2012 08:35]

Report message to a moderator

Re: Roles dependency [message #562983 is a reply to message #562982] Tue, 07 August 2012 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Forgot to mention this does not handle the object privileges.

Regards
Michel
Re: Roles dependency [message #563455 is a reply to message #562878] Mon, 13 August 2012 21:53 Go to previous messageGo to next message
manubatham20
Messages: 473
Registered: September 2010
Location: Champaign, IL
Senior Member

No problem.

I was only interested in what privileges are assigned to roles.

Thanks,
Manu
Re: Roles dependency [message #563469 is a reply to message #563455] Tue, 14 August 2012 01:47 Go to previous message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Object privileges can be granted to roles.

Regards
Michel
Previous Topic: multiple recipients using UTL_SMTP package
Next Topic: Updating table with sequentially changing column name
Goto Forum:
  


Current Time: Thu Dec 18 09:43:41 CST 2014

Total time taken to generate the page: 0.06685 seconds