Re: Need recursive roles script

From: Sridhar Subramaniam <avion_at_ozemail.com.au>
Date: 1995/11/09
Message-ID: <30A2C5FF.7BA9_at_ozemail.com.au>#1/1

Kevin Woolley wrote:
>
> We have a database set up with two layers of roles (objects
> privileges granted to application roles, these roles then granted to
> business roles, which are finally granted to users). I need a script to
> trace down this tree so I can easily find which object privileges and/or
> application roles are granted to a user.
>
> Can anyone help.
>
> Kevin

Kevin,

You could do the foll:

1. To find what object privs have been granted to a role :

	select *
	from sys.dba_tab_privs
	where grantee = <Role_name>;

This would cover all object grants, including procedural objects

2. To find out more about 'cascaded' roles : ( roles granted to roles )

 	select *
	from role_role_privs
	where role = <Role_name>   .... or is granted_role. Don't 've the manuals 

handy. Check this out in the DBA manuals.

3. If a user needs to find out what roles he/she has enabled and what objcet privileges they bring in :

	select * 
	from user_tab_privs_recd
	where grantor = <role_name>.

4. To find out which roles are granted to a particular user :

	select *
	from sys.dba_role_privs
	where grantee = <username>

5. To get what sys privs have been inherited from roles

	select *
	from sys.dba_sys_privs
	where grantee = <role>

This should cover your requirement

Hope this helps

-- 
Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia
Email : ssubrama_at_nibucorp.ccdn.otc.com.au / avion_at_ozemail.com.au

Disclaimer : All opinions are truly and just mine.

Received on Thu Nov 09 1995 - 00:00:00 CET