Set rows to read-only

Question

I have an application with the following requirements:

Before a predefined event (in my application) occurs, I need to update and insert rows in different tables.
After the event, certain rows in various tables should be set to "ReadOnly," meaning no updates or deletes are allowed on these specific rows.

To implement this, I added an IsReadOnly column to all relevant tables and created functions and security policies to enforce these restrictions.

-- Create schema-bound filter predicate function CREATE FUNCTION dbo.ReadOnlyFilterPredicate(@IsReadOnly BIT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS Result; -- Create schema-bound block predicate function CREATE FUNCTION dbo.ReadOnlyBlockPredicate(@IsReadOnly BIT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS Result WHERE @IsReadOnly = 0; -- Create the security policy with the filter predicate CREATE SECURITY POLICY ReadOnlyPolicy ADD FILTER PREDICATE dbo.ReadOnlyFilterPredicate(IsReadOnly) ON dbo.Plant WITH (STATE = ON); -- Alter the security policy to add block predicates for UPDATE and DELETE ALTER SECURITY POLICY ReadOnlyPolicy ADD BLOCK PREDICATE dbo.ReadOnlyBlockPredicate(IsReadOnly) ON dbo.Plant

When a row has IsReadOnly = 0, I can update or delete it as expected. Conversely, if a row has IsReadOnly = 1, updates or deletes are not permitted, which is also as expected.

However, when the event occurs and I attempt to change IsReadOnly from 0 to 1, I encounter an error:

The attempted operation failed because the target object ‘MyTablent' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.

Disabling the security policy temporarily allows me to set IsReadOnly to 1 and then re-enable the policy, which resolves the issue.

However, during the brief period when the security policy is disabled, there is a risk that unauthorized changes could be made to rows.

The problem seems to be that the predicate evaluates the "new" value (e.g., IsReadOnly = 1) instead of the actual value in the table, preventing me from changing IsReadOnly.

I am seeking ideas on how to address this requirement effectively.

Why, out of interest, give dbo.ReadOnlyFilterPredicate the parameter @IsReadOnly when it doesn't make use of it? — Thom A
– Thom A ♦, Commented May 15 at 14:09
Sounds like you need a simple trigger? I didn't even know these policies could be used for blocking things. Anyways, learn.microsoft.com/en-us/sql/t-sql/statements/… seems to imply, you can specify BEFORE/AFTER, which might do the trick? — siggemannen
– siggemannen, Commented May 15 at 14:15
It would be easier with a trigger. During UPDATE you can check which fields are updated, if only ReadOnly field is updated then you can allow, if not, check if ReadOnly is 1 or 0 and allow or not accordingly. For DELETE you just check ReadOnly flag. — endo64
– endo64, Commented May 15 at 16:35

Thom A · Accepted Answer · 2025-05-16 12:48:57Z

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;

Thank you all very much for your input, especially Thom A for his example. Your example worked perfectly on my database with your new table, but not with my other tables. I had to do quite a bit of analysis before I discovered that I had a trigger which was also writing to the table, and that was the culprit. After correcting the trigger, everything worked well. Once again, thank you all very much!
If you have a TRIGGER then that will likely be stopped by the block predicate if you change the row to "ReadOnly", @MagicJP , as most TRIGGERs are AFTER <DML operation> and so it's then affecting a "ReadOnly" row. You would likely be better off with a a new question for a workaround to that, as such as change to this question would invalidate the answer.
Thanks. I was able to solve the problem by adding a condition in my trigger. If IsReadOnly = 1, then the trigger does not attempt to write to the row, and everything runs smoothly. Thanks for your help and input. It helped me find the right solution. Thanks a lot!

Collectives™ on Stack Overflow

Set rows to read-only

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related