This post is the continuation of the Azure SQL Server security recommendations series. In my previous blog, we went through the filter predicate. In this blog, we will cover block predicate and highlight some of the best practices recommended by Microsoft.
Block Predicate blocks users from making changes to the table which violate the predicate. It covers UPDATE, INSERT and DELETE operations. It is applicable only to SQL Server 2016 (from 13.x) & Azure SQL DB and doesn’t work in Azure SQL Data Warehouse.
There are different security protocol options available in block predicate:
- BEFORE UPDATE – Checks the existing row values against the predicate rules and then updates
- BEFORE DELETE – Checks the existing row against the predicate rules and then deletes
- AFTER UPDATE – Blocks update if the new value doesn’t pass the predicate
- AFTER INSERT – Blocks insert if the new row doesn’t pass the predicate.
We will take the same example of employee and department table as we saw in our previous blog. In our scenario, the employee can make changes only to their records and the department heads can add or make changes to employee records in their department.
We will use the same security function that we created for filter predicate, but, we will add block security protocols to it as shown below:
Now, that we have created our security protocol, let us test them with our test users- ‘harry’ and ‘fly’.
Let’s try to update a record for an employee named ‘ron’ with ‘harry’ as the user and see how the security protocol works. We will get 0 rows affected because according to filter predicate, user ‘harry’ doesn’t have access to update over other records.
Likewise, let us try to update the department of the employee named ‘hermione’ to ‘Magical Portions’ using the ‘fly’ user:
Here, the permission to update is denied because of the AFTER UPDATE predicate.
Current predicates wouldn’t allow an employee to be transferred from one department to another. So, to facilitate that, we can remove the AFTER UPDATE predicate. This way, department user ‘fly’ will be able to update the department of an employee within the department. Let’s remove this predicate and try running the same query.
Now the employee ‘hermione’ has to be reassigned to ‘Magical Portions’ department.
This is how the Block Predicate can be used to prevent unauthorized DML on the tables containing sensitive information.
Microsoft has laid down some best practices for Row Level Security, which applies for both filter and block predicate, as mentioned below:
- It’s recommended to have a dedicated schema for security protocols and functions.
- Scrutiny should be exercised while granting ALTER ANY SECURITY POLICY to any user. Only users like security admin should be given such elevated privileges. Also, the security admin doesn’t need to have read access over the tables. A malicious security admin could collude with other users to obtain/retrieve sensitive information.
- Avoid type conversions in predicate functions to avoid run-time errors.
- Avoid recursion in predicate functions, as this will affect the performance.
- Be cautious in using large joins in predicate as it will affect the query performance.
- Avoid predicate which depends on the session properties like implicit date conversions etc.
- It is possible to derive some sensitive information using carefully crafted brute force queries. For instance, a user could divide the salary column by randomized number to derive at another particular user’s salary, because the runtime would throw a divide by zero exception before it could check the predicates. So, it is advisable to enable audit log on the database to monitors such accesses.
With this, we have reviewed Row Level Security features in Azure SQL Server. In the next blog, we will visit the Dynamic Data Masking concept as a continuation of the Azure SQL Server Security recommendations.
Click here to take a look at our Key Microsoft Analytics Offerings.