June 12, 2008 at 7:04 am
Dear all,
I try SQL 2005 Data Encryption :
(1)CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd'
(2)CREATE CERTIFICATE MyCert WITH SUBJECT = 'My DB Access', START_DATE = '01/01/2007', EXPIRY_DATE = '10/31/2010'
(3)CREATE SYMMETRIC KEY MySymKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyCert
(4)OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert
(5)UPDATE dbo.Employees SET emppassword = EncryptByKey(KEY_GUID('MySymKey'),emppassword)
But everybodies can type the following commands to decrypt data, where is the protection ?
(6)OPEN SYMMETRIC KEY MySymKey DECRYPTION BY CERTIFICATE MyCert
(7)SELECT Convert(VarChar,DecryptByKey(emppassword)) FROM table1
June 12, 2008 at 7:25 am
The caller must have some permission on the key, and must not have been denied VIEW permission on the key. Additional requirements vary, depending on the decryption mechanism:
DECRYPTION BY CERTIFICATE: CONTROL permission on the certificate and knowledge of the password that encrypts its private key.
DECRYPTION BY ASYMMETRIC KEY: CONTROL permission on the asymmetric key and knowledge of the password that encrypts its private key.
DECRYPTION BY PASSWORD: knowledge of one of the passwords that is used to encrypt the symmetric key.
Regards,
T.
June 12, 2008 at 7:39 am
The idea behind using encryption is one of the following:
- The data is being accessed by a service account from an application. Therefore, the service account has access to the key but the regular end user does not.
- The data is being access through a stored procedure which has an EXECUTE AS clause. The user the stored procedure is executing under has access to the key but the regular end user does not.
- Each user has his/her own key and the data is encrypted based on the user's key. This prevents Susan from seeing John's data.
In the scenario you've given, either the first or second option would be appropriate.
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply