I recently presented a free webinar for Pragmatic Works where I discussed new security features in SQL Server 2016. You can click here to watch the recording of the webinar if you missed it. In this follow up I wanted to post the scripts I used and answer a few questions from the webinar along the way.
Q: Can I use a WHERE clause on a masked column? For instance, SELECT * FROM Table WHERE SSN LIKE ‘%789’?
A: Yes. The data lives on disk in non-masked format and only the result is masked upon being returned to the user.
Q: Do we still need to implement RLS inside SSAS cubes?
A: If the cube/model uses a cached mode then yes, you will still need to build the RLS into the SSAS layer. I see customers being able to use the same data structures to support both RLS in the database engine and RLS in the SSAS engine though. The same, or very similar, pieces need to be in place to support both.
Q: Is there a difference in masking detail vs. aggregation scenarios?
No. Just know that if you do an aggregation on a masked column and you do not have unmask permissions the result will always return as 0.
Q: Can I join on a masked value to an unmasked value in order to determine the actual data in the field?
A: Yes. Join and WHERE will still function. So you would run a series of WHERE statements or create a table with a set of possible values and join to the table in order to determine the underlying data.
Q: Brad is my hero. I strive to achieve his level of excellence someday, though I’m sure I will never be able to achieve it. For that reason, I am a sad panda.
A: Not really a question, but I can see your point. We all need life goals though, so I’m glad I can be the bar to which you will never reach. I kid of course. I know the guy that posted the “question” and he is a rock star when it comes to big data solutions. Go check him out: http://joshluedeman.com/
Q: What if they made pizza flavored coffee?
A: That sounds like a waste of perfectly delicious pizza flavor. Also, no, I would not drink that.
Q: When you say database owners will always see unmasked data do you mean the actual database owner or anyone that is db_owner?
A: Both will be able to see all the data unmasked at all time.
Q: What is the performance overhead on masking?
A: I have noticed no to minimal impact. It’s going to be CPU that is hit for the operation and will depend on the number of masked columns, masking function and the number of records returned.
Q: What is the performance impact of Always Encrypted?
A: There is will be some network overhead due to the data being passed encrypted as a varbinary value. Otherwise the impact is really on the client application server since that is where the decryption is actually occurring, not on the SQL Server. This is mainly a CPU bound operation.
Q: Can the DBA get access to the unencrypted data?
A: Unlike data masking, the SA/Owners do not get unencrypted data. The user accessing the data must have the necessary certificate to decrypt data in order to see the actual values. I get a lot of feedback from DBAs saying they need to be able to access to actual data and a smaller number that say they like the current functionality. If you have opinions on this please leave me a comment and I would love to talk with you about your thoughts so I can pass them on to the product team. This is certainly a place where there have been a lot of feelings expressed both from the maintenance and security sides.
I cannot take credit for the scripts and demos for everything that I used. As mentioned in the webinar a portion of the content was derived from a blog post that Jamey Johnston did and can be found here: http://blog.jameyjohnston.com/oil-gas-sql-server-security-demo/ He did an awesome job creating a end to end demo of masking, RLS and Always Encrypted along with a pretty cool data set. My session wasn’t solely objects from his blog post but I did use some pieces and want to make sure he gets credit for the work!
Here is a link to the scripts/deck from my presentation: http://bradleyschacht.com/new-security-features-in-sql-server-2016/
Here is a link to the recording of the session (free to view): http://pragmaticworks.com/Training/Details/New-Security-Features-in-SQL-Server-2016
