Oracle user privileges report

Oracle user priv
Oracle user privilege



from web


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

SQL> @priv

NAME
---------
ORCL

1 row selected.


TODAY
---------------------------------------------
July 08, 2011

1 row selected.


HOST_NAME
----------------------------------------------------------------
apt-amd-02

1 row selected.

The database has the following schema owners :


schema owner
------------------------------
ANUJ
ANUJREP
ANUJTEST
APEX_030200
APEX_040000
CTXSYS
EXFSYS
GGATE
HR
IX
MDSYS
OE
OLAPSYS
ORDDATA
ORDSYS
PM
RMAN
SH
WMSYS
XDB

20 rows selected.

Enter the name of Oracle user to show privileges for (return for all users) : ANUJ
old 12: and nvl(u.username,1) like upper(nvl('&&u_name','%'))
new 12: and nvl(u.username,1) like upper(nvl('ANUJ','%'))
old 25: and nvl(u.username,1) like upper(nvl('&&u_name','%'))
new 25: and nvl(u.username,1) like upper(nvl('ANUJ','%'))
old 38: and nvl(u.username,1) like upper(nvl('&&u_name','%'))
new 38: and nvl(u.username,1) like upper(nvl('ANUJ','%'))

July08,2011 List of Privileges For Database Users in ORCL on apt-amd-02

Object System
Username Object Owner Object Privileges Privileges Granted Role
-------------- -------------- ------------------------- ---------- ------------------------ ----------------------
ANUJ UNLIMITED TABLESPACE
CONNECT
RESOURCE