e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第52回 「定義者権限と実行者権限(その1)」

2013.03.25

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

今日は、定義者権限と実行者権限についてご紹介します。
PL/SQLのプロシージャ、ファンクション、パッケージには、「定義者権限」または「実行者権限」という区別があります。デフォルトは「定義者権限」です。

「定義者権限」(デフォルト)のプロシージャは、誰がそのプロシージャを実行するかに関係なく(つまり実行者に関係なく)、「定義者の権限とスキーマ」で実行されます。例えば、SCOTTデータベースユーザがEMP表およびEMP表をSELECTするPROC1プロシージャを所有しているとします。そして以下のようなプロシージャだとします。このプロシージャは特別な設定は何もしていないので、「定義者権限」です。

--  ここから  ----------------------------------------------
SQL> CREATE OR REPLACE PROCEDURE PROC1
  2  IS
  3     V_ENAME    VARCHAR2(20);
  4  BEGIN
  5    SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = 7934;
  6    DBMS_OUTPUT.PUT_LINE(V_ENAME);
  7  END;
  8  /

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

このプロシージャは、社員番号(EMPNO)が7934の社員の名前(ENAME)を表示するものです。
さっそく、実行してみましょう。

--  ここから  ----------------------------------------------
SQL> SET SERVEROUTPUT ON      -- SQL*Plus(SQL*Developer)の画面出力を有効
SQL> SHOW USER                -- 現在のユーザは SCOTTユーザ
ユーザーは"SCOTT"です。
SQL> EXEC PROC1               -- PROC1プロシージャを実行
MILLER

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

上記の例でSCOTTユーザのPROC1プロシージャの結果からわかるように、SCOTTユーザのEMP表では、社員番号7934の社員名は、大文字の「MILLER」です。

では、ここで、例えば「ALLEN」というデータベースユーザを用意して、そのALLENユーザもSCOTTユーザと同じEMP表を持つと思ってください。ただし、ALLENユーザのEMP表においては、7934の社員の名前は、小文字の「miller」にします。
実際に行ってみます。

--  ここから  ----------------------------------------------
SQL> SHOW USER
ユーザーは"ALLEN"です。       -- 現在のユーザは ALLENユーザ
                              -- SCOTTのEMP表をもとに同じ名前の表を作成する
SQL> CREATE  TABLE  EMP AS SELECT * FROM SCOTT.EMP;

表が作成されました。
                              -- 社員番号7934の社員名を小文字の「miller」とする
SQL> UPDATE EMP SET ENAME = 'miller' WHERE EMPNO = 7934;

1行が更新されました。

SQL> COMMIT;                  -- 確定する

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

SQL> SELECT ENAME FROM EMP WHERE EMPNO = 7934; 

ENAME
----------
miller                        -- 確かに、社員7934は、小文字の「miller」である

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

では、ここで、ALLENユーザにSCOTTユーザのPROC1プロシージャの実行権限を与えてALLENユーザでそのプロシージャを実行してみましょう。
結果はどうなるでしょうか?ALLENのEMP表を問い合わせて、小文字の「miller」が表示されるでしょうか?結論をいうとそうはならないのです。ALLENが実行したとしても、このプロシージャは、SCOTTのEMP表を問い合わせて、大文字の「MILLER」と表示します。
実際にやってみましょう。

--  ここから  ----------------------------------------------
SQL> CONNECT SCOTT/tiger
接続されました。
SQL> SHOW USER
ユーザーは"SCOTT"です。       -- 現在は SCOTTユーザ
              -- ALLENユーザにSCOTTのPROC1プロシージャに対する実行権限を与える
SQL> GRANT EXECUTE ON  SCOTT.PROC1  TO  ALLEN; 

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

SQL> CONNECT ALLEN/allen
接続されました。
SQL> SHOW USER
ユーザーは"ALLEN"です。      -- ALLENユーザになり
SQL> SET SERVEROUTPUT ON
SQL> SELECT ENAME FROM EMP WHERE EMPNO = 7934;

ENAME
----------
miller                     -- ALLENのEMP表では社員7934の社員名は小文字の「miller」

SQL> SET SERVEROUTPUT ON
SQL> EXEC SCOTT.PROC1
MILLER                     -- しかし、プロシージャの結果は、大文字の「MILLER」
                 -- つまりプロシージャはALLENではなくSCOTTのEMP表をSELECTした

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

ご覧のように、ALLENが実行しても、このプロシージャは定義者であるSCOTTのEMP表を問い合わせていることがわかります。これが、「定義者権限」のプロシージャということです。

つまり、このPROC1プロシージャの中で記述されている「EMP」は誰がこのプロシージャを実行しても、「定義者であるSCOTTのEMP」表ということになります。つまり「定義者のスキーマ」で実行されているわです。

そうすると、ALLENは、このプロシージャを通して、SCOTTのEMP表をSELECT するわけですが、ALLENには SCOTT.EMPに対するSELECT権限が与えられていません。
しかし問題ではないのです。実行者はそのプロシージャを実行できる権限さえあればOKであり、その中で行われている個々の操作は定義者の権限でおこなっているので、実行者にはそれらの権限は必要ないのです。
冒頭で、「定義者権限」のサブプログラムは誰が実行しても「定義者の権限とスキーマ」で実行されると申し上げたのは、こういうことです。

では、ALLENがSCOTT.PROC1プロシージャを実行することで、SCOTTではなくALLENのEMP表を問い合わせて小文字の「miller」が表示されるようにするにはどうすればいいのか?

答えは、SCOTT.PROC1プロシージャを修正して、「実行者権限」に変更すればいいのです。

では、その方法は次回説明いたします。ご期待ください。

先頭へ戻る