Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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

share|improve this answer

CONTROL gives permission to do whatever to the certificate. This includes dropping the certificate. Unfortunately, there's no way to restrict the user any further if the user has CONTROL permissions. It's the unfortunate nature of the beast.

With that said, you can create a stored procedure which runs under the context of another user account (such as dbo) which has CONTROL on the certificate in order to use it to open the symmetric key used to encrypt and decrypt the data. The user can have permission to execute the stored procedure. In this way the user can use the certificate, but only through the stored procedure. And therefore you completely control how such access is done.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.