Monday, July 14, 2008

Show All Direct And Indirect Privileges Of A Role Or User

Usually, privileges are granted via roles. Sometimes, roles are then granted to other roles, roles are granted to users and privileges are granted directly to users.

Also, there are different kinds of privlileges: object and system privileges.

The following statement gives an easy overview of all privileges that are granted to a user (or role):

select owner
, object_name
, grantee
, grant_path
, privilege
from (
select connect_by_root owner as owner
, connect_by_root object_name as object_name
, grantee
, connect_by_isleaf
, level as grant_level
, sys_connect_by_path (grantee,'/') as grant_path
, connect_by_root privilege as privilege
from (
select grantee, owner, table_name object_name, privilege
from dba_tab_privs
union all
select grantee, 'SYSPRIV', privilege, null
from dba_sys_privs
union all
select grantee, 'ROLE', granted_role, null
from dba_role_privs
) p
connect by prior p.grantee = p.object_name
start with owner != 'ROLE'
where grantee = 'MY_ROLE_OR_SCHEMA'

The column grant_path shows the whole path of the grant (i.e. all the roles the privilege was granted through).

No comments: