SQL SERVER SYMMETRIC KEY SECURITY ISSUE

I've got a DB in which i need to store the encrypted values for a column. I use the SQL Server encryption format for encryption. The format is as follows:

CREATE MASTER KEY ENCRYPTION BY PASSWORD 'dsagfdsagv418515adsf' CREATE CERTIFICATE 'CERTIFICATE_NAME' CREATE SYMMETRIC KEY 'KEY_NAME' WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE 'CERTIFICATE_NAME';

Now I am able to encrypt and decrypt the column, using EncryptByKey and DecryptByKey functions resp.

Now I see a security threat here in a way, that the SYMMETRIC KEY is visible to DBA and other DB Users. They can always decrypt that column using the key anytime they want. This is not feasable in my application. Can someone please suggest what can be done to safeguard this key?

May 28th, 2015 4:44am

USE encrypt_test;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID 
SELECT statement

 -- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO

Accessing the Encrypted Data

All the read access users will see the encrypted values while they do a select on table. A user need to have permission to symmetric key and certificate to decrypt data, if they still try to decrypt then they will receive null for encrypted values. However they do not receive any errors. In the below sample code I am running select in context of a user 'test' which has only read access on DB.

Execute as user='DBA'
GO
SELECT statement

 Grant Permissions to the Encrypted Data

Permissions can be granted to a set of users to decrypt and read data using the commands below.

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO DBA; 
GO
GRANT VIEW DEFINITION ON Certificate::Certificate1 TO DBA;
GO    

Refer

 
Free Windows Admin Tool Kit Click here and download it now
May 28th, 2015 8:53pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics