I am assigned to researching column-level encryption, with the additional requirement of the sa not being able to read the encryted column(s).

The idea as I've read in this DBSE post is to create a password-protected certificate, a bunch of symmetric keys under it, and the client should open these key(s) with the password only they are aware.

This seems to ensure that indeed noone other than the client can read the encrypted data. However, they can still modify them (delete them or update=corrupt them). As I understand it, using ENC/DECRYPTBYKEY is just a scalar function, independent of tables. That means that whether someone uses these functions on a specific column does not "inform" the database that these columns hold encrypted data. Thus, the only way to "protect" the data is the old school priviledge one.

Have I missed something? Is there a way to restrict a column to only accept updates or deletes if a specific key is open? Maybe even, in the case of update, require that the inserted. value is always the output of the EncryptByKey function?

  • Is Always Encrypted an option? It's also column level encryption but in a much more secure manner that does stop any sysadmins (assuming proper setup) from reading the data: docs.microsoft.com/en-us/sql/relational-databases/security/… – Sean Gallardy - Retired User Mar 19 '19 at 10:27
  • Ah, I forgot to mention that. Well, it is kind of seperate research, so I'm not looking into it. But even in this case, wouldn't then the dba be able to indiscriminately upd/del the columns in question? – George Menoutis Mar 19 '19 at 10:40
  • Could they physically update the column? Yes. Would the column have any data that made sense? No, so you'd know that someone touched a row of data because the value would be corrupt. There is no way to stop a sysadmin from updating or deleting a column/row of data, period. – Sean Gallardy - Retired User Mar 19 '19 at 11:12

Is there a way to restrict a column to only accept updates or deletes if a specific key is open?

No, a sysadmin or someone with sufficient permissions could change the data regardless of encryption.

Should the question or investigation change to be something where it doesn't matter if the data could change but that it was encrypted or otherwise unavailable to a sysadmin or someone with sufficient permissions, then yes that is possible but would require the keys to be stored outside the database system or for secret passwords to be known (less secure).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.