Separating encrypt and decrypt privilidges in SQL Server

I am trying to save sensitive information on DB with encryption and all users can put their information in DB after encrypting it, but only privilidged users can decrypt and see it. How can i achieve it using开发者_JS百科 SQL Server 2008 key/certificate/encryption mechanism?

Thanks, Ebe.

Maybe use an EXECUTE AS clause in your insert statement/procedure to do the encryption.

Or you could create a view to decrypt the data and only grant access to your read role. I'm not sure if that stops the insert users from being able to decrypt through another method though.

You can't, and shouldn't, hide the encrypted information from the user that actually saved it. What is a more realistic scenario is that ordinary users can encrypt and decrypt their own data, while priviledged users can decrypt everybody's data.

To achieve this you do the following:

• data is encrypted with a symmetric key (that's always the case)
• each user uses a different symmetric key
• the symmetric keys are encrypted by a certificate belonging to the user, this certificate is encrypted by a password
• when using the application users enter their certificate decryption password and the application opens the certificate in their session, thus granting access to decrypt the symmetric key and thus allowing the session to encrypt new data and decrypt it's own data
• the important part: all symmetric keys are also encrypted with a single 'admin' certificate, and this 'admin' certificate is encrypted with a password
• only priviledged users know the admin certificate password
• when administrators use the application they enter the admin certificate password and thus gain access to all symmetric keys, and are able to decrypt all the data

The big problem, you'll find out, is provisioning the symmetric key, as it has to be encrypted by both the user certificate and by the admin certificate. That means that either the admins know all users's certificates passwords (and that usually doesn't hold water for what I hope are obvious reasons), or your application has to implement some pretty complex procedure to deploy the symmetric keys. Getting that procedure right is incredibly complicated. It helps tremendously if you have an escrow key (a very highly priviledged key, usualy stored in hardware, to facilitate the symmetric key provisioning) because the system can use the escrow key to open the symmetric key and add both the user and the 'admin' certificate encryption to the key.

This scenarios allows separation of access to the data by cryptographic means, and as you see is extreamly complex.

The alternative is to separate the access by authorization means. This simply implies that the access to the keys used is governed by GRANT/DENY/REVOKE access control lists and users can encrypt and decrypt data based on the access to the keys, but the keys are decrypted for everyone (eg. they are encrypted with database master key and service master key). But this is much, much weaker than a cryptographic separation. You can achieve exactly the same effect by using Transparent Database Encryption and controlling data access via GDRs, for a fraction of the complexity.

And finally there is the path you shouldn't take: embed passwords in the app. Eg. have stored procedures that open keys, save the data, then close keys, with certificate access passwords embeded in them, and then encrypt the procedure. This will give you just a grand delusion of security and that is all I'll say about it.