set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 130
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

PROMPT
PROMPT ###################################################################### 
PROMPT 
PROMPT --  Name          : listUserPrivilegesInfo.sql
PROMPT     
PROMPT --  Copyright (c) DTA Inc., All Rights Reserved (2003)
PROMPT 
PROMPT --  Description   : This script will get a list of of users
PROMPT                     and the roles, object privs and system privs they've been assigned.
PROMPT                     It can be used to create a report of Users and the privileges and
PROMPT                     roles they've been assigned, for cleaning up security.
PROMPT 
PROMPT --  Input(s)     : None
PROMPT 
PROMPT --  Output       : The output is in a file named userPrivilegesInfo_<DBNAME>_<HOST>.lst
PROMPT 
PROMPT --  Note         : Run this script while connected as the SYS user
PROMPT ######################################################################
PROMPT 

column "Username"     format a14  heading "Username" 
column "Obj Owner"    format a14  heading "Object Owner" wrap
column "Obj"          format a25  heading "Object"       wrap
column "Obj Priv"     format a10  heading "Object|Privileges" wrap
column "Sys Priv"     format a24  heading "System|Privileges" wrap
column "Granted Role" format a22  heading "Granted Role" wrap
column "dummy"        noprint

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name FROM v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today FROM dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set termout on

PROMPT The database has the following schema owners : 
PROMPT
-- Get list of segment owners
select distinct(owner) "schema owner" from dba_segments
where owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'OUTLN', 'DBSNMP')
order by owner;

-- allow user name to be entered.  NULL/return key is for all locks/users
accept u_name prompt 'Enter the name of Oracle user to show privileges for (return for all users) : ' 

--set termout off
set feedback off

set head on

spool userPrivilegesInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
       center "List of Privileges For Database Users in "  &s_dbname " on " &s_machine skip 2

break on Username skip 1

select username "Username",
       owner "Obj Owner",
       table_name "Obj",
       privilege "Obj Priv",
       ' ' "Sys Priv",
       ' ' "Granted Role",
       1 "dummy"
from dba_users u,
     dba_tab_privs t
where u.username = t.grantee
and u.username not in ('SYS','SYSTEM','DBSNMP')
and nvl(u.username,1) like upper(nvl('&&u_name','%'))
union
select username,
       ' ',
       ' ',
       ' ',
       privilege,
       ' ',
       2 "dummy"
from dba_users u,
     dba_sys_privs s
where u.username = s.grantee
and u.username not in ('SYS','SYSTEM','DBSNMP')
and nvl(u.username,1) like upper(nvl('&&u_name','%'))
union
select username,
       ' ',
       ' ',
       ' ',
       ' ',
       granted_role,
       3 "dummy"
from dba_users u, 
     dba_role_privs r
where u.username = r.grantee
and u.username not in ('SYS','SYSTEM','DBSNMP')
and nvl(u.username,1) like upper(nvl('&&u_name','%'))
order by 1, 7;

spool off

ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on 

PROMPT
PROMPT -- If file is empty, no user information was found for the database &s_dbname
PROMPT --                or no user information was found for the username &u_name
PROMPT
PROMPT
PROMPT Output file userPrivilegesInfo_&s_dbname._&s_machine..LST produced
PROMPT

undefine u_name

@sqlplus_settings