In part one, we covered one of three important new security features in SQL 2016, Dynamic Data Masking. In part two, we covered Always Encrypted. For the final part of this series, we look at the new feature, Row Level Security, or RLS.
Business data protection remains a hot topic as news of data leaks and breaches continue to make headlines.
KTL Solutions’s developers work to stay ahead of the curve by exploring new security features in SQL to incorporate into our upgrade deployments, as well as software customization to help customers further secure their data.
Row Level Security allows for programmatic customization to determine if a user can access or modify a row of data. This feature is extremely useful when it comes to situations where there are large tables of data that are subject to numerous rules regarding what users can access and/or modify.
Advantages of RLS
Traditionally, such restrictions were applied at the application level. Switching these restrictions to the database level offers a few important advantages:
- The coding of RLS access is centralized in one location. Applications coded to expect access restrictions should, in most cases, require minimal code changes in response to future access rule changes.
- The probability of security holes in access rules due to bugs in multiple and/or complex end user applications is greater than with a centralized approach, with far less program code.
- RLS covers access to the database outside of applications, such as database access by a user using SQL Server Management Studio and/or other database access tools.
It is important to note that the term “user” does not have to mean a SQL login account. In this case, “user” can be a custom user table for one or more applications that use a single encrypted SQL login to access the database.
RLS supports this by allowing the passing of an application user ID parameter in SQL session context, which can be applied after the application establishes a database connection. As a protective security measure, the application user ID can be applied as read-only so that it cannot be changed through any other means during the lifetime of the SQL connection.
Understanding the Two Security Predicates
RLS has two types of security predicates which can be added to tables.
- Filter Predicate – Silently filters rows available to read operations from SQL SELECT, UPDATE, or DELETE commands.
- Block Predicate – Blocks write operations that don’t meeting the predicate on SQL operations AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, and BEFORE DELETE.
To demonstrate, let’s assume there is a table of users for a sales department called SalesUser which has a flag indicating if the user is a manager. The goal is to only allow sales users access to rows in the Sales tables marked with their name, with the exception of managers who can view all rows. A simple filter predicate would look like this:
Note that predicate functions should be placed in a separate schema to keep them secure from regular users. This function returns the table only is the username passed in matches the current user, or has the IsManager flag set for their name in the SalesUser table.
The next step is to link the Sales table to the predicate by creating a security policy such as this:
This creates a policy named SalesFilter than uses the security predicate function on the SalesUser column of dbo.Sales with a state of being enabled. Once active, any attempts to query Sales will only return rows with the SalesUser column matching their username unless they happen to have a row in SalesUser with the IsManager bit set to 1, in which case queries will return all rows.
RLS Provides Better Options for Business Data Protection
The power of using RLS to tie security rules to SQL functions opens many possibilities for custom centralized security implementations. Like most security features, RLS does have some limitations, so it’s important to carefully review and plan any design. More information on the Row Level Security feature and its applications and limitations is available here.
If you need additional support, please feel free to reach out to the team here at KTL Solutions. Our expert staff is here to help you and your business succeed.
