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 |
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
SECURITY TYPE に INVOKER が指定されている場合、実行時には呼び出し元の権限で実行され、このとき DEFINER は無効になります。
*トリガーとイベントには SQL SECURITY がなく、常に DEFINER コンテキストで実行されます。
例
以下の例では、ストアドプロシージャを作成する方法を示します。この例では、ストアドプロシージャの名前を GetHighSalaryEmployees とし、入力パラメータ minSalary を受け取り、指定された値よりも給与が高い従業員情報を返します。
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
上記の呼び出しは、給与が55000より高い従業員情報を返します。
上記の例では、CREATE ROUTINE 権限を使用してストアドプロシージャを作成します。他のユーザーにこのストアドプロシージャを実行する権限を付与するには、GRANT ステートメントを使用できます。
GRANT EXECUTE ON PROCEDURE GetHighSalaryEmployees TO 'your_user'@'your_host';