CASS

--- privs.sql list of privileges for user or role
set pages 1000 lines 160 

col grantee format a20
col privilege format a30
col OWNER format a20
col lvl format a12
col table_name format a20
col column_name format a20 

set termout off serveroutput off heading off timing off
col title_name new_value title_name
select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from  v$instance;
set termout on serveroutput on heading on 

TTitle Left '###   Users directly granted privs from: dba_col_privs, dba_role_privs, dba_sys_privs, dba_tab_privs -  ' title_name skip 2 

--- users from dba_users 

select grantee,lvl,privilege,grantable,owner,table_name,column_name
from (
   select grantee,'Column' lvl,privilege,grantable,owner,table_name,column_name from dba_col_privs
   union
   select grantee,'Role' GrType,granted_role obj,admin_option a, null,null,null from dba_role_privs
   union
   select grantee,'Sys Priv',privilege,admin_option,null,null,null from dba_sys_privs
   union
   select grantee,'table',privilege,grantable,owner,table_name,null from dba_tab_privs  where privilege !='EXECUTE'
   union
   select grantee,'Program', privilege,grantable,owner,table_name,null from dba_tab_privs  where  privilege ='EXECUTE'
)
where grantee like  '&GRANTEE%%'; 

TTitle off