---
title: "存储过程与权限管理 - OceanBase 数据库 V4.2.3 | OceanBase 文档中心"
description: 存储过程与权限管理 在 OceanBase 数据库中，PL 权限用于管理用户对存储过程和存储函数的操作。存储过程是数据库中的一种例程，可以通过 CREATE PROCEDURE 和 CREATE FUNCTION 语句创建，并可在创建时指定其所属的数据库。执行存储过程时使用 CALL 语句，而存储函数则在表达式中直接…
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.2.3

# 存储过程与权限管理

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

[编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.2.3/zh-CN/700.reference/500.sql-reference/300.pl-reference/200.pl-mysql/200.storage-object-mysql/350.pl-stored-routines-privileges-mysql.md)  

在 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` 权限。

示例如下：

```shell
mysql> GRANT CREATE ROUTINE ON my.* TO mingye;
mysql> CREATE PROCEDURE p1()
    -> BEGIN
    -> SELECT 1 FROM dual;
    -> END;
    -> /

```

返回结果如下：

```shell
+---------------------------------------------------------------------+
| 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` 权限。如果你不需要这个权限，你可以手动删除这个权限。

示例如下：

```shell
mysql> SELECT @@automatic_sp_privileges;
+---------------------------+
| @@automatic_sp_privileges |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.03 sec)

```

查询到 `automatic_sp_privileges` 系统为 1，现在可以手动删除这个自动创建的权限。

```shell
mysql> REVOKE EXECUTE ON PROCEDURE my.p1 FROM mingye;
mysql> CALL p1();/

```

`automatic_sp_privileges` 是全局变量，需要 SUPER 权限才能修改。示例如下：

```sql
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` 的用户。

```shell
mysql> CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
       BEGIN
        UPDATE t1 SET counter = counter + 1;
       END;

mysql> SHOW PROCEDURE STATUS LIKE '%p2%';

```

返回结果如下所示：

```shell
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| 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`。

```sql
mysql> CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2()
     SQL SECURITY INVOKER
     BEGIN
       UPDATE t1 SET counter = counter + 1;
     END;
     /
mysql> SHOW PROCEDURE STATUS LIKE '%p2%';

```

返回结果如下所示：

```shell
+------+------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| 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`，并返回薪资高于指定值的员工信息。

```sql
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 ;

```

现在调用存储过程：

```sql
obclient > CALL GetHighSalaryEmployees(55000);

```

返回结果如下所示：

```shell
+------------+--------+
| name       | salary |
+------------+--------+
| Jane Smith |  60000 |
+------------+--------+
1 row in set

```

上述调用会返回薪资高于 55000 的员工信息。

在上述示例中，使用 `CREATE ROUTINE` 权限创建存储过程。为了授予其他用户执行这个存储过程的权限，可以使用 `GRANT` 语句：

```sql
GRANT EXECUTE ON PROCEDURE GetHighSalaryEmployees TO 'your_user'@'your_host';

```

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线