あるユーザではアクセスできるのに、こちらのユーザでは権限エラーが出てしまう。権限の付与をしてあげればいいのだろうけど、いちいちDBA_ROLE_PRIVSや、DBA_TAB_PRIVS、DBA_SYS_PRIVSを調べるのめんどいよ、、、って時にお手軽な方法があります。
DBMS_METADATA.GET_GRANTED_DDLを使って取得
DBMS_METADATA.GET_GRANTED_DDLを利用すればロール名やスキーマ名をパラメータにして付与されているロールの「grant文」を取得できます。基本はSQL*PlusやSQLclで出力する事おすすめします。SQL DeveloperやOsqlEditなどのツールを通して出力する場合「VARCHAR2」のマックスサイズに納まる結果セットであればよいですが、超えてしまう場合は途中で切れてしまいますのでご注意くだしさい。(set xxxについてはこちらで少し解説しています。)以下はロール情報のDDLを取得する例です。
SET LINESIZE 100 SET LONG 5000000 SET LONGCHUNKSIZE 5000000
select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT') from dual ;
SQL> SET LINESIZE 100
SQL> SET LONG 5000000
SQL> SET LONGCHUNKSIZE 5000000
SQL> select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT') from dual ;
DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT')
----------------------------------------------------------------------------------------------------
GRANT "CONNECT" TO "SCOTT"
GRANT "RESOURCE" TO "SCOTT"
GRANT "SELECT_CATALOG_ROLE" TO "SCOTT"
SQL>
パラメータを切り替える事で各種情報のDDL取得が可能
上記はロール情報取得でしたが、以下の通りパラメータを切り替える事で各種情報の取得が可能です。
ディクショナリビュー | GET_GRANTED_DDLへ渡すパラメータ | 解説 |
DBA_ROLE_PRIVS | ROLE_GRANT | ロール情報 |
DBA_TAB_PRIVS | OBJECT_GRANT | オブジェクト権限情報 |
DBA_SYS_PRIVS | SYSTEM_GRANT | システム権限情報 |
付与されている権限がない場合は以下のようなエラーが出ます。
ORA-31608: タイプOBJECT_GRANTの指定されたオブジェクトが見つかりません。
ORA-31608: タイプSYSTEM_GRANTの指定されたオブジェクトが見つかりません。
dba_role_privsを参照して自作するケース
SQL*Plusが使えない環境でかつVARCHAR2のマックスサイズを超えてしまう場合は、各種ディクショナリビューを参照して自分で作成する事もできます。以下はdba_role_privsを参照して自作した例です。
select 'GRANT '||r.GRANTED_ROLE||' TO '||r.GRANTEE||';' from dba_role_privs r where r.GRANTEE='SCOTT' ;
SQL> select 'GRANT '||r.GRANTED_ROLE||' TO '||r.GRANTEE||';' from dba_role_privs r where r.GRANTEE='SCOTT';
'GRANT'||R.GRANTED_ROLE||'TO'||R.GRANTEE||';'
--------------------------------------------------------------------------------
GRANT RESOURCE TO SCOTT;
GRANT SELECT_CATALOG_ROLE TO SCOTT;
GRANT CONNECT TO SCOTT;
SQL>