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).