-- 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