こんにちは。インストラクターの蓑島です。

前回は、実行者権限プロシージャがセキュリティ強化に役立つことを解説しました。
そしてそのためにはPL/SQLパッケージとロールを使うことで管理が簡単であることを最後に予告して、その詳細は今回でということでしたね。

早速、具体例で解説します。まずPL/SQLパッケージを使わない場合です。

例えばユーザに応じたセキュリティ管理ということで、SCOTT.EMP表に対してSELECTしかできないユーザと、SELECTの他、UPDATEもできるユーザを考えてみましょう。
それぞれSELECT_USERユーザ、UPDATE_USERユーザという名前で作成し権限を与えます。

--  ここから  ----------------------------------------------
SQL> show user
ユーザーは"SYSTEM"で
す。                     -- 管理者ユーザで
  
  -- SELECT_USERユーザを作り
SQL> CREATE USER SELECT_USER IDENTIFIED BY SELECT_USER;

ユーザーが作成されました。
      　　　　　　　　　　　　　 --  SCOTT.EMPのSELECT権限付与
SQL> GRANT SELECT ON SCOTT.EMP TO SELECT_USER;

権限付与が成功しました。
   　　　　　　　　　　　　　　　 -- UPDATE_USERユーザを作り
SQL> CREATE USER UPDATE_USER IDENTIFIED BY UPDATE_USER;

ユーザーが作成されました。
                                  --  SCOTT.EMPのSELECTとUPDATE権限付与
SQL> GRANT SELECT, UPDATE ON SCOTT.EMP TO   UPDATE_USER;

権限付与が成功しました。
                           -- データベースに接続できるようにCONNECTロール付与　
SQL> GRANT CONNECT TO SELECT_USER, UPDATE_USER;

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

--  ここまで  ----------------------------------------------

次に、SCOTT.EMPをSELECTするPROC_SELECT_EMPプロシージと、SCOTT.EMPをUPDATEするPROC_UPDATE_EMPプロシージャをパッケージ化せずに作成します。

--  ここから  ----------------------------------------------
SQL> show user
ユーザーは"SCOTT"です。

SQL> CREATE OR REPLACE PROCEDURE PROC_SELECT_EMP
  2   (P_EMPNO IN NUMBER)
  3   AUTHID CURRENT_USER        -- 実行者権限の指定をする
  4  IS
  5    V_ENAME   VARCHAR2(50);
  6  BEGIN

  7    SELECT ENAME INTO V_ENAME FROM SCOTT.EMP
  8    WHERE  EMPNO = P_EMPNO;
  9    DBMS_OUTPUT.PUT_LINE('社員名=' || V_ENAME);
 10  END;
 11  /

プロシージャが作成されました。

SQL> CREATE OR REPLACE PROCEDURE PROC_UPDATE_EMP
  2   (P_EMPNO IN NUMBER,
  3    P_ENAME IN VARCHAR2)
  4    AUTHID CURRENT_USER        -- 実行者権限の指定をする
  5  IS
  6  BEGIN
  7    UPDATE SCOTT.EMP
  8    SET  ENAME = P_ENAME
  9    WHERE EMPNO = P_EMPNO;
 10  END;
 11  /

プロシージャが作成されました。
--  ここまで  ----------------------------------------------

これで、SCOTT.EMPをSELECTするプロシージャと、UPDATEする実行者権限のプロシージャをそれぞれ作成しました。

次にこれらのプロシージャの実行権限を上記のユーザに与える訳ですが、
SELECT_USERユーザには、SCOTT.PROC_SELECT_EMPプロシージャの実行権限のみ与え、UPDATE_USERユーザには、SCOTT.PROC_SELECT_EMPプロシージャと、SCOTT.PROC_UPDATER_EMPプロシージャの実行権限を与えることになります。
しかしこのような管理は複雑です。
一般にひとつのシステムで用いるプロシージャやファンクションの数は多くなりますし、ユーザの数も多くなります。
それらたくさんのプロシージャやファンクションの実行権限を選択的にきめ細かく個々のユーザに付与するのは複雑です。

そこでこれらの実行者権限のプロシージャやファンクションをパッケージにまとめれば管理が容易になります。

パッケージはその中にプロシージャやファンクション等を含むわけですが、それら個々のプロシージャ、ファンクションに対しては実行者権限の指定はできません。
パッケージに対してはパッケージ全体に対して実行者権限の指定ということになります。

さらに、パッケージ内の個々のプロシージャやファンクションの実行権限をユーザに与えるということもできません。
パッケージはパッケージ全体の実行権限をユーザに付与するということになります。

このようにプロシージャやファンクションをパッケージにより一つにまとめることで管理が簡素化できるわけです。

早速、上記のプロシージャをPAC1という実行者権限のパッケージにまとめます。
パッケージ化の構文は仕様部、本体とありますが、詳しくはバックナンバー第27回、28回をご覧ください。

--  ここから  ----------------------------------------------
SQL> SHOW USER
ユーザーは"SCOTT"です。

SQL> CREATE OR REPLACE PACKAGE PAC1   -- パッケージ仕様をつくる
  2  AUTHID CURRENT_USER   -- ここでパッケージ全体の実行者権限の指定
  3  IS
  4
  5  PROCEDURE PROC_SELECT_EMP
  6   (P_EMPNO IN NUMBER);
  7
  8  PROCEDURE PROC_UPDATE_EMP
  9   (P_EMPNO IN NUMBER,
 10    P_ENAME IN VARCHAR2);
 11
 12  END PAC1;
 13  /

パッケージが作成されました。

SQL> CREATE OR REPLACE PACKAGE BODY PAC1    --次にパッケージ本体を作る
  2  IS
  3  PROCEDURE  PROC_SELECT_EMP
  4   (P_EMPNO IN NUMBER)
  5  IS
  6    V_ENAME   VARCHAR2(50);
  7  BEGIN
  8    SELECT ENAME INTO V_ENAME FROM SCOTT.EMP
  9    WHERE  EMPNO = P_EMPNO;
 10    DBMS_OUTPUT.PUT_LINE('社員名=' || V_ENAME);
 11  END;
 12  PROCEDURE PROC_UPDATE_EMP
 13   (P_EMPNO IN NUMBER,
 14    P_ENAME IN VARCHAR2)
 15  IS
 16  BEGIN
 17    UPDATE SCOTT.EMP
 18    SET  ENAME = P_ENAME
 19    WHERE EMPNO = P_EMPNO;
 20  END;
 21  END PAC1;
 22  /

パッケージ本体が作成されました。

--  パッケージにまとめたので、もとの個々のプログラムは不要なので削除する
SQL> DROP PROCEDURE  PROC_SELECT_EMP;

プロシージャが削除されました。

SQL> DROP PROCEDURE  PROC_UPDATE_EMP;

プロシージャが削除されました。

-  ここまで  ----------------------------------------------

これで、この二つのプロシージャは、PAC1というパッケージにまとめられました。
ひとつにまとまっていますので、実行権限の付与もパッケージ全体で実行者ユーザに付与することになりとてもシンプルになります。

SQL> GRANT EXECUTE ON  SCOTT.PAC1 TO SELECT_USER, UPDATE_USER;

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

実際に実行するときに、権限をチェックし、権限不足であればエラーにしてくれます。
以下はその様子です。

SQL> CONNECT UPDATE_USER/UPDATE_USER
接続されました。
SQL> SET SERVEROUTPUT ON
SQL> EXEC SCOTT.PAC1.PROC_SELECT_EMP(7934)    --UPDATE_USERユーザはSELECTできる
社員名=MILLER

PL/SQLプロシージャが正常に完了しました。

SQL> EXEC SCOTT.PAC1.PROC_UPDATE_EMP(7934,'MILLER2')  -- UPDATEもできる

PL/SQLプロシージャが正常に完了しました。

SQL> COMMIT;

コミットが完了しました。

SQL> CONNECT SELECT_USER/SELECT_USER
接続されました。
SQL> SET SERVEROUTPUT ON
SQL> EXEC SCOTT.PAC1.PROC_SELECT_EMP(7934)   -- SELECT_USERユーザはSELECTできるが
社員名=MILLER2

PL/SQLプロシージャが正常に完了しました。

SQL> EXEC SCOTT.PAC1.PROC_UPDATE_EMP(7934,'MILLER3')   -- UPDATEはできない
BEGIN SCOTT.PAC1.PROC_UPDATE_EMP(7934,'MILLER3'); END;

*
行1でエラーが発生しました。:
ORA-01031: 権限が不足しています。
ORA-06512: "SCOTT.PAC1", 行19
ORA-06512: 行1

このようにパッケージを使えば、個々の実行者権限プロシージャの権限管理を個別に管理しなくてもよいので管理が簡単なわけです。

では、次にロールですが、こちらはPL/SQLプログラムの話ではなく、一般にデータベースでの権限管理を簡単にする話です。
概要だけを述べたいと思います。

ロールとは権限をひとつにまとめたものです。あらかじめユーザのタイプごとに作っておきます。
例えば、営業部社員に必要な権限と総務部社員に必要な権限は違いますので、それぞれ営業部ロール、総務部ロールというロールを作っておいて、そのロールに権限をまとめる意味で必要な権限を付与します。
そのロールをそれぞれ、営業部社員、総務部社員に付与することで権限管理が簡素化できます。

例えば上述の例で、UPDATE_USERと同じ権限をロールで管理するには次のようにします。

-- まず管理者ユーザで、ロールを作る(ロール名: UPDATE_ROLE)
SQL> CREATE ROLE UPDATE_ROLE;

-- 次にそのロールに必要な権限を与える
SQL> GRANT SELECT, UPDATE  ON SCOTT.EMP TO UPDATE_ROLE;
SQL> GRANT EXECUTE ON SCOTT.PAC1 TO UPDATE_ROLE;
SQL> GRANT CREATE SESSION TO UPDATE_ROLE;

-- 最後にそのロールを必要なユーザに与える
SQL> GRANT UPDATE_ROLE TO 新規ユーザ1, 新規ユーザ2, 新規ユーザ3,・・・;

これで多数のユーザにロールを通して同じ権限管理ができますので大変簡素化できます。

最後にまとめですが、実行者権限のプロシージャについては実行者の個々の権限管理が可能なので、きめ細かく管理すればセキュリティ向上に役立ちますが、管理が複雑になります。
そこで実行者権限のパッケージを作ってプロシージャをまとめたり、さらにロールを利用することで、きめ細かい権限管理を簡素化できます。

では、4回にわたって解説した定義者権限と実行者権限の話はここまでにします。
次回からまた違ったテーマを解説します。ご期待ください。