存储过程与权限管理 - OceanBase 数据库 V4.2.3

存储过程与权限管理

更新时间：2024-04-24 14:53:02

在 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。

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 将无效。

*Triggers 和 events 没有 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';