| Select ALL privileges of a particular user [message #19529] |
Mon, 25 March 2002 05:26  |
Lissa Wong
Messages: 4
Registered: March 2002
|
Junior Member |
|
|
How do I list all system privileges granted to a particular user (whether directly or indirectly)? It includes privileges granted to a user directly, through the roles and to roles through another role to that user. I tried retrieving from "USER_SYS_PRIVS" but system privileges granted to a user via a role to another role (i.e: Grant role1 to role2; Grant role2 to myuser) are not displayed. I am using the username as the parameter of the SQL statement.
Any help is greatly appreciated.
|
|
|
|
| Re: Select ALL privileges of a particular user [message #19530 is a reply to message #19529] |
Mon, 25 March 2002 05:46  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
This should get you on the right track.
set echo off
set verify off
set pages 200
col granted_role form a20
col owner form a12
col table_name form a27
col privilege form a27
ACCEPT username prompt 'Enter Username : '
spool privs_&username..lst
PROMPT Roles granted to user
SELECT granted_role,admin_option,default_role
FROM dba_role_privs
WHERE grantee=UPPER('&username');
PROMPT Table Privileges granted to a user through roles
SELECT granted_role, owner, table_name, privilege
FROM ( SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
UNION
SELECT granted_role
FROM role_role_privs
WHERE role in (SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
)
) roles, dba_tab_privs
WHERE granted_role=grantee;
PROMPT System Privileges assigned to a user through roles
SELECT granted_role, privilege
FROM ( SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
UNION
SELECT granted_role
FROM role_role_privs
WHERE role in (SELECT granted_role
FROM dba_role_privs WHERE grantee=UPPER('&username')
)
) roles, dba_sys_privs
WHERE granted_role=grantee;
PROMPT Table privileges assigned directly to a user
SELECT owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee=UPPER('&username');
PROMPT System privileges assigned directly to a user
SELECT privilege, admin_option
FROM dba_sys_privs
WHERE grantee=UPPER('&username');
spool off
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
