SQL Server 2016 is the latest version of the data platform from Microsoft (currently in public preview). Per Microsoft, “It is the biggest leap forward in Microsoft’s data platform history with real-time operational analytics, rich visualizations on mobile devices, built-in advanced analytics, new advanced security technology, and new hybrid cloud scenarios.”
There’s a ton of ground to cover on all of the new enhancements, so this blog post will focus on a few of the new advanced security offerings that Microsoft has incorporated into the platform, including Dynamic Data Masking, Row-Level Security, and Always Encrypted.
Dynamic Data Masking
Data masking is obscuring data, such as a SSN, in order to render only a portion of a record to certain users, i.e. “XXX-XX-1234” instead of “123-12-1234”. It is not security per se, as authorization is maintained apart from the masking function.
Data masking is a common feature implemented in the application tier, however, centralizing the implementation within the data source itself is a great value-add. Having data masking rules maintained within the database engine eliminates the need for redundant implementation across multiple apps and mitigates the risk of a missed (or inconsistent) implementation.
Turning on this feature in SQL 2016 only requires a single DML statement. For example, to implement SSN masking to show only the last four numbers, one would execute a statement against an existing table column:
ALTER COLUMN [Social Security Number] ADD MASKED WITH (FUNCTION = ‘partial(0,”XXX-XX-“,4)’)
Dynamic data masking provides for four different built in masking functions (default, Email, Custom String, and Random) to meet virtually any set of requirements (see MSDN for details).
Row-Level Security (RLS) is a much anticipated feature of SQL Server 2016. RLS allows for authorization control to be maintained based on any custom attribute and applied down to the row level. Unlike dynamic data masking, it is a true security feature as it controls the ability to select, update, or delete the data itself. Per Microsoft, below are sample use cases for RLS:
- A hospital can create a security policy that allows nurses to view data rows for their own patients only.
- A bank can create a policy to restrict access to rows of financial data based on the employee’s business division, or based on the employee’s role within the company.
- A multi-tenant application can create a policy to enforce a logical separation of each tenant’s data rows from every other tenant’s rows. Efficiencies are achieved by the storage of data for many tenants in a single table. Of course, each tenant can see only its data rows.
RLS implementation requires a few steps; however, it is entirely managed by the database engine. It requires the creation of at least* three database objects:
- An inline table valued function (to define the filter logic)
- A security predicate (to apply the filter function to database object(s))
- A security policy (to contain and implement the security predicate(s))
*A recommended best practice is to create a separate database schema to contain the RLS objects.
Below is a sample select filter implementation based on Adventure-works (database file available here):
The implementation of a select filter has no impact to the calling application. One could implement a multi-tenant application with identical SQL statements being used for all tenants and have all-access control enforced across the same database objects based simply on an identifying attribute of each tenant’s credentials (either SQL or windows).
Block predicates for updating and deleting data can be applied in much the same way, however, attempts to access data protected by a block predicate does return an error to the calling application. Additional logic is needed in the app tier when implementing update and delete predicates to handle these potential error states.
One of the most important new advances in security rolling out with SQL Server 2016 is Always Encrypted. Microsoft has offered encryption built into SQL Server since 2005 (cell level encryption), expanding to Transparent Data Encryption (TDE) in SQL 2008. Cell level and TDE encrypt data at rest. Both are useful mechanisms for securing data where physical theft is a concern, however, threats from
network intrusion or use of stolen credentials are much more common now and encryption for data on disk alone is frankly just not good enough. Enter: Always Encrypted.
Always Encrypted relies on the database engine, client database driver, and encryption key store to implement end to end encryption for both the database and client application. Encryption and decryption occur entirely within the database driver of the client machine, ensuring that non-authorized users, machines, and administrators have no access to view sensitive data (credit card #s, PCI, etc). The separation of data management from data access and the requirement to store encryption keys outside of the local filesystem are best practices in security that are now base requirements in Always Encrypted.
There are different implementation scenarios for enabling Always Encrypted, dependent on cloud vs. on prem vs. hybrid infrastructure. It’s up to the solution designer to understand the database, application, and certificate management options. If the same person is admin to your application, database, and key vault, then they literally have all the keys. If so, does that person also hold all of the liability?
These new features along with all of the existing tools in SQL Server offer a lot of options in implementing a secure, performant, reliable, and flexible data platform. With all of the options available in the SQL Server Data Platform, it’s even more important to partner with a consultancy that specializes in the Microsoft stack and has a proven track record of client success. If you’re interested in learning more, please contact SCS with any and all of your questions.