Non-priviledge way to restrict UPD/DEL to user knowing certificate pass

Question

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?

Answer 1

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).