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?