データ・ディクショナリ

DBA_ROLE_PRIVS ユーザに与えられているロール一覧

DBA_ROLE_PRIVS

どのユーザ、またはどのロールにどんなロールが付与されているのか調べる際に参照するのがDBA_ROLE_PRIVSです。(ロールは多段階の構成もありえますので注意が必要です。)

ロールにどんなシステム権限が付与されているのか確認するのがDBA_SYS_PRIVS。どんなオブジェクト権限が付与されているのか確認するのがDBA_TAB_PRIVSとなります。私はこれら3つのデータディクショナリをPRIVS三兄弟と呼んでいます。

DBA_ROLE_PRIVSの確認SQL

dba_rolesと結合しORACLE_MAINTAINED=’N’のロールがユーザ作成ロールです。=’Y’のロールはシステムが提供しているロールとなります。

select t.GRANTEE,t.GRANTED_ROLE,r.ORACLE_MAINTAINED,t.ADMIN_OPTION
from dba_role_privs t 
left outer join dba_roles r 
on t.GRANTED_ROLE = r.ROLE
where t.GRANTEE ='SOONI' order by 2
;
多段階構成を確認

実際のところユーザ作成ロールが付与されている場合って、結局はどんな権限なの?ってなりますよね。以下の通り直接付与されているのは「MY_ROLE」だけど、実際は「CONNECT」「RESOURCE」ロールが付与されている。というような事があります。このようにロールは多段階に構成する事可能ですが、あまり複雑にすると運用が大変になります。普段目立たないですが権限設計は重要です。

-- DBA_ROLE_PRIVSを参照する事でSOONIユーザはMY_ROLEを保持している事わかります。
                                          ORACLE   ADMIN
GRANTEE    GRANTED_ROLE                   MAINTAIN OPTION
---------- ------------------------------ -------- ------
SOONI      CONNECT                        Y        NO
SOONI      MY_ROLE                        N        NO   ←ユーザ作成ロールとわかる

SQL>

-- MY_ROLEは2つのロールを保持している事わかります。(ロールが多段階になっています)
SQL> select t.GRANTEE,t.GRANTED_ROLE,r.ORACLE_MAINTAINED,t.ADMIN_OPTION
  2  from dba_role_privs t
  3  left outer join dba_roles r
  4  on t.GRANTED_ROLE = r.ROLE
  5  where t.GRANTEE ='MY_ROLE' order by 2
  6  ;

                                          ORACLE   ADMIN
GRANTEE    GRANTED_ROLE                   MAINTAIN OPTION
---------- ------------------------------ -------- ------
MY_ROLE    CONNECT                        Y        NO
MY_ROLE    RESOURCE                       Y        NO

SQL>

、、で結局そのロールがどんな権限もっているのか、、という事でdba_sys_privsを参照する事になります。

-- それぞれ2つのロールは以下の権限を保持している事わかります。
SQL> select * from dba_sys_privs t where t.GRANTEE in ('CONNECT','RESOURCE')
  2  order by t.GRANTEE,t.PRIVILEGE
  3  ;

GRANTEE    PRIVILEGE                                ADMI COMM
---------- ---------------------------------------- ---- ----
CONNECT    CREATE SESSION                           NO   YES
CONNECT    SET CONTAINER                            NO   YES
RESOURCE   CREATE CLUSTER                           NO   YES
RESOURCE   CREATE INDEXTYPE                         NO   YES
RESOURCE   CREATE OPERATOR                          NO   YES
RESOURCE   CREATE PROCEDURE                         NO   YES
RESOURCE   CREATE SEQUENCE                          NO   YES
RESOURCE   CREATE TABLE                             NO   YES
RESOURCE   CREATE TRIGGER                           NO   YES
RESOURCE   CREATE TYPE                              NO   YES

10行が選択されました。

SQL>
SQL*Plus用
set lin 200
col grantee for a10
col granted_role for a30
col admin_option for a6
col delegate_option for a4
col default_role for a4
col common for a4
col oracle_maintained for a8
column oracle_maintained heading 'ORACLE|MAINTAINED'
column admin_option heading 'ADMIN|OPTION'
実際にどんなGRANT文が発行されていたのか以下で確認できます
スポンサーリンク
コピペで使う