Oracleデータベースの既存DBユーザーのCREATE USER文とGRANT文を作成する方法

既存のOracleデータベースに存在するユーザーを別のデータベースにも作成したい場合や、テスト用のデータベースを作成する際に全く同じOracleユーザーを作成したい場合があります。また、既存のOracleユーザーの権限を確認したい場合もあります。そのような場合に、どうやって同じユーザーの情報を取得すればよいのでしょうか。そのような場合に利用できるSQLを紹介します。

Oracleデータベースでユーザーを作成するには、CREATE USER文でユーザーを作成し、その後GRANT文で適切な権限を割り当ててあげる必要があります。それらのSQLを全て既存のOracleデータベースから取得するSQLを紹介しています。

既存OracleユーザーのCREATE文とGRANT文を作成する方法
まとめ

既存OracleユーザーのCREATE文とGRANT文を作成する方法

Oracleデータベースに既に存在するユーザーのCREATE USER文やGRANT文を生成するには次の一連のSQLを実行することで取得可能です。このSQLでは、「ACCEPT username PROMPT ‘Enter UserName : ‘」という箇所でユーザー名の入力を求められますので、CREATE USER文を生成したいユーザーを入力します。すると、その後のSQLで指定されたユーザーのCREATE USER文、GRANT文を取得して画面に出力します。

SET LINESIZE 200 LONG 100000 PAGESIZE 0 HEAD OFF VERIFY OFF FEEDBACK OFF
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);

ACCEPT username PROMPT 'Enter UserName : '

SELECT DBMS_METADATA.GET_DDL('USER','&&username') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&username') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&username') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&username') FROM DUAL;

こちらのSQLを実行することによって、以下のようにCREATE USER文とGRANT文を全て取得することが出来ます。ここではTESTUSERというユーザー名を指定して、TESTUSERのCREATE USER文とGRANT文を全て取得しています。

SQL> SET LINESIZE 200 LONG 100000 PAGESIZE 0 HEAD OFF VERIFY OFF FEEDBACK OFF
SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);

SQL> ACCEPT username PROMPT 'Enter UserName : '
Enter UserName : TESTUSER

SQL> SELECT DBMS_METADATA.GET_DDL('USER','&&username') FROM DUAL;


   CREATE USER "TESTUSER"
      DEFAULT TABLESPACE "TESTTAB"
      TEMPORARY TABLESPACE "TEMP";


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '&&username') FROM DUAL;


  GRANT ADVISOR TO "TESTUSER";

  GRANT CREATE MATERIALIZED VIEW TO "TESTUSER";

  GRANT CREATE SYNONYM TO "TESTUSER";

  GRANT UNLIMITED TABLESPACE TO "TESTUSER";


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', '&&username') FROM DUAL;


   GRANT "CONNECT" TO "TESTUSER";

   GRANT "RESOURCE" TO "TESTUSER";

   GRANT "SELECT_CATALOG_ROLE" TO "TESTUSER";

   GRANT "PLUSTRACE" TO "TESTUSER";


SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', '&&username') FROM DUAL;


  GRANT EXECUTE ON "SYS"."DBMS_CRYPTO" TO "TESTUSER";

  GRANT UPDATE ON "TESTDB"."TEST_TABLE" TO "TESTUSER";

  GRANT INSERT ON "TESTDB"."TEST_TABLE" TO "TESTUSER";

  GRANT DELETE ON "TESTDB"."TEST_TABLE" TO "TESTUSER";

  GRANT UPDATE ON "TESTDB"."WK_TABLE" TO "TESTUSER";

  GRANT INSERT ON "TESTDB"."WK_TABLE" TO "TESTUSER";

  GRANT DELETE ON "TESTDB"."WK_TABLE" TO "TESTUSER";

  GRANT READ ON DIRECTORY "DPUMP_DIR1" TO "TESTUSER";

  GRANT WRITE ON DIRECTORY "DPUMP_DIR1" TO "TESTUSER";

一度にCREATE USER文やGRANT文を取得したい場合には、次のように各SQLをUNION ALLで連結するとよいでしょう。ただし、ユーザーによってはいくつかの権限が付与されていない場合がありますので、その部分ではエラーになることがありますので、エラーが発生した場合には１つづつ確認しましょう。

SET LINESIZE 200 LONG 100000 PAGESIZE 0 HEAD OFF VERIFY OFF FEEDBACK OFF
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);

ACCEPT username PROMPT 'Enter UserName : '

SELECT DBMS_METADATA.GET_DDL('USER','&&username') FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','&&username') FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','&&username') FROM DUAL
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&&username') FROM DUAL;