In the first entry in this series (to view, follow this link), I took a look at the how row level security worked with read operations. Now in this entry, I want to expand that to how you use it to limit what a user might write to, or delete from a table.

To recap our current situation, we have three users:

CREATE USER BigHat WITHOUT LOGIN;–DROP IF EXISTS works with users as well as objects:

CREATE USER MedHat WITHOUT LOGIN; –MediumHat, which we want to get all of SmallHat’s rights, but not BigHats

CREATE USER SmallHat WITHOUT LOGIN; — gets a minimal amount of security

A VERY simple table:

CREATE TABLE Demo.SaleItem
(
    SaleItemId    int CONSTRAINT PKSaleIitem PRIMARY KEY,
    ManagedByUser sysname
)

With data that looks like this (the dbo can see all of the data).

SELECT *
FROM   Demo.SaleItem

Which returns:

SaleItemId  ManagedByUser
———– —————
1           BigHat
2           BigHat
3           MedHat
4           MedHat
5           SmallHat
6           SmallHat

The following security policy has been implemented:

CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
    ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem
    WITH (STATE = ON, SCHEMABINDING = ON);

Based on the following TVF:

CREATE FUNCTION rowLevelSecurity.ManagedByUser$SecurityPredicate (@ManagedByUser AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN (SELECT 1 AS ManagedByUser$SecurityPredicate
            WHERE @ManagedByUser = USER_NAME()
               OR (USER_NAME() = ‘MedHat’ and @ManagedByUser <> ‘BigHat’)
               OR (USER_NAME() IN (‘BigHat’,’dbo’))); –give ‘dbo’ full rights

Now the goal is going to be to demonstrate how we can execute INSERTs, UPDATEs, and DELETEs on the table.

First, I will grant the user rights to INSERT, UPDATE and DELETE from the table to all of the users we have set up:

GRANT INSERT,UPDATE,DELETE ON Demo.SaleItem TO SmallHat, MedHat,BigHat;

Before we start to look at the BLOCK predicates, let’t take a look at what the user can do at this point, starting with an INSERT from the SmallHat user, with a ManagedByUser that we configured back in part 1 that they cannot see:

EXECUTE AS USER = ‘SmallHat’;
GO
INSERT INTO Demo.SaleItem (saleItemId, ManagedByUser)
VALUES (7,’BigHat’);
GO
SELECT * FROM Demo.SaleItem;
SELECT COUNT(*) FROM Demo.SaleItem WHERE saleItemId = 7
GO
REVERT

This returns:

saleItemId  ManagedByUser
———– —————
5           SmallHat
6           SmallHat

———–
0

Which would, no doubt in my mind, drive the end user nuts thinking "Where did my row go?" So in just a bit, we will fix so it can’t occur, if you don’t want it to. In my contrived business rules, I will establish a case where we want to do just this, so that the user could update a row and it is no longer in view. (A real version might be to hide soft deleted rows from most users. ( For example a deletedFlag bit NOT NULL column with a value of 1, perhaps.) After RTM I will give that case a test when I test performance.)

As the dbo:

SELECT *
FROM   Demo.SaleItem
WHERE  saleItemId = 7;

I can see it

saleItemId  ManagedByUser
———– —————
7           BigHat

Next up, can we UPDATE or DELETE the row as the SmallHat user? It seems fairly obvious we cannot since we can’t see it in a WHERE clause of a SELECT, but it never hurts to check:

EXECUTE AS USER = ‘SmallHat’;
GO
UPDATE Demo.SaleItem
SET    ManagedByUser = ‘SmallHat’
WHERE  SaleItemId = 7; –Give it back to me!

DELETE Demo.SaleItem
WHERE  SaleItemId = 7; –or just delete it
GO
REVERT;
GO
SELECT *
FROM   Demo.SaleItem
WHERE  SaleItemId = 7;

If you haven’t turn the NOCOUNT setting on for your connection, you will see:

(0 row(s) affected)

(0 row(s) affected)

SaleItemId  ManagedByUser
———– —————
7           BigHat

So the FILTER predicate we previously established works on UPDATES and DELETEs as well. Great. Now let’s work on making sure that
the user can’t do something silly to the data they have in their view UNLESS it is an acceptable purpose.

I will drop the exiting security policy for the time being to demonstrate how the block predicate works. We will put back the filter in the very last part of the blog to meet the requirement of letting the user modify data to a state they can’t see:

DROP SECURITY POLICY IF EXISTS rowLevelSecurity.Demo_SaleItem_SecurityPolicy;

Next we are going BLOCK predicate, that will block users from doing certain options. There are two block types: AFTER and BEFORE.

• AFTER – If the row would not match your ability to see the data after the operation, it will fail. Here we have INSERT and UPDATE. So in my example scenario, for INSERT SmallHat would not be able to insert a row that didn’t have ‘SmallHat’ for the ManagedByUser. For UPDATE (with no before setting), SmallHat could update any row they can see to ‘SmallHat’, but not something else.
• BEFORE – This seem like it is the same thing as the filter predicate, saying that if you can’t see the row, you can’t UPDATE or DELETE it, but there is a subtle difference. This says, no matter if you can see the row, before you can modify the row, it must match the predicate. So in our case, if we added BEFORE update, and dropped the FILTER predicate, the SmallHat could see all rows, but only change the rows they manage.

I am going to set one of the obvious (to me) set of row level security predicates that one might set in a realistic scenario for a managed by user type column.

1. BLOCK AFTER INSERT, to say that if you can’t see the row, that you can’t create a new row.
2. BLOCK UPDATE and DELETE you don’t own.
3. Allow you to update a row to a manager that you cannot see, to enable you to pass the row to a collegue. Naturally some level of "are you sure" protection needs to be placed on the row, because once you update it, it will be gone from your view

So, using the security predicate function we already created, we apply the following:

CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
    ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.saleItem AFTER INSERT,
    ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.saleItem BEFORE UPDATE,
    ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.saleItem BEFORE DELETE
    WITH (STATE = ON, SCHEMABINDING = ON);

Now, let’s try again add a row that SmallHat couldn’t see:

EXECUTE AS USER = ‘SmallHat’;
GO
INSERT INTO Demo.SaleItem (SaleItemId, ManagedByUser)
VALUES (8,’BigHat’);

Nice try, it says, but:

Msg 33504, Level 16, State 1, Line 171
The attempted operation failed because the target object ‘SimpleDemos.Demo.SaleItem’ 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.
The statement has been terminated.

(Notice that the error message includes that database and schema of the object too. Nice!)

Now try again, with SmallHat as the ManagedByUser column value:

INSERT INTO Demo.SaleItem (SaleItemId, ManagedByUser)
VALUES (8,’SmallHat’);

This works, and now to show that it worked (still in the security context of the SmallHat user:

SELECT * FROM Demo.SaleItem

This returns:

SaleItemId  ManagedByUser
———– —————
1           BigHat
2           BigHat
3           MedHat
4           MedHat
5           SmallHat
6           SmallHat
7           BigHat
8           SmallHat

And we see all rows in the table because we dropped the FILTER predicate.

Next, continuing in the security context of the SmallHat user, let’s try the UPDATE and DELETE we tried earlier to SaleItemId 7:

UPDATE Demo.SaleItem
SET    ManagedByUser = ‘SmallHat’
WHERE  SaleItemId = 7;
GO
DELETE FROM Demo.SaleItem WHERE SaleItemId = 7;

Two errors that looks just like this:

Msg 33504, Level 16, State 1, Line 211
The attempted operation failed because the target object ‘SimpleDemos.Demo.SaleItem’ 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.
The statement has been terminated.

Msg 33504, Level 16, State 1, Line 211
The attempted operation failed because the target object ‘SimpleDemos.Demo.SaleItem’ 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.
The statement has been terminated.

Showing we have stopped the user from modifying the rows, even though they can see them.

Now, lets use the security hole we left. That of letting the user update the row by not checking that the value matched AFTER the UPDATE operation , in the following case changing the ManagedByUser column to another user.

UPDATE Demo.SaleItem
SET    ManagedByUser = ‘BigHat’
WHERE  SaleItemId = 8;

SELECT * FROM Demo.SaleItem WHERE SaleItemId = 8;

You can see that this was allowed

SaleItemId  ManagedByUser
———– —————
8           BigHat

Now, let’s go back to the context of the dbo, and let’s add the FILTER predicate back to the security policy, to show how you add
a policy to one

REVERT;

We can add a predicate using the same syntax, without knowing the other items that are in the policy.

ALTER SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
    ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem;
GO

Now we have the following policy defined:

CREATE SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
    ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem,
    ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem AFTER INSERT,
    ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem BEFORE UPDATE,
    ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem BEFORE DELETE
    WITH (STATE = ON, SCHEMABINDING = ON);

But what if we want to remove a predicate, in this example, say the redundant BEFORE predicates to the FILTER one we just added back.
Note that there is no name to each predicate, so for example, to drop the different BEFORE BLOCK predicates on Demo.SaleItem:

ALTER SECURITY POLICY rowLevelSecurity.Demo_SaleItem_SecurityPolicy
    DROP BLOCK PREDICATE ON Demo.SaleItem BEFORE UPDATE,
    DROP BLOCK PREDICATE ON Demo.SaleItem BEFORE DELETE;

I rather expect that in many cases it will be easier to drop and recreate the policy that is desired, but it is available to remove individual predicates. You can get away with only specifying the predicate type because as we mentioned in the previous entry in this series, you can only have one predicate of a given type on each table.

In this blog, I wanted to cover the range of things one might do with one table and a fairly simple predicate function. In the next entry, I will get a bit more complex with the functions you can build to apply, including accessing other tables. I won’t cover any more about the DDL of the CREATE SECURITY POLICY statement, just note that it does not have to center on one table. The following is certainly possible:

CREATE SECURITY POLICY rowLevelSecurity.MultipleTables
    ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.Sale,
    ADD FILTER PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem,
    ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.Sale AFTER INSERT,
    ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.SaleItem BEFORE UPDATE,
    ADD BLOCK PREDICATE rowLevelSecurity.ManagedByUser$SecurityPredicate(ManagedByUser) ON Demo.UserName BEFORE DELETE
    WITH (STATE = ON, SCHEMABINDING = ON);

I don’t believe there to be any real advantage to doing it this way versus another other than convenience of turning policies off being easier (and I could see error troubleshooting to be more difficult.) I will update the blog if this turns out to not be correct.