Differences

This shows you the differences between two versions of the page.

--- user_his_roles_and_privileges [2018/03/28 15:53]
admin
+++ user_his_roles_and_privileges [2018/03/28 15:54] (current)
admin
@@ Line 29: / Line 29: @@
   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 ;

Solaris Systems Wiki