I am trying to implement encryption in sql server 2005 through Certificate and Symmetric Key
and i came to know that the application user should have the following access in order to Encrypt and Decrypt Data
1) CONTROL permission on Certificate and
2) REFERENCES on the Symmetric Key
(Let me know if i am wrong)
Now my concern is what does CONTROL mean in the context of Certificate?
If my User1 has Control permission on my certificate Cert1
What all can he do, Is there a way to restrict him further, but user1 still be able to Encrypt\Decrypt the data
I could not find any good practice doc for certificate and key management so can some one advice the good practice for this
Thanks,
Ram
Best Answer
From BOL: http://msdn.microsoft.com/en-us/library/ms191291.aspx
Control - Confers ownership-like capabilities on the grantee. The grantee effectively has all defined permissions on the securable. A principal that has been granted CONTROL can also grant permissions on the securable. Because the SQL Server security model is hierarchical, CONTROL at a particular scope implicitly includes CONTROL on all the securables under that scope. For example, CONTROL on a database implies all permissions on the database, all permissions on all assemblies in the database, all permissions on all schemas in the database, and all permissions on objects within all schemas within the database.
Essentially, when you grant control, you are giving the user full access to the certificate. You may want to check out the following links for more info on sql encryption... the SQL Server Central article is really well done and is probably your best bet for a guide for how to do this.
http://technet.microsoft.com/en-us/library/ms189586%28SQL.90%29.aspx
http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/sql2005symmetricencryption/2291/
http://www.mssqltips.com/tip.asp?tip=1319