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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.