Dの小部屋

忘れやすい私のメモ帳

システム権限の表示(ロールに付加している権限も表示)

 

SQL

with roleinfo as (
     select
       connect_by_root grantee as grantee,
       granted_role
     from sys.dba_role_privs
     start with grantee in (select username from dba_users)
     connect by prior granted_role = grantee
)
select distinct grantee, privilege from (
     select grantee, privilege from sys.dba_sys_privs
     union all
     select r.grantee, p.privilege
     from
          sys.dba_sys_privs p,
          roleinfo r
     where p.grantee = r.granted_role
)
where grantee = upper('###ユーザ名###')
order by privilege
;

 


test_roleユーザの状況

test_role
→UNLIMITED TABLESPACE (権限)
→ttt_role4
  →ttt_role3
    →CREATE LIBRARY (権限)
    →ttt_role2
      →ttt_role1
        →CREATE SESSION (権限)
        →ttt_role0
          →CREATE SESSION (権限)
      →ttt_role21
        →ttt_role22
          →ttt_role23
            →CREATE VIEW (権限)
※ttt_role*は全てロール

結果

GRANTEE     PRIVILEGE
----------- ----------------------------------------
TEST_ROLE   CREATE LIBRARY
TEST_ROLE   CREATE SESSION
TEST_ROLE   CREATE SESSION
TEST_ROLE   CREATE VIEW
TEST_ROLE   UNLIMITED TABLESPACE