Hi friends, continuing from the previous post in this post we will learn how to add the blocking predicate to restrict the users to perform DML operation (INSERT, UPDATE, DELETE) for others users data.
If you want to read the previous posts on Row Level Security you can find those post here:
Let’s now add a block predicate and check if it explicitly raise an error or not after adding block predicate.
There are 4 block predicates AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE available and we will test using AFTER INSERT predicate.
Now alter the security policy as per the below query –
–ADD block predicate
ALTER SECURITY POLICY PersonSecurityPolicy
ADD BLOCK PREDICATE dbo.PersonPredicate(User_Access)
ON dbo.Person AFTER INSERT
We can find the information about the predicates that we have added to the security policy using system dmv like this:
SELECT * FROM sys.security_predicates
As we have added a block predicate to the security policy so it adds another row for this as previously there was no block predicate added to it, only FILTER predicate was present.
Now, execute the query as user ‘User_CS’ and try to insert the row for another user ‘User_IT’ using following query:
EXECUTE AS USER = ‘User_CS’
INSERT INTO Person (PersonName, Department, Salary, User_Access)
SELECT ‘Sumit’, ‘IT’, 35000, ‘User_IT’
This time it will not insert any rows and raise an error.
‘The attempted operation failed because the target object dbo.Person has a block predicate that conflicts with this operation. In case 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 block predicate.’
This time it will throw an error and will not insert any rows for user ‘User_IT’. Hence after adding block predicate DML operations are restricted for unauthorized users.
Here are the few limitation and restrictions in Row Level Security that are as:
Limitation and Restrictions in Row Level Security
- Predicate function must be created with WITH SCHEMABINDING. If function is created without Schemabinding and try to bind it to a Security Policy you will get an error.
- Indexed views cannot be created on a table on which Row Level Security is implemented.
- In-Memory tables are not supported for Row Level Security
- Full text indexes are not supported.