SQL Server 2016, the next major release of Microsoft’s flagship database and analytics platform (which is available as a Community Technology Preview [CTP] as of this writing), includes exciting features such as StretchDB and AlwaysEncrypted. Here are two more in the works to think about: dynamic data masking and row-level security. In the case of these two features, they’ll be released first to the cloud platform (Azure SQL Database) and, later, to the on-premises version of SQL Server.
Dynamic Data Masking in SQL Server 2016
Dynamic data masking ensures that non-privileged users see only the data they are permitted to see while other data is masked. The big benefit is that, once designated, the data is revealed to the end users with minimal impact upon the application layer. No data in the database is altered. It’s simply obfuscated upon presentation to the client application.
Dynamic data masking has a few components: privileged logins, masking rules, and masking functions. Privileged logins are those logins that get unmasked data in their query result sets. Masking rules define which fields are masked and what functions are used to perform the masking. Masking functions are the algorithms that control how data is exposed to the end users. Azure SQL Database provides a variety of built-in masking functions for the most common scenarios, such as credit cards (such as XXXX-XXXX-XXXX-1234), Social Security numbers (such as XXX-XX-XX12), email (such as aXX@XXXX.com), and random-number generators, not to mention a customized text format which you can design.
You can set up the feature either via PowerShell cmdlets, via the REST API, or using the Azure SQL Database graphic user interface. It does not currently support Transact-SQL statements.
Read more about dynamic data masking at https://azure.microsoft.com/en-us/documentation/articles/sql-database-dynamic-data-masking-get-started.
Row-Level Security
Row-level security (RLS) is going to make the lives of application developers much easier! RLS enables you to access which rows in a database table can be accessed, depending on group membership of the end user or the execution context of their connection. Because all of the permissions logic is stored in the database, the security contexts are applied very reliably no matter how or at what tier an end user attempts to access the data.
RLS filter predicates are the equivalent to adding a WHERE clause to a DML statement. The predicate could be as simple as WHERE my_id = @absolute_nbr or a highly sophisticated set of considerations involved data in the table and/or metadata.
Implementing RLS is a multi-step process with a handful of best practices and constraints all its own. I strongly encourage you to read the documentation to get a good feel for the process. In a nutshell, RLS is implemented via the CREATE SECURITY POLICY Transact-SQL statement (online at https://msdn.microsoft.com/en-us/library/dn765135.aspx). But you may need to make changes to your tables, and explicitly declare the schema which will to store the RLS information, in addition to issuing the CREATE SECURITY POLICY statement for each type of security access you want an application to have.