Sign in or 
Share this
Determine Roles and System/Table Privileges Granted to Users
set lines 110 pages 1000 ver off
col role for a16
col pv for a75 hea 'PRIVILEGE OR ROLE'
bre on role on type skip 1
define usercheck = 'SH'
select grantee, 'ROL' type, granted_role pv
from dba_role_privs where grantee = '&usercheck' union
select grantee, 'PRV' type, privilege pv
from dba_sys_privs where grantee = '&usercheck' union
select grantee, 'OBJ' type,
max(decode(privilege,'WRITE','WRITE,'))||max(decode(privilege,'READ','READ'))||
max(decode(privilege,'EXECUTE','EXECUTE'))||max(decode(privilege,'SELECT','SELECT'))||
max(decode(privilege,'DELETE',',DELETE'))||max(decode(privilege,'UPDATE',',UPDATE'))||
max(decode(privilege,'INSERT',',INSERT'))||' ON '||object_type||' "'||a.owner||'.'||table_name||'"' pv
from dba_tab_privs a, dba_objects b
where a.owner=b.owner and a.table_name = b.object_name and a.grantee='&usercheck'
group by a.owner,table_name,object_type,grantee union
select username grantee, '---' type, 'empty user ---' pv from dba_users
where not username in (select distinct grantee from dba_role_privs) and
not username in (select distinct grantee from dba_sys_privs) and
not username in (select distinct grantee from dba_tab_privs) and username like '%&usercheck%'
group by username
order by grantee, type, pv;
GRANTEE........................TYP PRIVILEGE OR ROLE
------------------------------ --- ---------------------------------------------------------------------------
SH............................ OBJ EXECUTE ON PACKAGE "SYS.DBMS_STATS"
SH................................ EXECUTE ON PACKAGE BODY "SYS.DBMS_STATS"
SH................................ READ ON DIRECTORY "SYS.DATA_FILE_DIR"
SH................................ READ ON DIRECTORY "SYS.LOG_FILE_DIR"
SH............................ PRV ALTER SESSION
SH................................ CREATE CLUSTER
SH................................ CREATE DATABASE LINK
SH................................ CREATE DIMENSION
SH................................ CREATE MATERIALIZED VIEW
SH................................ CREATE SEQUENCE
SH................................ CREATE SESSION
SH................................ CREATE SYNONYM
SH................................ CREATE TABLE
SH................................ CREATE VIEW
SH................................ QUERY REWRITE
SH................................ UNLIMITED TABLESPACE
SH............................ ROL CWM_USER
SH................................ RESOURCE
SH................................ SELECT_CATALOG_ROLE
SH_EMPTY_USER................. --- empty user ---
col role for a16
col pv for a75 hea 'PRIVILEGE OR ROLE'
bre on role on type skip 1
define usercheck = 'SH'
select grantee, 'ROL' type, granted_role pv
from dba_role_privs where grantee = '&usercheck' union
select grantee, 'PRV' type, privilege pv
from dba_sys_privs where grantee = '&usercheck' union
select grantee, 'OBJ' type,
max(decode(privilege,'WRITE','WRITE,'))||max(decode(privilege,'READ','READ'))||
max(decode(privilege,'EXECUTE','EXECUTE'))||max(decode(privilege,'SELECT','SELECT'))||
max(decode(privilege,'DELETE',',DELETE'))||max(decode(privilege,'UPDATE',',UPDATE'))||
max(decode(privilege,'INSERT',',INSERT'))||' ON '||object_type||' "'||a.owner||'.'||table_name||'"' pv
from dba_tab_privs a, dba_objects b
where a.owner=b.owner and a.table_name = b.object_name and a.grantee='&usercheck'
group by a.owner,table_name,object_type,grantee union
select username grantee, '---' type, 'empty user ---' pv from dba_users
where not username in (select distinct grantee from dba_role_privs) and
not username in (select distinct grantee from dba_sys_privs) and
not username in (select distinct grantee from dba_tab_privs) and username like '%&usercheck%'
group by username
order by grantee, type, pv;
GRANTEE........................TYP PRIVILEGE OR ROLE
------------------------------ --- ---------------------------------------------------------------------------
SH............................ OBJ EXECUTE ON PACKAGE "SYS.DBMS_STATS"
SH................................ EXECUTE ON PACKAGE BODY "SYS.DBMS_STATS"
SH................................ READ ON DIRECTORY "SYS.DATA_FILE_DIR"
SH................................ READ ON DIRECTORY "SYS.LOG_FILE_DIR"
SH............................ PRV ALTER SESSION
SH................................ CREATE CLUSTER
SH................................ CREATE DATABASE LINK
SH................................ CREATE DIMENSION
SH................................ CREATE MATERIALIZED VIEW
SH................................ CREATE SEQUENCE
SH................................ CREATE SESSION
SH................................ CREATE SYNONYM
SH................................ CREATE TABLE
SH................................ CREATE VIEW
SH................................ QUERY REWRITE
SH................................ UNLIMITED TABLESPACE
SH............................ ROL CWM_USER
SH................................ RESOURCE
SH................................ SELECT_CATALOG_ROLE
SH_EMPTY_USER................. --- empty user ---
olapdba |
Latest page update: made by olapdba
, Apr 22 2008, 4:42 PM EDT
(about this update
About This Update
Edited by olapdba
6 words added view changes - complete history) |
|
Keyword tags:
None
More Info: links to this page
|
There are no threads for this page.
Be the first to start a new thread.
