Back with more from SQL Server 2016. Today is the turn of new security features: Always Encrypted, Row Level Security and Dynamic Data Masking. Let’s dive into these right away!
Always Encrypted
First and foremost Always encrypted is NOT a solution for doing entire database encryption (such as TDE) but column level encryption. Consider this technology to encrypt the most critical pieces of your database (certainly not that auto-generated primary key or something that may not be confidential such as a product name or stock amount in an inventory database, for example).
It doesn’t to look very well polished. Let’s see some of its limitations
Something very simple as sorting rows based on a column using Always Encrypted will not be possible (only at an application level) because SQL Server will sort based on the encrypted value rather than the decrypted value, which is totally useless in 99% of the cases.
You can search for columns using Always Encrypted only if you use equality operations (how a released product sees the light of day with such restrictions escapes my comprehension). And that is only possible if your encryption type (one of the many settings you should configure during Always Encrypted initial setup) is DETERMINISTIC. With other encryption types not even that limited ability to search using equality is possible. In other words, use RANDOMIZED encryption type and that column is no longer good for searching.
If you using RANDOMIZED encryption types (which by the way according to some documentation is more secure than DETERMINISTIC) then forget about indexes (they are simply not supported)
Dynamic SQL apparently doesn’t play nicely with Always Encrypted, you need to change your code to use parametrization.
There is a laundry list of column types that are not supported, I’m not going to include them here but this nice article by Aaron Bertrand contains the nitty-gritty details: http://blogs.sqlsentry.com/aaronbertrand/t-sql-tuesday-69-always-encrypted-limitations/
If all you have seen so far have not discourage you from still playing around with Always Encrypted then as far as syntax goes, you need to add the ENCRYPTED WITH clause to the CREATE TABLE statement. It will look something like this:
CREATE TABLE [dbo].[Customers](
[CustomerId] [int] IDENTITY(1,1),
[TaxId] [varchar](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
COLUMN_ENCRYPTION_KEY = MyColumnKey) NOT NULL,
[FirstName] [nvarchar](50) NULL,
....Three parameters are used within the ENCRYPTED WITH syntax for the CREATE TABLE statement.
The first of these is the ENCRYPTION_TYPE parameter, which accepts a value of RANDOMIZED or DETERMINISTIC. The second is the ALGORITHM parameter, which only accepts a value of AEAD_AES_256_CBC_HMAC_SHA_256. The obvious thing to think about when you see a parameter accepting only a single value is that Microsoft will eventually attempt to provide more encryption algorithms. I have to admit that, in trying to understand what AEAD_AES_256_CBC_HMAC_SHA_256 does I started reading the Always Encrypted Cryptography official documentation from Microsoft (you can find it at https://msdn.microsoft.com/en-us/library/mt653971.aspx) detailing each and every step of how the plain text becomes cypher text using this algorithm but I abandoned half-way for fear of falling asleep.
The third parameter is the COLUMN_ENCRYPTION_KEY, which is the encryption key you use to encrypt the value.
Migration
Another awful aspect of Always Encrypted is the lack of support for pain-free migration: Because of the design of Always Encrypted, data is encrypted only by applications using the ADO.NET driver with parameterized queries. This design prevents you from using SSMS to move data into the new table.
What does this mean? It means you have to create your own .NET application to encrypt the data and move it to a new table (yes, I’m not kidding). The new table is a copy of the old one (same schema) but with the encryption enabled for those columns you want to use Always Encrypted.
The approach for migration, according to documentation is this
Build a new staging table.
Write a .NET application using ADO.NET to process the encryption of both existing and updated rows.
Run the .NET application built in the prior step.
Drop the existing table and rename the new table to use the old table name.
Change the application’s connection string to include Column Encryption Setting=enabled (this effectively lets the application know that from now one it should use Always Encrypted).
It is fair to admit that not all this information and limitations belongs to the final product. Some of this shortfalls have been experienced in the CTP 2.2 build so there is the slight chance you may not find all this is true in the final release. This is even moreso true if in the future Microsoft happens to release service packs or improvements to the initial release so my advise is to try that by yourself in whatever release you are working with.
Row Level Security (RLS)
This looks a bit more promising. Row-Level Security (RLS) allows you to configure tables such that users see only the rows within the table to which you grant them access. This feature limits which rows are returned to the user, regardless of which application they are using, by automatically applying a predicate to the query. In this context, a predicate is table function that identifies the rows accessible to users. Before you go nuts with a super complicated function, bear in mind this function will execute every time the user attempts to access a row in a table (EVERY time) so be extra careful that your function works as fast as possible.
If this sounds a bit too much like triggers… well… I don’t what to say… it is not THAT different (you are trying to achieve the same goal, only using a different way to get there).
Now, back with RLS. After creating inline table-valued functions, you next bind them to the table that you want to secure. To do this, use the CREATE SECURITY POLICY command.
What I don’t like is that, by default the FILTER PREDICATE security policy affects “get” (SELECT) operations only. Users are still able to insert rows that they cannot subsequently query. They can also update rows they can currently access and even change the rows to store values that block further access. You can overcome this limitation by using BLOCK PREDICATEs, but the syntax get more and more complex. So to recap, the functionality is there, it is just that the implementation is unnecessarily too complex.
Just as with Always Encrypted, there are nitty-gritty details. For example: a table will not support 2 predicates for the same operation (like BEFORE UPDATE) or Filter predicates are not compatible with Partitioned Views. These are just two of the dozens more details of when you can use or not this technology and enumerating them in this review will really not make it more dynamic and interesting to read (especially if you just want to get a bird’s eye view of the new functionalities) so enough is to say that you can find those details (with some scenarios including complete T-SQL to see all these in action) here: https://msdn.microsoft.com/en-us/library/dn765131.aspx
One more thing, as I was playing around with this feature I captured a video so if you don’t have SQL Server 2016 but you still want to see it being used in Microsoft SQL Server Management Studio see this video:
Dynamic Data Masking
From all of the security related features, this one was the only one that did not disappointed. Not only dynamic data masking is super easy to implement, it provides with a declarative syntax to implement something that in the past would have taken much more T-SQL to write, more time to implement and way more time to maintain and administer.
Here is how it works: when you have a database that contains sensitive data, you can use dynamic data masking to obfuscate a portion of the data unless you specifically authorize a user to view the unmasked data. To mask data, you can use one of the following four masking functions to control how users see the data returned by a query:
Default Use this function to fully mask values by returning a value of XXXX (or fewer Xs if a column length is less than 4 characters) for string data types, 0 for numeric and binary data types, and 01.01.2000 00:00:00.0000000 for date and time data types.
Email Use this function to partially mask email addresses like this: aXXX@XXXX.com. This pattern masks not only the email address but also the length of the email address.
Partial Use this function to partially mask values by using a custom definition requiring three parameters as described in the following table: – Prefix: Number of starting characters to display – Padding: Value used for the masking partially – Suffix: Number of ending characters to display
To configure dynamic data masking for a new table, use the CREATE TABLE statement with the MASKED WITH argument.
When you use dynamic data masking, the permissions that you assign to users affect whether users see plain text values or masked values. Specifically, members of the db_owner fixed database role always see plain text values, whereas users who are not members of this role see masked data by default.
If you need to grant a user permission to see plain text data in a table, you must grant the new UNMASK permission at the database level. To do this, use the GRANT UNMASK statement in the database containing the masked values.
One thing I liked about this is that data-masking permissions and configuration survive when you copy data from one object to another. For example, if you copy data from a user table to a temporary table, the data remains masked in the temporary table.
I was so interested in this aspect of data security that I decided to give it a whirl myself, this is the T-SQL script for an end to end test using AdventureWorks database.
-- First: Change the Person.Person table to use data masking on the
-- First Name column
ALTER TABLE Person.Person
ALTER COLUMN FirstName ADD MASKED WITH (FUNCTION='default()')
GO
-- Second: Create a Login and associated user in AdventureWorks
CREATE LOGIN MyUser WITH PASSWORD = 'abc123!@#'
GO
CREATE USER MyUser FOR LOGIN MyUser
GO
sp_addrolemember 'db_datareader', 'MyUser'
GO
-- Thrid: See how the table looks like from that user’s perspective
EXECUTE AS USER='MyUser'
select *
from Person.Person
go
REVERT
-- Fourth: Give the user permissions to see the unmasked data
GRANT UNMASK TO MyUser
-- Fifth: Repeat step four, now with the additional permission to
-- see the unmasked data
EXECUTE AS USER='MyUser'
select *
from Person.Person
REVERT
-- Sixth: Clean Up!
DROP user MyUser
GO
DROP LOGIN MyUser
GO
ALTER TABLE Person.Person
COLUMN FirstName DROP MASKEDThis is how it looks, notice the first names are masked in the first select , but unmasked on the second one
Hope you had fun reading all about these new security features in SQL Server 2016 (and you feel a little wiser). I know I had fun researching them.
See you soon. Cheers!
Comments