如何限制 SQL 用户权限为只读，同时仍允许执行存储过程？

Question

这是我的目标：创建一个登录名和关联的数据库用户，并授予其选择（只读）的权限，并执行不进行更新的存储过程。

背景故事是，这是 SQL 2005，是一个拥有十多年历史和层层不愉快事情的数据库。数百个存储过程，全部在模式 dbo 中。我知道...

我已经创建了登录名和用户，并授予该用户 db_datareader 权限。我还必须授予 EXECUTE 权限才能运行任何过程，但我的目标是禁止任何执行任何类型的 UPDATE/DELETE 操作的过程。尝试在这些过程上单独设置权限将是一场噩梦，我还没有准备好考虑。

我描述的这种情况可能吗？

Answer 1

一旦将逻辑封装在存储过程中，就不再需要对底层对象授予权限。实现目标的一种方法是使用表上的触发器。例如：

create trigger tr_del_tbl on tbl after delete as
begin
    if user_name() = 'foobar' and exists (select 1 from deleted)
        rollback
end

我还会使用角色来执行此操作，而不是某个特定用户。这样，如果您稍后遇到另一个需要类似处理的用户，您无需更改触发器；只需将其添加到角色即可。

Answer 2

除非您根据存储过程的操作手动分配权限，否则没有基于权限的模型可以做到这一点。您唯一的选择是触发器。请记住，只有在不使用 EXECUTE AS 的情况下才会起作用，因为 EXECUTE AS 会改变 user_name() 函数报告的用户方式。