MySQL 存储对象访问控制

摘要：在本教程中，您将了解 MySQL 中的存储对象访问控制。

在 MySQL 中，存储例程（存储过程和函数）、触发器、事件和视图在确定其权限的安全上下文中执行。

MySQL 使用 DEFINER 和 SQL SECURITY 特性来控制这些权限。

该 `DEFINER` 属性

当您定义存储过程或函数等存储例程时，您可以选择指定 DEFINER 属性，即 MySQL 帐户的名称：

CREATE [DEFINER=user] PROCEDURE spName(parameter_list)
...

CREATE [DEFINER=user] FUNCTION sfName()
...

如果跳过该 DEFINER 属性，则默认为当前用户帐户。

DEFINER 如果您拥有 SUPER 或 SET_USER_ID 权限，则可以在属性中指定任何帐户。如果指定的用户帐户不存在，MySQL 将发出警告。

从 MySQL 8.0.16 开始，您必须具有 SYSTEM_USER 特权才能将 DEFINER 存储对象的属性设置为具有 SYSTEM_USER 特权的用户帐户。

该 `SQL SECURITY` 特征

存储例程（存储过程和函数）和视图可以包含 SQL SECURITY 一个值为 DEFINER or的子句 INVOKER ：

CREATE [DEFINER=user] PROCEDURE spName(parameter_list)
SQL SECURITY [DEFINER | INVOKER]
...

CREATE [DEFINER=user] FUNCTION sfName(parameter_list)
SQL SECURITY [DEFINER | INVOKER]
...

`SQL SECURITY DEFINER`

当您将 SQL SECURITY DEFINER 用于存储对象时，它将在定义者安全上下文中以 DEFINER 属性指定的用户权限执行。

请注意，调用存储对象（或调用者）的用户可能没有与定义者相同的权限。

如果调用者的权限最小而定义者的权限最大，则调用者可以在存储的对象中执行高于其权限的操作。

`SQL SECURITY INVOKER`

如果您将 SQL SECURITY INVOKER 用于存储的例程或视图，它将在调用者的权限内操作。

该 DEFINER 属性在对象执行期间无效。

存储对象访问控制实例

首先，创建一个名为的新数据库 testdb ：

CREATE DATABASE testdb;

其次，选择要使用的数据库 testdb ：

USE testdb;

第三，创建一个名为 messages 的新表：

CREATE TABLE messages (
    id INT AUTO_INCREMENT,
    message VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

第四，创建一个向表中插入新行的存储过程 messages ：

DELIMITER $$

CREATE DEFINER = root@localhost PROCEDURE InsertMessage( 
    msg VARCHAR(100)
)
SQL SECURITY DEFINER
BEGIN
    INSERT INTO messages(message)
    VALUES(msg);
END$$

DELIMITER ;

在这个存储过程中，定义者是 root@localhost 拥有所有权限的超级用户。

SQL 安全性设置为定义器。这意味着调用此存储过程的任何用户帐户都将使用定义者的所有权限（即 root@localhost 用户帐户）执行。

第五，创建一个名为的新用户 dev@localhost ：

CREATE USER dev@localhost 
IDENTIFIED BY 'Abcd1234';

六、授予 EXECUTE 权限 dev@localhost ，使其可以执行 testdb 数据库中的任何存储过程：

GRANT EXECUTE ON testdb.* 
TO dev@localhost;

七、使用 dev@localhost 登录MySQL Server：

mysql -u dev@localhost -p

八、使用 SHOW DATABASES 显示 dev@localhost 可以访问的数据库：

mysql> show databases;

这是清单：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| testdb             |
+--------------------+
2 rows in set (0.00 sec)

九、选择 testdb 数据库：

mysql> use testdb;

第十，调用 InsertMessage 过程向 messages 表中插入一行：

mysql> call InsertMessage('Hello World');

这是输出：

Query OK, 1 row affected (0.01 sec)

即使 dev@localhost 对 messages 表没有任何权限，它也可以通过存储过程成功地将新行插入到该表中，因为存储过程是在 root@localhost 用户帐户的安全上下文中执行的。

十一、进入root的会话，创建一个更新 messages 表的存储过程：

DELIMITER $$

CREATE DEFINER=root@localhost 
PROCEDURE UpdateMessage( 
    msgId INT,
    msg VARCHAR(100)
)
SQL SECURITY INVOKER
BEGIN
    UPDATE messages
    SET message = msg
    WHERE id = msgId;
END$$

DELIMITER ;

该 UpdateMessage 具有的安全上下文 INVOKER 谁就会调用此存储过程。

第十二，进入 dev@localhost 的会话，调用 UpdateMessage() 存储过程：

mysql> call UpdateMessage(1,'Good Bye');

这次 UpdateMessage() 存储过程以调用者的权限执行，即 dev@localhost .

由于 dev@localhost 对 messages 表没有任何权限，MySQL 报错并拒绝更新：

ERROR 1142 (42000): UPDATE command denied to user 'dev'@'localhost' for table 'messages'

在本教程中，您了解了 MySQL 存储对象访问控制。

该 DEFINER 属性

该 SQL SECURITY 特征

SQL SECURITY DEFINER

SQL SECURITY INVOKER

存储对象访问控制实例

本页目录

该 `DEFINER` 属性

该 `SQL SECURITY` 特征

`SQL SECURITY DEFINER`

`SQL SECURITY INVOKER`