如何为只读用户授予仅执行不含insert/update/delete操作存储过程的权限

可以实现该需求，核心逻辑是只给只读用户授予指定查询类存储过程的执行权限，拒绝其访问含增删改操作的存储过程，不需要额外做全局限制，按存储过程粒度管控即可。

前提说明 #

存储过程的权限管控默认独立于表权限：由于数据库所有权链机制，只要用户有存储过程的执行权限，哪怕没有底层表的写权限，只要存储过程所有者有对应写权限，执行存储过程时依然会触发增删改操作，所以不能只依赖用户的只读表权限限制，必须单独管控存储过程的执行权限。

具体操作步骤 #

1. 先收回该用户所有存储过程的默认执行权限 #

避免用户继承了公共角色的全局执行权限，从根源阻断访问不符合要求的存储过程的可能：

SQL Server 操作

REVOKE EXECUTE TO [reader];

MySQL 操作

REVOKE EXECUTE ON *.* FROM 'reader'@'%';
FLUSH PRIVILEGES;

2. 筛选仅包含查询逻辑的存储过程，单独授予执行权限 #

先通过系统表批量筛选无增删改操作的存储过程，确认后再授权：

SQL Server 筛选查询类存储过程示例

SELECT 
    p.name AS procedure_name
FROM 
    sys.procedures p
JOIN 
    sys.sql_modules m ON p.object_id = m.object_id
WHERE 
    m.definition NOT LIKE '%INSERT%'
    AND m.definition NOT LIKE '%UPDATE%'
    AND m.definition NOT LIKE '%DELETE%'
    AND m.definition NOT LIKE '%TRUNCATE%'
    AND m.definition NOT LIKE '%ALTER%'
    AND m.definition NOT LIKE '%DROP%';

• 筛选结果确认无误后，单条授权语法为：

GRANT EXECUTE ON [dbo].[存储过程名] TO [reader];

MySQL 筛选查询类存储过程示例

SELECT 
    ROUTINE_NAME
FROM 
    INFORMATION_SCHEMA.ROUTINES 
WHERE 
    ROUTINE_TYPE = 'PROCEDURE'
    AND ROUTINE_DEFINITION NOT LIKE '%INSERT%'
    AND ROUTINE_DEFINITION NOT LIKE '%UPDATE%'
    AND ROUTINE_DEFINITION NOT LIKE '%DELETE%'
    AND ROUTINE_DEFINITION NOT LIKE '%TRUNCATE%'
    AND ROUTINE_DEFINITION NOT LIKE '%ALTER%'
    AND ROUTINE_DEFINITION NOT LIKE '%DROP%';

• 筛选结果确认无误后，单条授权语法为：

GRANT EXECUTE ON PROCEDURE 库名.存储过程名 TO 'reader'@'%';
FLUSH PRIVILEGES;

3. 可选兜底限制 #

如果担心漏选存储过程，可以给reader用户额外添加数据库级别的只读限制：

• SQL Server 可以给用户设置默认只读事务，或开启数据库只读属性
• MySQL 仅给该用户授予表的SELECT权限，即使误给了含写操作的存储过程执行权限，也会因为用户没有底层表写权限执行失败

内容的提问来源于stack exchange，提问作者Alina Golov