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に存在しないユーザーを指定した場合、オーファンオブジェクトが作成されます。
ストアドオブジェクトを呼び出す際、実行者は以下の権限を持っている必要があります：
- 呼び出し元には
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';