CODEWITHSUNDEEP
cloudsql-server-2016
123_xyz
123_xyz

Reputation: 13

Row-wise encryption SQL Server 2016

Is it possible to use the Always Encrypted feature, with the row level security feature in SQL Server 2016? And somehow encrypt the rows with different EncryptionKeys with respect to the rows that are to be viewed by a particular user?

Upvotes: 1

Views: 698

Answers (1)

SQLmojoe
SQLmojoe

Reputation: 2053

That helps. Short answer, can't do it easily today with AE & RLS.

AE is currently column scoped; you encrypt an entire column with a specific column encryption key (CEK). You can encrypt multiple columns in a table, each with its own CEK but it's still column scoped. You can't logically group rows and encrypt them with their own keys with the current AE implementation. If this is really important to you, you can submit a suggestion to the team via https://connect.microsoft.com/SQLServer/feedback/

That said, apart from meeting some regulatory or corporate policy requirement, you don't gain much from your current proposed design. Unless you have a very small number of users (or groups of users) hence a very small number of groups of rows, you'll quickly run into CEK proliferation. Not a hard thing to manage but still tedious and potentially a lot of work. For example, each user or group of users need their own keys or secret to access the key pushed to their app or workstation. Complexity and/or lots of moving parts means more risks of failure. Additionally, it can be extra "fun" during audit season (depending on your auditor of course).

If I may restate your goals, you want to ensure 1. data is always secure from unauthorized eyes 2. authorized users are only allow to see data they're authorized to see

Implemented correctly, AE meets #1 requirements. Without the key, all you see is ciphertext. Sure, you can brute force it or find flaws in the encryption algorithm or SQL Server's implementation but there are FAR, FAR easier ways than what Hollywood wants us to believe.

RLS does address #2 under most circumstances. The implementation is at the query processor level so circumventing RLS is extremely difficult; there has to be a bug in RLS for that to be possible. If your RLS filter is based on a non-encrypted column (and it really should be), then there's no chance someone could even sniff your secrets from memory/network unless your client is compromised.

So both requirements are met even though you only have 1 CEK. This makes your life a lot easier in the long term with a smaller surface area to manage. This almost always results in a more secure environment. Having separate keys buys you an added layer which is great on paper for defense-in-depth but in practice, your efforts will yield a lot more in other areas (e.g. alerting, user education, etc...).

Upvotes: 1

Related Questions