【PL/SQL】作成や実行に必要な権限と確認・付与する方法

ここではORACLE社が提供するPL/SQL言語でのプログラム作成や実行に必要な権限と確認・付与する方法を紹介しています。

PL/SQLプログラムの作成や実行に必要な権限

PL/SQLプログラム（プロシージャ、ファンクション、パッケージ）の作成や実行に必要な権限は以下の通りです。

　作成：CREATE PROCEDURE、CREATE ANY PROCEDURE
　変更：ALTER ANY PROCEDURE
　削除：DROP ANY PROCEDURE
　実行：EXECUTE ANY PROCEDURE
　　　　または該当プログラムに対するEXECUTE権限

必要な権限の確認方法

ユーザに付与された権限は、DBA_SYS_PRIVSまたはUSER_SYS_PRIVSで確認できます。

DBA_SYS_PRIVSは全てのユーザの権限を確認することが出来ます。USER_SYS_PRIVSは現行ユーザに付与された権限のみを確認することが出来ます。

DBA_SYS_PRIVSの列は以下の通りです。
・GRANTEE
　権限受領者名、つまり、権限を受け取るユーザーまたはロール
・PRIVILEGE
　システム権限
・ADMIN_OPTION
　ADMINオプション付きで付与された場合はYES、そうでない場合はNO

SQLは次のように必要な権限を条件にすると付与されている権限されているかどうか一目で分かります。

-- 全てのユーザの中から権限を確認するSQL
SELECT a.PRIVILEGE FROM DBA_SYS_PRIVS a
WHERE 
    a.GRANTEE = 'ユーザ名'
    AND a.PRIVILEGE IN ('CREATE PROCEDURE'
                                    ,'CREATE ANY PROCEDURE'
                                    ,'ALTER ANY PROCEDURE'
                                    ,'DROP ANY PROCEDURE'
                                    ,'EXECUTE ANY PROCEDURE');

-- ログインユーザの権限を確認するSQL
SELECT a.PRIVILEGE FROM USER_SYS_PRIVS a
WHERE a.PRIVILEGE IN ('CREATE PROCEDURE'
                                    ,'CREATE ANY PROCEDURE'
                                    ,'ALTER ANY PROCEDURE'
                                    ,'DROP ANY PROCEDURE'
                                    ,'EXECUTE ANY PROCEDURE');

実行すると次のように結果が返ってきます。

SQL> SELECT a.PRIVILEGE FROM USER_SYS_PRIVS a
  2  WHERE a.PRIVILEGE IN ('CREATE PROCEDURE'
  3                                      ,'CREATE ANY PROCEDURE'
  4                                      ,'ALTER ANY PROCEDURE'
  5                                      ,'DROP ANY PROCEDURE'
  6                                      ,'EXECUTE ANY PROCEDURE');

PRIVILEGE
--------------------------------------------------------------------------------
ALTER ANY PROCEDURE
CREATE ANY PROCEDURE
CREATE PROCEDURE
DROP ANY PROCEDURE
EXECUTE ANY PROCEDURE

必要な権限を付与する方法

GRANT文：権限の付与

権限はGRANT文で付与することが出来ます。

権限を付与することが出来るユーザで「GRANT 権限名 TO ユーザ名」を実行すると付与することが出来ます。

PL/SQLの作成や実行に必要な権限は次のSQLで付与することが出来ます。

GRANT ALTER ANY PROCEDURE TO ユーザ名;
GRANT CREATE ANY PROCEDURE TO ユーザ名;
GRANT CREATE PROCEDURE TO ユーザ名;
GRANT DROP ANY PROCEDURE TO ユーザ名;
GRANT EXECUTE ANY PROCEDURE TO ユーザ名;

実行すると次のようになります。

SQL> GRANT ALTER ANY PROCEDURE TO USER1;

権限付与が成功しました。

SQL> GRANT CREATE ANY PROCEDURE TO USER1;

権限付与が成功しました。

SQL> GRANT CREATE PROCEDURE TO USER1;

権限付与が成功しました。

SQL> GRANT DROP ANY PROCEDURE TO USER1;

権限付与が成功しました。

SQL> GRANT EXECUTE ANY PROCEDURE TO USER1;

権限付与が成功しました。

全ての権限を1文のSQLで付与することもできます。

GRANT ALTER ANY PROCEDURE,CREATE ANY PROCEDURE,CREATE PROCEDURE,DROP ANY PROCEDURE,EXECUTE ANY PROCEDURE TO ユーザ名;

実行すると次のようになります。

SQL> GRANT ALTER ANY PROCEDURE,CREATE ANY PROCEDURE,CREATE PROCEDURE,DROP ANY PROCEDURE,EXECUTE ANY PROCEDURE TO USER1;

権限付与が成功しました。

REVOKE文：権限の取消

権限はREVOKE文で取消することが出来ます。

「REVOKE 権限名 FROM ユーザ名」で権限を取り消しすることが出来ます。

PL/SQLの作成や実行に必要な権限は次のSQLで取消しすることが出来ます。

REVOKE ALTER ANY PROCEDURE FROM ユーザ名;
REVOKE CREATE ANY PROCEDURE FROM ユーザ名;
REVOKE CREATE PROCEDURE FROM ユーザ名;
REVOKE DROP ANY PROCEDURE FROM ユーザ名;
REVOKE EXECUTE ANY PROCEDURE FROM ユーザ名;

実行すると次のようになります。

SQL> REVOKE ALTER ANY PROCEDURE FROM USER1;

取消しが成功しました。

SQL> REVOKE CREATE ANY PROCEDURE FROM USER1;

取消しが成功しました。

SQL> REVOKE CREATE PROCEDURE FROM USER1;

取消しが成功しました。

SQL> REVOKE DROP ANY PROCEDURE FROM USER1;

取消しが成功しました。

SQL> REVOKE EXECUTE ANY PROCEDURE FROM USER1;

取消しが成功しました。

全ての権限を1文のSQLで取消することもできます。

REVOKE ALTER ANY PROCEDURE,CREATE ANY PROCEDURE,CREATE PROCEDURE,DROP ANY PROCEDURE,EXECUTE ANY PROCEDURE FROM USER1;

実行すると次のようになります。

SQL> REVOKE ALTER ANY PROCEDURE,CREATE ANY PROCEDURE,CREATE PROCEDURE,DROP ANY PROCEDURE,EXECUTE ANY PROCEDURE FROM USER1;

取消しが成功しました。