Created
December 9, 2010 19:51
-
-
Save jkstill/735224 to your computer and use it in GitHub Desktop.
Find all privileges granted to a user
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- get_all_privs.sql | |
-- for a user, find all privileges | |
-- even if assigned recursively through a role | |
@clears | |
set pagesize 50000 | |
set linesize 120 | |
col source_user new_value source_user noprint | |
prompt | |
prompt Source User: | |
set feed off term off | |
select upper('&1') source_user from dual; | |
set feed on term on | |
with rolesource as ( | |
select grantee, granted_role | |
from dba_role_privs | |
where grantee in (select role from dba_roles) | |
union all | |
select grantee, granted_role | |
from dba_role_privs | |
where grantee = upper('&source_user') | |
), | |
roletree as ( | |
select grantee, granted_role | |
from rolesource | |
connect by prior granted_role = grantee | |
start with grantee = upper('&source_user') | |
), | |
roles as ( | |
select grantee, granted_role | |
from roletree | |
), | |
sysprivs as ( | |
select privilege | |
from dba_sys_privs | |
where grantee in (select grantee from roles) | |
union | |
select privilege | |
from dba_sys_privs | |
where grantee = upper('&source_user') | |
order by 1 | |
), | |
tabprivs as ( | |
select privilege, owner, table_name | |
from dba_tab_privs | |
where grantee in (select grantee from roles) | |
union | |
select privilege, owner, table_name | |
from dba_tab_privs | |
where grantee = upper('&source_user') | |
order by 1 | |
) | |
select 'SYSPRIV:' privtype, privilege from sysprivs | |
union | |
select 'TABPRIV:' privtype, privilege || ' on ' || owner || '.' || table_name | |
from tabprivs | |
order by 1,2 | |
/ | |
undef 1 2 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment