Sunday, November 17, 2013

Grants given to objects through Roles for a Schema & without Roles (Direct)

Hi Folks,

 

The following queries are very much useful to get the list of Grants given to Roles for a particular schema objects & to List the direct grants given to users.

 

Grants given to objects through Roles for a Schema

**************************************************

SELECT TP.OWNER,TP.GRANTEE,RP.GRANTEE, TP.TABLE_NAME, TP.PRIVILEGE FROM DBA_TAB_PRIVS TP, DBA_ROLE_PRIVS RP WHERE TP.GRANTEE = RP.GRANTED_ROLE AND TP.OWNER='Schema Name'

ORDER BY 1,2,3 

 

 

Individual Grants given to objects without using Roles:

********************************************************

SELECT TP.OWNER,TP.GRANTEE,TP.TABLE_NAME, TP.PRIVILEGE FROM dba_tab_privs TP

where TP.OWNER='<Schema Name>' AND GRANTEE NOT IN (Select ROLE from DBA_ROLES) ORDER BY 2,3,4

 

Thanks & Regards

Fauzi

 

No comments: