PL/SQLオブジェクトに対する権限

PL/SQLでの認可には、特定の権限が必要です。

• PL/SQL文およびPL/SQL操作の権限

• 無効化されたオブジェクト

• 定義者権限および実行者権限(AUTHID句)

ノート:

「SQLオブジェクトに対する権限」も参照してください。

PL/SQL文およびPL/SQL操作の権限

PL/SQL文およびPL/SQL操作に必要な権限があります。

• PL/SQL文およびPL/SQL操作の権限の概要

• PL/SQL文およびPL/SQL操作の権限参照

• PL/SQL文およびPL/SQL操作の権限の付与

PL/SQL文およびPL/SQL操作の権限の概要

PL/SQLユーザーの場合、権限の付与による認可は、ユーザーがパッケージおよびそのメンバー・プロシージャやファンクションを含むPL/SQLプロシージャおよびファンクションを作成、変更、破棄または実行できるようにするために必要です。

プロシージャ、ファンクション、パッケージ定義またはパッケージ本体をユーザー自身のスキーマに作成する場合は、CREATE PROCEDURE権限が必要であり、他のスキーマに作成する場合はCREATE ANY PROCEDUREが必要です。プロシージャ、ファンクション、パッケージ定義、またはパッケージ本体を変更または削除するには、その所有者であるか、ALTER ANY PROCEDURE権限またはDROP ANY PROCEDURE権限をそれぞれ持っている必要があります。

ユーザーが所有していないPL/SQLファンクション、PL/SQLプロシージャまたはPL/SQLパッケージを実行するには、プロシージャまたはファンクション、あるいはそれが属するパッケージに対するEXECUTEオブジェクト権限、またはEXECUTE ANY PROCEDUREが付与されている必要があります。ユーザーにパッケージに対するEXECUTE権限を付与すると、そのコンポーネント・プロシージャおよび関数に対するEXECUTE権限が自動的に付与されます。

EXECUTE権限によって、次のことが認可されます。

• プロシージャまたは関数を実行します。

• パッケージの仕様で宣言されている任意のプログラム・オブジェクトにアクセスします。

• 現時点で無効または未コンパイルの関数またはプロシージャのコール時にオブジェクトを暗黙的にコンパイルします。

ALTER PROCEDUREまたはALTER FUNCTIONを使用して明示的にコンパイルするには、ユーザーにALTER ANY PROCEDUREシステム権限が付与されている必要があります。

PL/SQL文およびPL/SQL操作の権限参照

PL/SQL文およびPL/SQL操作に必要な権限があります。

PL/SQL文および操作に必要な権限を表2-2にまとめます。

表2-2 PL/SQLのプロシージャおよびファンクションを使用するための権限

アクション	SQL文または操作	必要な権限
プロシージャ、ファンクション、パッケージ定義またはパッケージ本体を作成します。	CREATE [OR REPLACE] PROCEDURE CREATE [OR REPLACE] FUNCTION CREATE [OR REPLACE] PACKAGE CREATE [OR REPLACE] PACKAGE BODY	ユーザーのスキーマでのCREATE PROCEDURE または: 他のスキーマでのCREATE ANY PROCEDURE
プロシージャ、ファンクションまたはパッケージを変更します。	ALTER PROCEDURE ALTER FUNCTION ALTER PACKAGE	プロシージャ、ファンクションまたはパッケージの所有権 または: ALTER ANY PROCEDURE
プロシージャ、ファンクション、パッケージ定義またはパッケージ本体を削除します。	DROP PROCEDURE DROP FUNCTION DROP PACKAGE DROP PACKAGE BODY	プロシージャ、ファンクションまたはパッケージの所有権 または: DROP ANY PROCEDURE
プロシージャまたはファンクションを実行します。	プロシージャまたはファンクションを起動します。	プロシージャまたはファンクション、あるいはこれらが所属するパッケージ(該当する場合)の所有権 または: プロシージャまたはファンクション、あるいはこれらが属するパッケージ(該当する場合)に対するEXECUTE または: EXECUTE ANY PROCEDURE

PL/SQL文およびPL/SQL操作の権限の付与

プロシージャおよびuser1が所有するパッケージに対して、user2にEXECUTE権限を付与してから取り消すことができます。

Command> grant execute on user1.myproc to user2;
Command> grant execute on user1.mypkg to user2;
...
Command> revoke execute on user1.myproc from user2;
Command> revoke execute on user1.mypkg from user2;

ノート:

• プロシージャ(またはファンクション)を実行する権限を付与されたユーザーは、プロシージャによってコールされる他のプロシージャに対する権限がない場合でも、そのプロシージャを実行できます。たとえば、ストアド・プロシージャuser2.proc1によって実行されるプロシージャuser2.proc2の例を考えてみます。proc1を実行する権限を付与されているものの、proc2を実行する権限を付与されていないuser1は、直接proc2を実行できませんが、proc1は実行できます。

• プロシージャまたはファンクションの実行時に、これらが無効であるか、またはコンパイルされていなくても、プロシージャまたはファンクションを実行する権限によって暗黙的なコンパイルが可能になります。

• シノニムを介してプロシージャまたはファンクションを起動するには、基礎となるプロシージャまたはファンクションを実行する権限がユーザーに必要です。

• PL/SQLで実行されるSQL文には、直接実行する場合と同じ権限が必要です。

• EXECUTE ANY PROCEDUREはTimesTen提供パッケージには適用されませんが、そのほとんどはPUBLICロールを介してアクセス可能です。

次の例は、ユーザーuser1によって試行される次の一連の操作を示しています。

1. ユーザーが、必要な権限を付与されていないときに各操作を試行します。その結果、エラーが表示されます。

2. インスタンス管理者が必要な権限を付与します。

3. ユーザーは操作の実行に成功します。

user1はttIsqlユーティリティを使用して操作を実行(または試行)し、インスタンス管理者はこのユーティリティを使用して権限を付与します。

user1:

ユーザーは、最初はプロシージャを作成する権限を持っていません。ユーザー自身のスキーマであっても、付与される必要があります。

Command> create procedure testproc is
         begin
         dbms_output.put_line('user1.testproc called');
         end;
         /
15100: User USER1 lacks privilege CREATE PROCEDURE
The command failed.

インスタンス管理者:

Command> grant create procedure to user1;

user1:

user1がuser1スキーマでプロシージャを作成できるようになると、そのユーザーがプロシージャを所有し、実行できるようになります。

Command> create procedure testproc is
         begin
         dbms_output.put_line('user1.testproc called');
         end;
         /
 
Procedure created.
 
Command> begin
         testproc();
         end;
         /
user1.testproc called
 
PL/SQL procedure successfully completed.

ただし、ユーザーは別のスキーマではまだプロシージャを作成できません。

Command> create procedure user2.testproc is
         begin
         dbms_output.put_line('user2.testproc called');
         end;
         /
15100: User USER1 lacks privilege CREATE ANY PROCEDURE
The command failed.

user1:

user1は別のスキーマでプロシージャを作成できるようになりましたが、所有しない場合や必要な権限がない場合は実行できません。

インスタンス管理者:

Command> grant create any procedure to user1;

Command> create procedure user2.testproc is
         begin
         dbms_output.put_line('user2.testproc called');
         end;
         /
 
Procedure created.

Command> begin
         user2.testproc();
         end;
         /
 8503: ORA-06550: line 2, column 7:
PLS-00904: insufficient privilege to access object USER2.TESTPROC
 8503: ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
The command failed.

インスタンス管理者:

Command> grant execute any procedure to user1;

user1:

user1は、別のスキーマでプロシージャを実行できるようになりました。

Command> begin
         user2.testproc();
         end;
         /
user2.testproc called
 
PL/SQL procedure successfully completed.

無効化されたオブジェクト

オブジェクトに対するユーザーの権限が取り消されると、そのオブジェクトを参照する、そのユーザーのすべてのPL/SQLオブジェクトが一時的に無効化されます。 権限が元に戻されると、ユーザーはオブジェクトに対して適宜ALTER PROCEDURE、ALTER FUNCTIONまたはALTER PACKAGEを実行し、オブジェクトを明示的に再コンパイルおよび再検証できます。また、各オブジェクトは、次に実行されるときに自動的に再コンパイルおよび再検証されます。

たとえば、user1が、user2.proc1をコールするプロシージャuser1.proc0を所有している場合、user1のproc1に対するEXECUTE権限が取り消されると、proc0は無効になります。

次を使用して、無効になっているオブジェクトがないかを確認します。

select * from user_objects where status='INVALID';

この例では、無効化されたPL/SQLプロシージャを生成する一連のアクションを示します。

1. ユーザーがCREATE ANY PROCEDURE権限を付与され、別のユーザーのスキーマにプロシージャを作成してから、その別のユーザーのスキーマにあるプロシージャをコールするプロシージャをユーザー自身のスキーマに作成します。

2. ユーザーが別のユーザーのスキーマでプロシージャを実行するEXECUTE権限を付与されます。

3. ユーザーが別のユーザーのスキーマでプロシージャをコールするプロシージャを自身のスキーマで実行します。

4. ユーザーから、別のユーザーのスキーマのプロシージャに対するEXECUTE権限が取り消され、そのユーザー自身のプロシージャが無効化されます。

5. ユーザーに、別のユーザーのスキーマのプロシージャに対するEXECUTE権限が再度付与されます。ユーザーが自身のプロシージャを実行すると、プロシージャは暗黙的に再コンパイルおよび再検証されます。

管理ユーザー:

Command> grant create any procedure to user1;

user1:

Command> create procedure user2.proc1 is
         begin
         dbms_output.put_line('user2.proc1 is called');
         end;
         /
 
Procedure created.
 
Command> create procedure user1.proc0 is
         begin
         dbms_output.put_line('user1.proc0 is called');
         user2.proc1;
         end;
         /
 
Procedure created.

管理ユーザー:

Command> grant execute on user2.proc1 to user1;

user1:

Command> begin
         user1.proc0;
         end;
         /
user1.proc0 is called
user2.proc1 is called
 
PL/SQL procedure successfully completed.

user1が無効なオブジェクトを持っていないことを確認するには、次のように実行します。

Command> select * from user_objects where status='INVALID';
0 rows found.

管理ユーザー:

ここで、user1のEXECUTE権限を取り消します。

Command> revoke execute on user2.proc1 from user1;

user1:

user1にはuser2.proc1を実行する権限がなくなったため、即座にuser1.proc0は無効になります。

Command> select * from user_objects where status='INVALID';
< PROC0, <NULL>, 273, <NULL>, PROCEDURE, 2021-06-04 14:51:34, 2021-06-04 14:58:23,
2021-06-04:14:58:23, INVALID, N, N, N, 1, <NULL> >
1 row found.

このため、user1はプロシージャを実行できなくなりました。

Command> begin
         user1.proc0;
         end;
         /
 8503: ORA-06550: line 2, column 7:
PLS-00905: object USER1.PROC0 is invalid
 8503: ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
The command failed.

管理ユーザー:

再度user2.proc1へのEXECUTE権限をuser1に付与します。

Command> grant execute on user2.proc1 to user1;

user1:

プロシージャuser1.proc0は、明示的または暗黙的に再コンパイルされるまで無効のままです。次に示すとおり、実行時に暗黙的に再コンパイルされます。または、ALTER PROCEDUREを使用して明示的に再コンパイルすることができます。

Command> select * from user_objects where status='INVALID';
< PROC0, <NULL>, 273, <NULL>, PROCEDURE, 2021-06-04 14:51:34, 2021-06-04 16:13:00,
2021-06-04:16:13:00, INVALID, N, N, N, 1, <NULL> >
1 row found.
Command> begin
         user1.proc0;
         end;
         /
user1.proc0 is called
user2.proc1 is called
 
PL/SQL procedure successfully completed.
 
Command> select * from user_objects where status='INVALID';
0 rows found.

定義者権限および実行者権限(AUTHID句)

PL/SQLのプロシージャまたはファンクションを定義するときに、CREATE FUNCTION文またはCREATE PROCEDURE文のオプションのAUTHID句で、プロシージャまたはファンクションを定義者権限(AUTHID DEFINER、デフォルト)で実行するか、実行者権限(AUTHID CURRENT_USER)で実行するかを指定します。

AUTHIDの設定は、実行時にプロシージャまたはファンクションによって発行されるSQL文の名前解決および権限チェックに影響を与えます。定義者権限の場合、SQLの名前解決および権限チェックは、プロシージャまたはファンクションの所有者(定義者、つまりプロシージャまたはファンクションがあるスキーマの所有者)が実行しているものとして実行されます。実行者権限の場合、SQLの名前解決および権限チェックは、現在のユーザー(実行者)が実行しているものとして実行されます。

パッケージ内のプロシージャまたはファンクションでは、CREATE PACKAGE文のAUTHID句で、パッケージのメンバーのファンクションまたはプロシージャそれぞれを定義者権限で実行するか、実行者権限で実行するかを指定します。AUTHID句の詳細は、『Oracle TimesTen In-Memory Database SQLリファレンス』のSQL文で、これらの文の構文に関する記述を参照してください。

実行者権限は、コード本体に対しては広範囲な権限を付与する一方で、そのコードの影響範囲をユーザーのスキーマ内の自分のオブジェクトに限定する場合に役立ちます。

定義者権限は、すべてのユーザーが1つの集中管理された表またはその他のSQLオブジェクトにアクセスできるようにするものの、プロシージャによって実行される操作を特定の操作に制限する場合に役立ちます。それ以外の場合、ユーザーはSQLオブジェクトにはアクセスできません。

『Oracle Database PL/SQL言語リファレンス』の実行者権限および定義者権限(AUTHIDプロパティ)を参照してください。

次の例では、変更を1つだけ加えてスクリプトをttIsqlで2回実行します(最初は実行者権限のAUTHID CURRENT_USERでPL/SQLプロシージャを定義し、次に定義者権限のAUTHID DEFINERで定義します)。

AUTHIDのスクリプトの例:

スクリプトでは、1人のツール・ベンダーと2人のツール・ユーザー(brandXおよびbrandY)の、3人のユーザーが作成されていることを想定しています。各ユーザーには、必要に応じて、CREATE SESSION権限、CREATE PROCEDURE権限およびCREATE TABLE権限が付与されています。また、use username;構文を使用してusernameとしてデータベースに接続できるよう、次の設定が想定されています。

connect adding "uid=toolVendor;pwd=pw" as toolVendor;
connect adding "uid=brandX;pwd=pw" as brandX;
connect adding "uid=brandY;pwd=pw" as brandY;

スクリプトでは、次の操作を実行します。

• ツール・ベンダーとして、プロシージャprintInventoryStatisticsを作成します。

• 3つのユーザー・スキーマのそれぞれに同じmyInventoryという名前の表を作成し、それぞれの表に一意のデータを移入します。

• それぞれのツール・ユーザーとして、プロシージャを実行します。

2回のスクリプト実行の結果の違いは、実行者権限と定義者権限の違いを示しています。

次に、実行者権限で実行する場合のスクリプトを示します。

use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);

create or replace procedure printInventoryStatistics authid current_user is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
set serveroutput on
execute toolVendor.printInventoryStatistics;
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
set serveroutput on
execute toolVendor.printInventoryStatistics; 

定義者権限スクリプトの違いは、プロシージャ定義のAUTHID句を変更することのみです。

...
create or replace procedure printInventoryStatistics authid definer is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
...

AUTHID CURRENT_USERの使用:

ここでは、実行者権限を使用してプロシージャを定義した場合の結果について説明しています。ツール・ユーザーbrandXおよびbrandYがprintInventoryStatisticsプロシージャを実行すると、実行者としてそれぞれにmyInventory表のデータが表示されることに注意してください。

Command> run invoker.sql
 
use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
1 row inserted.
 
create or replace procedure printInventoryStatistics authid current_user is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/

Procedure created.
 
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
toothpaste 100
 
PL/SQL procedure successfully completed.
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
shampoo 10
 
PL/SQL procedure successfully completed.

次のコマンドを使用して、すべての接続を終了します。

Command> disconnect all;

AUTHID DEFINERの使用:

ここでは、定義者権限を使用してプロシージャを定義した場合の結果について説明しています。ツール・ユーザーbrandXおよびbrandYがprintInventoryStatisticsを実行すると、ツール・ベンダー(定義者)に属するmyInventoryのデータが表示されることに注意してください。

Command> run definer.sql
 
use toolVendor;

create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
1 row inserted.
 
create or replace procedure printInventoryStatistics authid definer is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
 
Procedure created.
 
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
butter 1
 
PL/SQL procedure successfully completed.
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
butter 1
 
PL/SQL procedure successfully completed.

この場合、brandXおよびbrandYはそれぞれプロシージャを介してtoolVendor.myInventory表にアクセスできますが、直接にはアクセスできないことを理解しておくことも有用です。定義者権限を主にこのように使用することによって、プロシージャのアクションを介して表または他のSQLオブジェクトに明確に制限付きでアクセスできるようになります。

Command> use brandX;
brandx: Command> select * from toolVendor.myInventory;
15100: User BRANDX lacks privilege SELECT on TOOLVENDOR.MYINVENTORY
The command failed.

brandx: Command> use brandY;
brandy: Command> select * from toolVendor.myInventory;
15100: User BRANDY lacks privilege SELECT on TOOLVENDOR.MYINVENTORY
The command failed.

終了したら、すべての接続を終了します。

Command> disconnect all;