Set rows to read-only

Question

I have an application with the following requirements:

1. Before a predefined event (in my application) occurs, I need to update and insert rows in different tables.

2. 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.

Answer 1

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;