We will be discussing some of the SQL Server security features available in the Azure Database/Data warehouse in this blog series. Security is a key issue while people are actively putting their data in the cloud. In this blog, we will review how Row-Level security features can be used to enhance security in Azure.
Row-Level Security (RLS)
Very often, we come across situations where users from multiple departments within the organization use the same employee table. So, any user within the organization could access your personal contact information. To avoid such cases, we could use Row Level Security (RLS) to control access to rows in a table to a specific group.
RLS allows you to implement fine-grain control over your data. Also, this restriction is applied at the database level using inline functions, so, these restrictions will be applied to any data-access on those tables. Hence RLS acts as a centralized security logic.
RLS Supports two kinds of security predicates
- Filter predicate: It silently filters records while reading. It is applicable to SELECT, UPDATE and DELETE operations.
- Block predicate: They explicitly block DML operations on records which violates the predicate. It is applicable to INSERT, UPDATE and DELETE.
In this blog, we will be covering only Filter predicate. Click here to read about Block Predicate.
Since the filter predicate is applied implicitly, the user/application is usually unaware of the filtered rows. The user/application will be allowed to make inserts even if it violates the filter predicate. Filter predicate is applicable in SQL server 2016 (from 13.x), Azure SQL DB and Azure SQL Data Warehouse. In fact, RLS filter predicate could be applied even to external tables in Azure SQL DW.
Here I have created an employee table, with the following records:
And a department table with the following records:
Since the employee table has personal information like the phone number, we are going to create a security protocol over this table. Security protocol will be as such that the employee can only read only their record and the respective department heads will be able to read all records of the employees assigned to their department.
Firstly, we will create a security function which takes ‘EmpName’ and ‘DeptId’ as parameters. This function will restrict access by either ‘EmpName’ or ‘DeptName’ by equating them with a username. Since we are applying the predicate over a foreign key, we have used subqueries to implement this.
Now we add to this function, a security protocol over the Employee table. We can turn off the security protocol by altering the state to ‘OFF’.
Now, we have a filter predicate applied on the Employee table. Even if you select as an admin user, you will get an empty result set. To test the filtering, we created two users ‘harry’ and ‘fly’. When selected with ‘harry’ as the user, we get only the employee record for ‘harry’ and while querying with the user ‘fly’, we get the record for ‘hermione’ as she is the only user in the ‘flying’ department.
That’s all for this blog. In the next blog, we will be visiting block predicate, best practices, and vulnerabilities of RLS.
Click here to take a look at our Microsoft Azure Offerings.