Tuesday, June 5, 2012

How to check whether user has datapump privilege

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
 

2 comments:

  1. Thanks for your help on querying for who has access to directories

    ReplyDelete