SQL Server:如何限制对加密列的访问，即使是来自 dba? [英] SQL Server: how to limit access to encrypted column, even from dba?

问题描述

我是数据库加密的新手.我们需要对显示敏感信息的各种表中的某些列进行加密.我们遇到的问题是，任何有权访问数据库或其证书和对称密钥(甚至 dbas)的人都不应该看到数据，只有查询数据库中数据的应用程序的一些用户才能看到这些信息.

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.

所以我们想出了这个:

• 为每个带有加密列的表创建 1 个对称密钥

• 受证书保护的所有密钥以及访问这些证书的存储过程现在都定义为:

• 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';

(这意味着用于打开Key_SalaryTable 的证书的密码被硬编码到存储过程中)

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

但是有一个问题，任何一个数据库管理员只要查看存储过程或者直接调用存储过程就可以知道如何打开查看salary表的key

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

即使我们限制每个用户对存储过程的访问，dba 也可以轻松地覆盖它并在再次设置限制之前提取信息，并且没有人知道信息已被泄露.

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?

推荐答案

另一种方法是从应用程序本身执行OPEN SYMMETRIC KEY/CLOSE SYMMETRIC KEY 命令而不是在存储过程中.顺便说一句，KEY 在会话中保持打开状态，直到其关闭或会话终止.确保您的应用程序和数据库之间的连接是加密的在线".

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".

在 ASP.NET 端，您可以将密码保存在加密的 web.config 部分.

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

这篇关于SQL Server:如何限制对加密列的访问，即使是来自 dba?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！