OceanBaseデータベースでは、PL権限はユーザーのストアドプロシージャおよびストアド関数に対する操作を管理するために使用されます。ストアドプロシージャはデータベース内のルーチンであり、CREATE PROCEDURE および CREATE FUNCTION ステートメントを使用して作成でき、作成時に所属するデータベースを指定できます。ストアドプロシージャの実行には CALL ステートメントを使用し、ストアド関数は式の中で直接参照され、式の評価時に値を返します。
ストアドプロシージャの作成と権限
OceanBaseの権限体系とストアドプロシージャの関係は以下のとおりです：
権限 |
説明 |
|---|---|
| CREATE ROUTINE | PROCEDUREとFUNCTIONを作成する権限。 |
| EXECUTE | PROCEDUREとFUNCTIONを実行する権限。 |
| ALTER ROUTINE | PROCEDUREとFUNCTIONを変更および削除する権限。 |
ストアドプロシージャを作成するには、
CREATE ROUTINE権限が必要です。CREATE ROUTINE権限を付与されると、ユーザーはPROCEDUREを作成できます。PROCEDUREの作成後、システムは作成されたPROCEDUREに対応するEXECUTEとALTER ROUTINE権限を自動的に追加します。
例：
mysql> GRANT CREATE ROUTINE ON my.* TO mingye;
mysql> CREATE PROCEDURE p1()
-> BEGIN
-> SELECT 1 FROM dual;
-> END;
-> /
戻り結果は次のとおりです：
+---------------------------------------------------------------------+
| Grants for mingye@% |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mingye`@`%` |
| GRANT CREATE ROUTINE ON `my`.* TO `mingye`@`%` |
| GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `my`.`p1` TO `mingye`@`%` |
+---------------------------------------------------------------------+
automatic_sp_privileges システム変数
automatic_sp_privileges システム変数のデフォルト値が1の場合、システムは自動的に EXECUTE と ALTER ROUTINE 権限を作成します。この権限が不要な場合は、手動で削除できます。
例：
mysql> SELECT @@automatic_sp_privileges;
+---------------------------+
| @@automatic_sp_privileges |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.03 sec)
automatic_sp_privileges システム変数の値が1であることを確認したので、この自動作成された権限を手動で削除できます。
mysql> REVOKE EXECUTE ON PROCEDURE my.p1 FROM mingye;
mysql> CALL p1();/
automatic_sp_privileges はグローバル変数であり、変更するには SUPER 権限が必要です。例：
mysql> SET GLOBAL automatic_sp_privileges = 0;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
DEFINERとSECURITY TYPE
PROCEDURE を作成する際に DEFINER を指定できます。定義時に DEFINER を省略した場合、DEFINER はデフォルトでその PROCEDURE を作成したユーザーに設定されます。
mysql> CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
BEGIN
UPDATE t1 SET counter = counter + 1;
END;
mysql> SHOW PROCEDURE STATUS LIKE '%p2%';
戻り値は以下のようになります：
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer |x| Security_type |
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | p2 | PROCEDURE | admin@localhost |x| DEFINER |
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
DEFINERを指定するユーザー：
SUPER権限を持つユーザーはDEFINERに任意の値を指定できます。- それ以外の場合、
DEFINERは現在のユーザー、CURRENT_USER、CURRENT_USER()、または直接テキストを記述することしかできません。
DEFINER に存在しないユーザーを指定した場合、オーファンオブジェクト（orphan object）が作成されます。
ストアドオブジェクトを呼び出す際、実行者は以下の権限を持っている必要があります：
- 呼び出し元に
EXECUTE権限が必要です。 DEFINERにもEXECUTE権限が必要です。
ストアドオブジェクトの実行中は、DEFINER で指定されたユーザーの権限に基づいて実行されます。
ストアドオブジェクトをオーファンオブジェクトにする
ストアドオブジェクトがオーファンオブジェクトになるとは、ユーザーを削除しようとした際に、そのユーザーが何らかのストアドオブジェクトの DEFINER として定義されている場合、依存関係が存在するため操作が失敗することを指します。このような状況を避けるため、システムはユーザーを正常に削除できるように、事前にこれらのストアドオブジェクトを削除または変更するよう求める場合があります。
- DROP USER の際、ユーザーが任意のストアドオブジェクトの
DEFINERである場合、実行は失敗しエラーが発生します。 - RENAME USER の際、ユーザーが任意のストアドオブジェクトの
DEFINERである場合、実行は失敗しエラーが発生します。
データベースシステムが対応するストアドオブジェクトの削除や変更なしにユーザーの削除を許可する場合、ユーザー削除後、関連するストアドオブジェクトは DEFINER のないオーファンオブジェクトになります。
オーファンオブジェクトをユーザーが引き取る
オーファンストアドオブジェクト、つまり現在有効な DEFINER を持たないオブジェクトが存在する場合、オーファンオブジェクトの元の DEFINER 名前と同じ新しいユーザーを作成しても、これらのオブジェクトは自動的に引き取られません。オーファンオブジェクトは、明示的に変更されるまで元の状態を維持します。
CREATE USER の際、ユーザーが任意のストアドオブジェクトの DEFINER である場合、実行は失敗しエラーが発生します。
SECURITY TYPE
ストレージオブジェクトを作成する際に SQL SECURITY を指定できます。明示的に指定しない場合、デフォルト値は DEFINER です。設定可能な値は DEFINER と INVOKER の2種類のみです。
mysql> CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2()
SQL SECURITY INVOKER
BEGIN
UPDATE t1 SET counter = counter + 1;
END;
/
mysql> SHOW PROCEDURE STATUS LIKE '%p2%';
実行結果は次のとおりです：
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer |x| Security_type |
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test | p2 | PROCEDURE | admin@localhost |x| INVOKER |
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
INVOKER を SECURITY TYPE として指定した場合、実行時には呼び出し元の権限で実行されます。このとき、DEFINER は無効になります。
*トリガーやイベントには SQL SECURITY がなく、常に DEFINER コンテキストで実行されます。
例
以下の例では、ストアドプロシージャの作成方法を示します。この例では、ストアドプロシージャの名前を GetHighSalaryEmployees とし、入力パラメータ minSalary を1つ受け取り、指定された値よりも給与が高い従業員情報を返します。
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
salary INT);
INSERT INTO employees (id, name, salary) VALUES
(1, 'John Doe', 50000),
(2, 'Jane Smith', 60000),
(3, 'Bob Johnson', 45000);
DELIMITER //
CREATE PROCEDURE GetHighSalaryEmployees(IN minSalary INT)
BEGIN
SELECT name, salary
FROM employees
WHERE salary > minSalary;
END //
DELIMITER ;
次に、ストアドプロシージャを呼び出します：
obclient > CALL GetHighSalaryEmployees(55000);
実行結果は以下のとおりです：
+------------+--------+
| name | salary |
+------------+--------+
| Jane Smith | 60000 |
+------------+--------+
1 row in set
上記の呼び出しは、給与が55,000より高い従業員情報を返します。
上記の例では、CREATE ROUTINE 権限を使用してストアドプロシージャを作成しました。他のユーザーにこのストアドプロシージャを実行する権限を付与するには、GRANT ステートメントを使用できます：
GRANT EXECUTE ON PROCEDURE GetHighSalaryEmployees TO 'your_user'@'your_host';