如何实现基于SQL存储过程的权限过滤？-SQL-PHP中文网

mysql存储过程本身无行级或字段级权限过滤能力，必须设sql security invoker启用调用者权限检查，配合视图、白名单拼接及最小权限definer账号才能实现安全过滤。

如何实现基于sql存储过程的权限过滤？

MySQL 存储过程本身不提供行级或字段级权限过滤能力，所谓“基于存储过程的权限过滤”，本质是用过程封装逻辑 + 显式控制执行上下文 + 外部配合（如视图、会话变量、应用层）来达成。直接在过程里写 SELECT * FROM users 并不能阻止调用者看到不该看的数据——除非你从源头切断权限路径。

必须设 `SQL SECURITY INVOKER` 才能启用调用者权限检查

默认情况下，MySQL 存储过程以 DEFINER 身份运行，也就是说：哪怕调用者是 'guest'@'%'，只要过程定义者是 'root'@'%'，过程内部所有 SQL 都按 root 权限执行。这完全绕过调用者权限体系。

显式声明 SQL SECURITY INVOKER 后，过程内每条语句（如 SELECT、UPDATE）都会实时校验调用者是否具备对应表的权限
创建时写：CREATE PROCEDURE sp_get_user() SQL SECURITY INVOKER BEGIN ... END
修改已有过程：ALTER PROCEDURE db.sp_get_user SQL SECURITY INVOKER
验证是否生效：SELECT ROUTINE_NAME, SQL_SECURITY FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='db' AND ROUTINE_NAME='sp_get_user'
常见坑：没加这句，或误写成 SQL SECURITY DEFINER，后续所有授权都白做

只靠 `GRANT EXECUTE ON PROCEDURE` 无法限制过程内访问的表

很多人以为给用户授了 EXECUTE 权限，就等于只允许他调用这个过程、不能碰底层表——这是错的。MySQL 的 EXECUTE 权限只控制能否 CALL，不控制过程体内的 SQL 执行权限。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

如果过程里有 SELECT salary FROM employees，而调用者自己有 employees 表的 SELECT 权限，他就真能查出 salary
真正有效的做法是：让调用者**没有**任何表权限，只给 EXECUTE，再把过程设为 SQL SECURITY DEFINER，并确保 DEFINER 是一个权限极小的专用账号（比如只对 vw_user_summary 视图有 SELECT）
注意：MySQL 8.0.16+ 才稳定支持 GRANT EXECUTE ON PROCEDURE；5.7 及以前版本该语法被忽略，实际生效的是数据库级 EXECUTE 权限
执行 REVOKE EXECUTE ON PROCEDURE db.sp_x FROM 'u'@'%' 在 8.0 中可能无效——因为权限继承自 db.* 级别，得先 REVOKE EXECUTE ON db.* FROM 'u'@'%'

动态租户/角色过滤必须靠拼接 + 白名单 + `QUOTE()`

存储过程没法像 PostgreSQL 函数那样用 current_setting() 或 SQL Server 的 SESSION_CONTEXT() 直接读取业务上下文。你得靠客户端传参 + 会话变量 + 安全拼接来模拟。

调用前必须设置：SET @tenant_id = 123;，并在过程开头用 IF @tenant_id IS NULL THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Missing tenant context'; END IF;
拼接 WHERE 条件时，列名（如 tenant_id、dept_code）必须来自白名单：CASE WHEN p_field = 'tenant_id' THEN 'tenant_id' WHEN p_field = 'dept_code' THEN 'dept_code' ELSE NULL END
值部分必须用 QUOTE(@tenant_id) 包裹，不能直接 CONCAT('WHERE ', p_field, ' = ', @tenant_id)
PREPARE 不能绑定列名或表名，所以白名单校验和字符串拼接无法避免——只能靠人工核对 @sql 内容，上线前删掉 SELECT @sql; 语句
禁止在拼接中引入子查询、UNION、函数调用等复杂结构，否则校验逻辑会失控

敏感字段过滤不能依赖过程体，得靠视图或应用层

如果你希望用户调用 sp_get_user() 只能看到 id 和 name，看不到 salary 或 phone，最可靠的方式不是在过程里写 SELECT id, name FROM users，而是：

建一个视图 v_user_public，只暴露安全字段，并给调用者授予该视图的 SELECT 权限
过程设为 SQL SECURITY INVOKER，内部只查这个视图
或者干脆不用过程，让应用直查视图——过程在这里只是多了一层无意义的封装
若必须用过程返回结果集，且要动态决定字段，则需用 SELECT ... INTO + 动态列名白名单 + CONCAT 拼接完整 SELECT 语句，风险极高，仅限内部工具使用
别指望 SELECT * 加注释“此处已脱敏”——上线后没人记得注释，审计也查不到真实执行路径