0

I used Column Level Encryption to encrypt one of the columns in my SQL Server database. Now everytime I want to SELECT to see that encrypted data, I have to open the symmetric key to decrypt the column and then close it.

I want to a particular user or login to always view the encrypted data. Is it possible in Column Level Encryption, if yes, how?

0

Try the following command if you want specific user to access data on specific database

use [mydb]

GRANT VIEW Definition TO User1
  • Is it for the encrypted column data (using Column Level Encryption). Will granting this permission allow the user to pass the encrypted data and see the original data, even if the symmetric key is closed? – Shaurya S Apr 1 at 4:34
  • This will create encryption exception for specific user on a specfic database. so yes you can view the data anytime you want on that database without modifing encryption status over and over everytime you need to select something on that database – Ahmad Abuhasna Apr 2 at 6:12
  • I tried it, it wont work if the symmetric key is closed. – Shaurya S Apr 3 at 4:20
0

There is no out of the box way to automatically open a key for a particular user. You could create a logon trigger that opens the symmetric key. Something like this:

CREATE TRIGGER open_sec_key
ON ALL SERVER
FOR LOGON  
AS  
BEGIN  
  IF ORIGINAL_LOGIN() = '<user>'
   OPEN SYMMETRIC KEY [key_name] DECRYPTION BY CERTIFICATE [cert_name];
END;

Logon Triggers.

  • As per the login triggered, the symmetric key will be opened. After it has been open, I believe anyone can SELECT and see that data? And do we have to close it manually everytime the symmetric key is opened through the trigger? – Shaurya S Apr 1 at 4:38
  • As per Docs the symmetric key is opened only for that session and remains open until the key is closed or the session terminated. I've tested this and a second session that hasn't opened the key can't see the data. – HandyD Apr 1 at 5:54

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.