Useful Query To check Some Privileges

Check for whether user has full database export/import privilege 


Query : 



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;

Check for user has create table or create session privilege.


Query 

SQL>SET lines 100 pages 100
COL privilege FOR a40
SELECT grantee, privilege
FROM dba_sys_privs
WHERE (grantee IN ('TEST', 'PUBLIC')
OR grantee IN (SELECT granted_role FROM dba_role_privs
WHERE grantee IN ('TEST', 'PUBLIC')))
AND privilege IN ('CREATE SESSION', 'CREATE TABLE')
ORDER BY 1,2;

Check for granted privileges on directory objects.


Query :

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;

Thank You
osama Mustafa