MySQL编程进阶之：存储过程的权限管理：`DEFINER`与`SQL SECURITY`的用法与安全考量。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷们，晚上好！今天咱们聊聊MySQL存储过程里头有点儿意思，但又容易被忽略的家伙——DEFINER和SQL SECURITY。别怕，听我白话白话，保证您听完能明白个七七八八，以后写存储过程的时候也能更踏实。

开场白：存储过程，谁说了算？

想象一下，你是一个小区物业经理（存储过程），负责处理小区各种事务（数据操作）。但是，这小区里住着各种各样的人（用户），有业主，有租户，有访客，权限各不相同。你物业经理干活的时候，是按照你自己的身份（DEFINER），还是按照报修的业主身份（INVOKER）来决定能不能进业主家门（访问特定数据）呢？这就是DEFINER和SQL SECURITY要解决的问题。

第一幕：DEFINER——存储过程的“户口本”

DEFINER顾名思义，就是定义存储过程的人。它就像存储过程的“户口本”上登记的名字，指明了这个存储过程是谁创建的。

默认情况： 默认情况下，DEFINER是创建存储过程的用户。也就是，谁写的这个存储过程，谁就是DEFINER。
显式指定： 你也可以在创建存储过程的时候显式指定DEFINER。
```
CREATE DEFINER=`root`@`localhost` PROCEDURE `my_procedure`()
BEGIN
  -- 存储过程的内容
END
```
这段代码的意思是，不管谁执行这个CREATE PROCEDURE语句，这个存储过程的DEFINER都是root@localhost。

查看DEFINER: 通过查询information_schema.routines表可以查看存储过程的定义者信息。

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, DEFINER FROM information_schema.routines WHERE ROUTINE_TYPE = 'PROCEDURE';

第二幕：SQL SECURITY——存储过程的“通行证”

SQL SECURITY决定了存储过程执行时使用的权限上下文。它有两个取值：

DEFINER (默认值): 存储过程使用DEFINER指定的用户的权限来执行。就像物业经理拿着自己的工作证（DEFINER的权限）去业主家处理事务。
INVOKER: 存储过程使用调用者的权限来执行。就像物业经理需要根据报修的业主身份（INVOKER的权限）才能决定能不能进业主家门。

简单来说，SQL SECURITY DEFINER强调的是存储过程本身的权限，SQL SECURITY INVOKER强调的是调用者的权限。

第三幕：代码说话，实例演示

咱们来几个例子，更直观地感受一下DEFINER和SQL SECURITY的作用。

场景设定:

有两个用户：alice@localhost 和 bob@localhost。
有一个数据库 mydb。
有一个表 employees，只允许 alice@localhost 访问。

1. SQL SECURITY DEFINER

-- 以 root@localhost 用户身份登录 MySQL
-- 创建 alice 用户并授权
CREATE USER 'alice'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON mydb.employees TO 'alice'@'localhost';

-- 创建 bob 用户
CREATE USER 'bob'@'localhost' IDENTIFIED BY 'password';

-- 创建存储过程，DEFINER 为 alice，SQL SECURITY DEFINER
USE mydb;
CREATE DEFINER='alice'@'localhost' PROCEDURE `get_employees`()
SQL SECURITY DEFINER
BEGIN
  SELECT * FROM employees;
END;

-- 以 bob@localhost 用户身份登录 MySQL
-- 执行存储过程
CALL mydb.get_employees();

在这个例子中，即使 bob@localhost 用户没有访问 employees 表的权限，但他仍然可以成功执行 get_employees 存储过程，因为存储过程是以 alice@localhost 的权限执行的。

2. SQL SECURITY INVOKER

-- 以 root@localhost 用户身份登录 MySQL
-- 删除之前创建的 bob 用户的权限（如果存在）
REVOKE ALL PRIVILEGES ON mydb.* FROM 'bob'@'localhost';

-- 创建存储过程，DEFINER 为 alice，SQL SECURITY INVOKER
USE mydb;
CREATE DEFINER='alice'@'localhost' PROCEDURE `get_employees_invoker`()
SQL SECURITY INVOKER
BEGIN
  SELECT * FROM employees;
END;

-- 以 bob@localhost 用户身份登录 MySQL
-- 执行存储过程
CALL mydb.get_employees_invoker();

这次，bob@localhost 用户执行 get_employees_invoker 存储过程时，会因为权限不足而报错，因为存储过程是以 bob@localhost 的权限执行的，而 bob@localhost 没有访问 employees 表的权限。

3. 跨数据库访问

假设还有另外一个数据库 otherdb, 用户 alice@localhost有权限访问otherdb.products，但用户 bob@localhost 没有。

-- 以 root@localhost 用户身份登录 MySQL
-- 创建 products 表
CREATE DATABASE IF NOT EXISTS `otherdb`;
USE `otherdb`;
CREATE TABLE `products` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL
);

-- 授权 alice 访问 otherdb.products
GRANT SELECT ON `otherdb`.`products` TO 'alice'@'localhost';

-- 创建存储过程，DEFINER 为 alice，SQL SECURITY DEFINER，跨数据库访问
USE `mydb`;
CREATE DEFINER='alice'@'localhost' PROCEDURE `get_products`()
SQL SECURITY DEFINER
BEGIN
  SELECT * FROM `otherdb`.`products`;
END;

-- 以 bob@localhost 用户身份登录 MySQL
-- 执行存储过程
CALL `mydb`.`get_products`();

bob@localhost 仍然可以成功执行这个存储过程，尽管他自己没有访问otherdb.products的权限，因为存储过程是以alice@localhost的权限执行的。

第四幕：DEFINER与SQL SECURITY的组合拳

第五幕：安全考量，敲黑板！

DEFINER 和 SQL SECURITY 的使用涉及权限管理，需要格外小心，不然容易出事儿。

SQL SECURITY DEFINER的风险： 如果 DEFINER 是一个高权限用户（比如 root），那么任何能够执行该存储过程的用户，都相当于拥有了 root 的权限。这会带来严重的安全风险，所以要慎用。想想看，如果 bob@localhost 可以通过存储过程间接拥有 root 的权限，那还得了？
DEFINER用户的权限变更： 如果 DEFINER 用户的权限被收回，或者用户被删除，那么该存储过程可能会失效，或者产生不可预知的行为。因此，要确保 DEFINER 用户拥有稳定的权限。
存储过程的权限提升： SQL SECURITY DEFINER 允许存储过程提升调用者的权限，这在某些场景下是必要的，但同时也带来了潜在的风险。要仔细评估是否真的需要提升权限，并采取相应的安全措施，比如对存储过程的输入参数进行严格的校验。
代码注入风险： 即使使用SQL SECURITY INVOKER，如果存储过程的输入参数没有经过充分的校验，仍然可能存在SQL注入的风险。因此，务必对输入参数进行参数化查询或者转义处理。

最佳实践建议:

最小权限原则： 尽量使用 SQL SECURITY INVOKER，并确保调用者拥有执行存储过程所需的最小权限。
避免使用高权限用户作为 DEFINER： 尽量使用一个权限受限的专门用户作为 DEFINER。
定期审计存储过程的权限： 定期检查存储过程的 DEFINER 和 SQL SECURITY 设置，以及相关用户的权限，确保符合安全要求。
参数化查询： 永远使用参数化查询，避免SQL注入。
代码审查： 对存储过程的代码进行仔细审查，确保没有安全漏洞。

第六幕：实战演练，防患未然

举个更具体的例子，假设你有一个存储过程，用于更新用户的密码。

-- 创建一个用于更新用户密码的存储过程
CREATE DEFINER='app_user'@'localhost' PROCEDURE `update_password`(
  IN p_username VARCHAR(255),
  IN p_new_password VARCHAR(255)
)
SQL SECURITY DEFINER
BEGIN
  -- 检查调用者是否有权限更新密码
  -- 可以在这里添加权限检查逻辑
  -- 例如，检查调用者是否是管理员，或者是否是该用户本人

  -- 更新密码
  UPDATE users
  SET password = SHA2(p_new_password, 256)
  WHERE username = p_username;
END;

在这个例子中，DEFINER 是 app_user@localhost，这是一个专门用于应用程序访问数据库的用户。 SQL SECURITY DEFINER 意味着存储过程将以 app_user@localhost 的权限执行。

为了提高安全性，可以采取以下措施：

权限检查： 在存储过程内部添加权限检查逻辑，确保只有授权的用户才能更新密码。
参数校验： 对 p_username 和 p_new_password 进行严格的校验，防止SQL注入。
密码加密： 使用 SHA2 等强加密算法对密码进行加密存储。
只授予必要的权限： 确保 app_user@localhost 只拥有更新密码所需的最小权限，比如 UPDATE users 表的权限。

收尾：安全第一，谨慎行事

好了，各位，今天关于MySQL存储过程的DEFINER和SQL SECURITY就聊到这里。记住，权限管理是数据库安全的重要组成部分。一定要谨慎使用DEFINER和SQL SECURITY，并采取相应的安全措施，才能确保数据库的安全。祝大家编码愉快，永不踩坑！咱们下期再见！

发表回复 取消回复

发表回复取消回复