DBA_ROLE_PRIVS
どのユーザ、またはどのロールにどんなロールが付与されているのか調べる際に参照するのがDBA_ROLE_PRIVSです。（ロールは多段階の構成もありえますので注意が必要です。）
ロールにどんなシステム権限が付与されているのか確認するのがDBA_SYS_PRIVS。どんなオブジェクト権限が付与されているのか確認するのがDBA_TAB_PRIVSとなります。私はこれら３つのデータディクショナリを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は２つのロールを保持している事わかります。（ロールが多段階になっています）
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を参照する事になります。
-- それぞれ２つのロールは以下の権限を保持している事わかります。
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'
