Set rows to read-only

After some trial and error, I found the following works. I assumed that in addition to stopping a row marked as "readonly" being UPDATEd you also wanted to stop it being DELETEd.

The main thing I therefore changed was the SECURITY POLICY. There is, as far as I can tell, no need for the FILTER PREDICATE, so I got rid of that. I then changed the BLOCK PREDICATE to be BEFORE UPDATE, and also added a BLOCK PREDICATE for BEFORE DELETE as well.

CREATE TABLE dbo.Plant (PlantID int IDENTITY (1,1) CONSTRAINT PK_Plant PRIMARY KEY,
                        SomeValue varchar(20) NOT NULL,
                        IsReadOnly bit NOT NULL CONSTRAINT DF_Plant_IsReadOnly DEFAULT 0);
GO

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

CREATE SECURITY POLICY ReadOnlyPolicyUpdate
ADD BLOCK PREDICATE dbo.ReadOnlyBlockPredicate(IsReadOnly) ON dbo.Plant BEFORE UPDATE,
ADD BLOCK PREDICATE dbo.ReadOnlyBlockPredicate(IsReadOnly) ON dbo.Plant BEFORE DELETE;

With this, trying to UPDATE a row to "Readonly" worked fine, but I was unable to both UPDATE or DELETE that row further, but performing such operations on a "writable" row worked fine:

INSERT INTO dbo.Plant (SomeValue,
                       IsReadOnly)
VALUES('abc',0),
      ('def',0),
      ('xyz',0);
GO
PRINT N'Change from writable to readonly';
UPDATE dbo.Plant
SET IsReadOnly = 1
WHERE SomeValue = 'xyz';
GO
PRINT N'Change SomeValue of writeable row';
UPDATE dbo.Plant
SET SomeValue = 'fed'
WHERE SomeValue = 'def';

GO
PRINT N'Change from readonly to writable; should fail';
UPDATE dbo.Plant
SET IsReadOnly = 0
WHERE SomeValue = 'xyz';
GO
PRINT N'Delete readonly row; should fail';
DELETE dbo.Plant
WHERE SomeValue = 'xyz';
GO
PRINT N'Delete writable row';
DELETE dbo.Plant
WHERE SomeValue = 'abc';
GO
SELECT *
FROM dbo.Plant;

If you wanted to enable some people to be able to edit read only rows, you could to this by storing a list of users in a table that are permitted to do so. I use a database role here, and then check if the user is part of that role by using IS_MEMBER (IS_SRVROLEMEMBER can be used for server roles, if needed).

CREATE USER SomeUser WITHOUT LOGIN;
CREATE ROLE db_readonlywriter;
GO
ALTER ROLE db_datareader ADD MEMBER SomeUser;
ALTER ROLE db_datawriter ADD MEMBER SomeUser;
ALTER ROLE db_readonlywriter ADD MEMBER SomeUser;

GO
--Need to DROP the policy so we can ALTER the function
DROP SECURITY POLICY ReadOnlyPolicyUpdate;
GO
ALTER FUNCTION dbo.ReadOnlyBlockPredicate (@IsReadOnly bit)
RETURNS table
WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS Result
           WHERE @IsReadOnly = 0
              --OR IS_SRVROLEMEMBER('sysadmin') = 1 --Is you want sysadmins to be able to do things, uncomment
              OR IS_MEMBER('db_readonlywriter') = 1;
GO
CREATE SECURITY POLICY ReadOnlyPolicyUpdate
ADD BLOCK PREDICATE dbo.ReadOnlyBlockPredicate (IsReadOnly)
    ON dbo.Plant BEFORE UPDATE,
ADD BLOCK PREDICATE dbo.ReadOnlyBlockPredicate (IsReadOnly)
    ON dbo.Plant BEFORE DELETE;
GO
EXECUTE AS USER = N'SomeUser';
GO
PRINT N'Change value of a read only row';
UPDATE dbo.Plant
SET SomeValue = 'zyx'
WHERE SomeValue = 'xyz';
GO
REVERT;
GO
--Check we can't we it still
UPDATE dbo.Plant
SET IsReadOnly = 0
WHERE SomeValue = 'zyx';
GO
SELECT *
FROM dbo.Plant;