SQL>SET lines 100
COL privilege FOR a50
SELECT grantee, granted_role, default_role
FROM dba_role_privs
WHERE granted_role IN ('DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY 1,2;
GRANTEE GRANTED_ROLE DEF
------------------------------ ------------------------------ ---
ARJU DBA YES
DBA EXP_FULL_DATABASE YES
DBA IMP_FULL_DATABASE YES
FARUK DBA YES
MOMIN DBA YES
SYS DBA YES
SYS EXP_FULL_DATABASE YES
SYS IMP_FULL_DATABASE YES
SYSMAN DBA YES
SYSTEM DBA YES
TEST EXP_FULL_DATABASE YES
Check for granted privileges on directory objects
SET lines 110
COL privilege FOR a12
COL grantee FOR a25
COL owner FOR a25
SELECT p.grantee, p.privilege, p.owner, d.directory_name
FROM dba_tab_privs p, dba_directories d
WHERE p.table_name=d.directory_name
AND (grantee IN ('TEST', 'PUBLIC')
OR grantee IN (SELECT granted_role FROM dba_role_privs
WHERE grantee IN ('TEST', 'PUBLIC')))
ORDER BY 4,3,2;
A typical output is
GRANTEE PRIVILEGE OWNER DIRECTORY_NAME
-------------------- ---------- -------------------- -----------------
TEST READ SYS D
TEST WRITE SYS D
EXP_FULL_DATABASE READ SYS DATA_PUMP_DIR
EXP_FULL_DATABASE WRITE SYS DATA_PUMP_DIR
Directory Name : TEST
Check for tablespace quota
SET lines 100 numwidth 12
SELECT q.username, q.tablespace_name, q.bytes, q.max_bytes
FROM dba_ts_quotas q, dba_users u
WHERE q.username=u.username AND q.username in ('TEST');
USERNAME TABLESPACE_NAME BYTES MAX_BYTES
------------------------------ ------------------------------ ------------ ------------
TEST SYSTEM 65536 10485760
Thanks for your help on querying for who has access to directories
LikeLike
your welcome
LikeLike