Solaris Systems Wiki

–# Privs :

select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
  select
    null     grantee,
    username granted_role
  from
    dba_users
  where
    username like upper('%&enter_username%')
/* THE ROLES TO ROLES RELATIONS */
union
  select
    grantee,
    granted_role
  from
    dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
  select
    grantee,
    privilege
  from
    dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;

–# Privileges

select * from (
select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE from dba_tab_privs where lower(grantee)='&username'
  union -- # looking up table rights of granted roles
select 'Role: '||ROLE, OWNER, TABLE_NAME,  NULL,PRIVILEGE from ROLE_TAB_PRIVS where role in (select 
GRANTED_ROLE from dba_role_privs where lower(grantee)='&&username')
  union -- # looking up table rights of roles granted to GRANTED_ROLE - (2nd level)
select 'Role-Role:'|| ROLE, OWNER, TABLE_NAME,  NULL, PRIVILEGE from ROLE_TAB_PRIVS where role in
       (select GRANTED_ROLE from ROLE_ROLE_PRIVS where ROLE in (select GRANTED_ROLE from dba_role_privs 
where lower(GRANTEE)='&&username'))
)order by grantee ;