Re: HELP

I figured out a solution to Q1 as fellow: (Please help to work on Q2, Thanks)

--FileNmae roletree.sql
--Usage: @roletree root_role_name

create table role_temp as select * from role_role_privs nologging /
select lpad(' ',2*(level-1))||granted_role||' is granted to '||role||decode(admin_option,
'YES', ' With Admin Option', null) "Role Relation" from role_temp connect by prior role=granted_role
start with granted_role = upper('&1')
/
drop table role_temp
/

In article <HfuX6.1$yp1.61_at_www.newsranger.com>, OracleDBA says...
>
>1. Assume many roles are nested granted, like role1 granted to role2, and role2
>to role3 and etc, how to generate a tree view of their connection?
>
>2. How to display the DETAIL object prviledge of all USERS (only, not role)
>granted either directly or by roles for a know table(provide OWNER and
>TABLE_NAME). Preferably with info of direct or role name. I can get it right if
>there is only one level role involved.
>
>Many thanks.
>
>-- Mr. Daniel A. Morgan, Please refrain from posting your nonsense to MY post.
>
>So far I get (slow performance too):
>
>
>--File_name tabgrant.sql
>--usage: @tabgrant table_owner table_name
>set echo off
>set verify off
>set pagesize 200
>
>col "USER" format a10
>col "Direct/Role" format a12
>col privilege format a12
>col "Grantor" format a10
>col "Grantable" format a10
>
>break on "USER" on "Direct/Role"
>
>SELECT grantee "USER", 'Direct' "Direct/Role", grantor "Grantor", privilege,
>grantable
>"Grantable"
>FROM dba_tab_privs
>WHERE table_name = upper('&2') and
>owner = upper('&1') and grantee not in (select role from dba_roles)
>UNION
>SELECT a.grantee "USER", 'By Role' "Direct/Role", a.granted_role "Grantor",
>b.privilege, a.admin_option "Grantable"
>FROM dba_role_privs a, dba_tab_privs b
>WHERE a.granted_role in (select grantee
>from dba_tab_privs
>where table_name = upper('&2') and
>owner = upper('&1') and grantee in (select role from dba_roles)
>UNION select role from role_role_privs
>where granted_role=a.grantee)
>/
>
>
Received on Tue Jun 19 2001 - 10:48:00 CDT