SQL Server: how to limit access to encrypted colum

问题:

I'm new to database encryption. We have a requirement to encrypt some columns in various tables which show sensitive information. The problem we have is, the data should not be seen by anyone with access to the database or its certificate and symmetric keys (not even dbas), and only some users of the application which query the data in the database should see the information.

So we have come up with this:

• 1 symmetric key created for each table with encrypted columns

• all the keys protected by a certificate, and stored procedures accessing these certificates are now defined with:

  OPEN SYMMETRIC KEY Key_SalaryTable 
  DECRYPTION BY CERTIFICATE SystemCertificate 
  WITH PASSWORD = 'password';
  

(which means the password for the certificate which is used to open the Key_SalaryTable is hardcoded into the stored procedure)

But there is a problem, any database administrator can just view the stored procedure or call the stored procedure directly to know how to open the key to view the salary table

Even if we restrict each user's access to the stored procedure, a dba can easily override that and extract information before setting the restrictions back again and no one will know the information has been compromised.

Does anyone have a better solution?

回答1:

The alternative is to execute the OPEN SYMMETRIC KEY / CLOSE SYMMETRIC KEY commands from the application itself not in the stored procedure. BTW, the KEY remains open on the session until its closed or the session terminates. Make sure the connection between your application and the db is encrypted "on the wire".

On the ASP.NET side you can keep the password on an encrypted web.config section.

回答2:

You can pass the Password from the application as parameter to the stored procedure,
Password as parameter is possible only by using dynamic query

--Open the Symmetric key

DECLARE @open nvarchar(200)

SET @open = 'OPEN SYMMETRIC KEY Key_SalaryTable DECRYPTION BY CERTIFICATE SystemCertificate WITH PASSWORD =''' + @password + ''''
EXEC sp_executesql @open

-- Queries

--Close the Symmetric key
CLOSE SYMMETRIC KEY Key_SalaryTable