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

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

第53回 「定義者権限と実行者権限(その2)」(2013.04.08)

2013.04.08

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

前回は定義者権限(デフォルトの状態)について解説しました。
定義者権限のプロシージャは、定義者の権限とスキーマで実行されます。
前回、ALLENさんが、SCOTTさんのプロシージャ(PROC1)を実行した際に、そのプロシージャのなかで参照されているEMP表は、実行者であるALLENさんのEMP表ではなく、定義者であるSCOTTさんのEMP表であったわけですね。
(SCOTT.EMPでは、社員名が大文字の「MILLER」、ALLEN.EMPでは小文字の「miller」となっているのでそれによりどちらのEMP表であるかを判断しています)

そして、実行者であるALLENさんのEMP表を参照するようにしたければ、そのプロシージャを「実行者権限」のプロシージャにすればよい、というのが前回までの話でした。

今回は早速、そのやり方(実行者権限にする方法)です。

まず、PROC1プロシージャの定義者は前回と同じく、SCOTTですから、SCOTTでプロシージャを再定義します。
ポイントは、「実行者権限」の指定をするわけです。以下の例をご覧ください。

-- ここから -------------------------------------------------------------
SQL> show user
ユーザーは"SCOTT"です。
SQL> CREATE OR REPLACE PROCEDURE PROC1
  2  AUTHID CURRENT_USER         -- ←実行者権限の指定
  3  IS
  4     V_ENAME    VARCHAR2(20);
  5  BEGIN
  6    SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = 7934;
  7    DBMS_OUTPUT.PUT_LINE(V_ENAME);
  8  END;
  9  /

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

上記の例で、2行目「AUTHID CURRENT_USER」が実行者権限の指定です。

では、このプロシージャを早速、ALLENで実行してみましょう。
-- ここから -------------------------------------------------------------
SQL> connect ALLEN/allen     -- ALLENユーザで 接続されました。
SQL> set serveroutput on     -- 画面表示可能として
SQL> EXEC  SCOTT.PROC1       -- SCOTT.PROC1プロシージャを実行
miller   -- 前回と違って小文字の「miller」、つまりALLENのEMP表をSELECTしたことがわかる

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

ご覧のように、今度は期待どおり、実行者であるALLENのEMP表をSELECTできました。
これが「実行者権限」のプロシージャということです。

ここまでのところでお判りと思いますが、「実行者権限」のプロシージャでメリットが大きいケースの一つの例は、「異なるスキーマに同じ構造の同じ名前の表」があり、その表に対して同じプログラム処理をしたいときです。

もしも定義者権限のプロシージャであれば、たとえ同じ処理であっても、それぞれのスキーマでプロシージャを定義する必要があります。
しかし、実行者権限のプロシージャであれば、一つのスキーマのみで定義すればよく、その実行権限を必要なユーザに与えるのみです。
したがって同じ内容のプロシージャを各スキーマでもつ必要はなくなり、管理も簡素化できます。

どうですか?簡単ですね。

しかし、1点、細かいですが注意点があります。
それは実行者権限のプロシージャ内でサブプログラム(プロシージャ、ファンクションなど)をコールしている場合は、そのサブプログラムについては例外的に定義者のスキーマとなることです。
つまり、表については実行者のスキーマですが、サブプログラムをコールしている場合は、そのサブプログラムは定義者のスキーマとなります。

例えば、PROC2というプロシージャをSCOTTユーザ、ALLENユーザでそれぞれ作成したとします。
SCOTTのPROC2プロシージャはSCOTTのメッセージを表示する、ALLENのPROC2プロシージャは、ALLENのメッセージを表示するとします。
ここで、実行者権限であるSCOTT.PROC1プロシージャ内で、 「PROC2」プロシージャをコールしているものとします。
そのSCOTT.PROC1プロシージャをALLENがコールした場合、そこで呼び出されるPROC2プロシージャはALLENのPROC2プロシージャか、それともSCOTTのプロシージャか? 答えは、SCOTTのPROC2プロシージャです。ですから、SCOTTのメッセージが表示されるはずです。

では早速やってみます。

まず、SCOTTユーザで上述のPROC2プロシージャを作成します。
-- ここから -------------------------------------------------------------
SQL> CONNECT SCOTT/tiger
接続されました。
SQL> CREATE OR REPLACE  PROCEDURE PROC2
  2  IS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('私はSCOTTスキーマのPROC2プロシージャです');
  5  END;
  6  /
-- ここまで -------------------------------------------------------------

次にALLENユーザでもPROC2プロシージャを作成します。
-- ここから -------------------------------------------------------------
SQL> CONNECT Allen/allen
接続されました。
SQL> CREATE OR REPLACE  PROCEDURE PROC2
  2  IS
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE('私はALLENスキーマのPROC2プロシージャです');
  5  END;
  6  /

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

そして、SCOTTのPROC1プロシージャにPROC2プロシージャをコールする記述を追加します。
-- ここから -------------------------------------------------------------
SQL> CONNECT SCOTT/tiger
接続されました。
SQL> CREATE OR REPLACE PROCEDURE PROC1
  2  AUTHID CURRENT_USER         -- ←実行者権限の指定
  3  IS
  4     V_ENAME    VARCHAR2(20);
  5  BEGIN
  6    SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = 7934;
  7    DBMS_OUTPUT.PUT_LINE(V_ENAME);
  8    PROC2;     -- ← サブプログラムコール追加
  9  END;
 10  /

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

最後にALLENが、SCOTT.PROC1プロシージャを実行します。結果はどうでしょう?
-- ここから -------------------------------------------------------------
SQL> CONNECT ALLEN/allen
接続されました。
SQL> set serveroutput on
SQL> CONNECT ALLEN/allen
接続されました。
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE SCOTT.PROC1
miller                       -- 小文字の「miller」なので、EMP表はALLENのスキーマ
私はSCOTTスキーマのPROC2プロシージャです   --  PROC2プロシージャはSCOTTのスキーマである

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

いかがですか?
実行者権限のプロシージャは、特にスキーマを明示しない限りは、表については実行者のスキーマであり、サブプログラムについては、定義者のスキーマとなります。

なぜ、サブプログラムだけ、例外的に定義者のスキーマなのでしょうか?
それは、実行者権限のプロシージャとは、異なるスキーマの同じ名前の表に『同じ処理』をしたいために存在する機能だからです。
同じ処理をするためには、処理の部分は共有できなければいけません。したがって、サブプログラムについてはたとえ実行者が同じ名前のサブプログラムを持っていても、それらはコールせず、定義者のサブプログラムをコールするわけです。

これにより、異なるスキーマの同じ構造、同じ名前の表に対して、『同じ処理』が保証できるわけです。

今回は、実行者権限プロシージャの「実行者のスキーマ」で実行されるという側面に焦点をあてて説明しましたが、もう一つ、「実行者の権限」で実行されるという側面に注目するとまた違った活用方法があります。それは次回にご紹介したいと思います。ご期待ください。

先頭へ戻る