Differences

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

--- user_his_roles_and_privileges [2018/03/28 15:52]
admin created
+++ user_his_roles_and_privileges [2018/03/28 15:54] (current)
admin
@@ Line 1: / Line 1: @@
---# Privs : From Viorel
+--# Privs :
-select
+  select
   lpad(' ', 2*level) || granted_role "User, his roles and privileges"
-from
+  from
   (
   /* THE USERS */
@@ Line 27: / Line 27: @@
       dba_sys_privs
   )
-start with grantee is null
+  start with grantee is null
-connect by grantee = prior granted_role;
+  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