如何解决SQL Server中设置行只读时的安全策略冲突问题

你的核心问题在于当前块谓词检查的是修改后的IsReadOnly值，导致将值从0改为1时触发了阻止逻辑。要解决这个问题，需要利用SQL Server行级安全中的OLD关键字，让谓词基于修改前的行状态做判断，而非新值。

解决方案步骤 #

1. 重新定义块谓词函数，基于修改前的IsReadOnly值进行校验：

CREATE OR ALTER FUNCTION dbo.ReadOnlyBlockPredicate(@OldIsReadOnly BIT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS Result WHERE @OldIsReadOnly = 0;

该函数逻辑为：仅当修改前行的IsReadOnly为0（可写状态）时，才允许执行UPDATE或DELETE操作。

2. 更新安全策略，指定块谓词使用OLD值作为参数：

-- 删除原有冲突的块谓词
ALTER SECURITY POLICY ReadOnlyPolicy
DROP BLOCK PREDICATE ON dbo.Plant;

-- 添加新的块谓词，绑定修改前的行状态
ALTER SECURITY POLICY ReadOnlyPolicy
ADD BLOCK PREDICATE dbo.ReadOnlyBlockPredicate(OLD.IsReadOnly) 
ON dbo.Plant
FOR UPDATE, DELETE;

逻辑说明 #

• 当行原本IsReadOnly为0时：允许任何修改（包括将其改为1），也支持删除操作，满足事件发生前的可编辑需求。
• 当行IsReadOnly已为1时：直接禁止所有UPDATE和DELETE操作，符合事件发生后的只读要求。
• 若需要限制事件发生后的插入操作，可额外添加针对INSERT的块谓词（比如校验插入时IsReadOnly的默认值或关联事件状态）。

另外，你原有的ReadOnlyFilterPredicate函数没有实际过滤逻辑（始终返回1），如果不需要限制行的可见性，可删除该过滤谓词以减少性能开销：

ALTER SECURITY POLICY ReadOnlyPolicy
DROP FILTER PREDICATE ON dbo.Plant;

修改后即可直接将IsReadOnly从0改为1，无需临时禁用安全策略，同时保证只读行不会被非法修改。

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