ORACLE 権限付与DDLの取得

あるユーザではアクセスできるのに、こちらのユーザでは権限エラーが出てしまう。権限の付与をしてあげればいいのだろうけど、いちいち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取得が可能

上記はロール情報取得でしたが、以下の通りパラメータを切り替える事で各種情報の取得が可能です。

付与されている権限がない場合は以下のようなエラーが出ます。
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>