MySQL 存储对象访问控制

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

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

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

`DEFINER`属性

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

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

CREATE [DEFINER=user] FUNCTION sfName()
...
Code language: SQL (Structured Query Language) (sql)

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

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

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

`SQL SECURITY`特性

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

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

CREATE [DEFINER=user] FUNCTION sfName(parameter_list)
SQL SECURITY [DEFINER | INVOKER]
...
Code language: SQL (Structured Query Language) (sql)

`SQL SECURITY DEFINER`

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

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

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

`SQL SECURITY INVOKER`

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

DEFINER属性在对象执行期间不起作用。

存储对象访问控制示例

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

CREATE DATABASE testdb;
Code language: SQL (Structured Query Language) (sql)

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

USE testdb;
Code language: SQL (Structured Query Language) (sql)

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

CREATE TABLE messages (
    id INT AUTO_INCREMENT,
    message VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);
Code language: SQL (Structured Query Language) (sql)

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

DELIMITER $$

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

DELIMITER ;
Code language: SQL (Structured Query Language) (sql)

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

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

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

CREATE USER dev@localhost 
IDENTIFIED BY 'Abcd1234';
Code language: SQL (Structured Query Language) (sql)

第六，向dev@localhost授予EXECUTE权限，以便它可以执行testdb数据库中的任何存储过程：

GRANT EXECUTE ON testdb.* 
TO dev@localhost;
Code language: SQL (Structured Query Language) (sql)

七、使用dev@localhost登录MySQL服务器：

mysql -u dev@localhost -p
Code language: SQL (Structured Query Language) (sql)

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

mysql> show databases;
Code language: SQL (Structured Query Language) (sql)

这是列表：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| testdb             |
+--------------------+
2 rows in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

九、选择testdb数据库：

mysql> use testdb;
Code language: SQL (Structured Query Language) (sql)

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

mysql> call InsertMessage('Hello World');
Code language: SQL (Structured Query Language) (sql)

这是输出：

Query OK, 1 row affected (0.01 sec)
Code language: SQL (Structured Query Language) (sql)

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

第十一，转到根会话并创建一个更新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 ;
Code language: SQL (Structured Query Language) (sql)

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

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

mysql> call UpdateMessage(1,'Good Bye');
Code language: SQL (Structured Query Language) (sql)

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

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

ERROR 1142 (42000): UPDATE command denied to user 'dev'@'localhost' for table 'messages'
Code language: SQL (Structured Query Language) (sql)

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

本教程有帮助吗？

DEFINER属性

SQL SECURITY特性

SQL SECURITY DEFINER

SQL SECURITY INVOKER

存储对象访问控制示例

`DEFINER`属性

`SQL SECURITY`特性

`SQL SECURITY DEFINER`

`SQL SECURITY INVOKER`