如何控制什么用户可以解密SQL Server对称密钥加密 [英] How to control what users can decrypt SQL Server Symmetric Key Encryption

问题描述

我正在研究加密SQL Server中的一些敏感数据，例如银行帐号和社会保障号码，以符合新的州法律。我使用SQL Server 2008作为我的数据库与.NET代码。我已经使用.NET来加密密码，但是对于这一点，我正在考虑使用微软的内置加密技术，只需使用简单的对称密钥加密即可加密我需要的几列数据。如果我去SQL Server加密，我可以从外部报告工具解密数据，而不仅仅是我的.NET应用程序。以下是我使用的示例：
http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and它使用由SQL Server创建的证书，然后使用DecryptByKey函数对数据进行解密，但我正在努力确定这是多么安全？只要他们打开对称密钥并使用解密功能，我如何控制用户可以解密数据的任何人呢？

解决方案

您有两种选择：

1. 加密控制。只有知道密码的用户才能解密数据。缺点是用户必须在访问数据时输入解密密码。报告必须包含一个Password参数，运行报表的用户将填写数据访问密码。应用程序必须从用户请求密码。网站必须向访客请求密码。等等等等




2. 访问控制。数据使用SQL Server本身可以访问的密钥进行加密（最终，ecnryption链一直到服务主密钥，并且使用DPAPI加密）。这给你没有更多的保护，其他授予和拒绝SELECT会给你的是：访问控制，而不是加密控制。这样一个方案完全可以防止意外丢失媒体（有人发现你的数据库有磁盘，或者你的数据库丢失了笔记本电脑）。您可以使用透明数据加密或文件级加密（ BitLocker ）。

通用数据加密方案是用对称密钥加密数据，然后用非对称密钥（通常是证书的私钥）加密对称密钥。然后使用密码对非对称密钥进行加密，并且尝试访问数据时必须显示该密码。这种两级间接的主要原因是密码更改：当密码或私钥被破坏时，对称密钥用不同的非对称密钥重新加密，或者使用不同的密码重新加密非对称密钥。这样，访问密码已经改变了，而不需要重新加密所有的数据。如果访问权限将被直接授予对称密钥，则密码泄密可能需要重新加密所有可能数TB数据的数据。

我提出的两种情况的不同之处在于是否使用数据库主密钥对非对称密钥进行了加密。情况1）不是，情况2）它是。这一切都在加密层次结构中解释。

I am looking into encrypting some sensitive data in SQL Server, such as bank account numbers and social security numbers in order to comply with new state laws. I am using SQL Server 2008 as my database with .NET code. I have used .NET to encrypt passwords, but for this I'm thinking of using Microsoft's built in encryption by just encrypting the few columns of data I need to with a simple Symmetric Key Encryption. If I go SQL Server encryption I can decrypt the data from outside reporting tools and not just within my .NET application. Here's the example I'm using: http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/

It uses a certificate created by SQL Server and then the DecryptByKey function to decrypt the data, but I'm trying to determine how secure this really is? How do I control what users can decrypt data or can anybody do it so long as they open the symmetric key and use the decrypt function?

解决方案

You have two alternatives:

1. Cryptographic control. With this only the users that know the password can decrypt the data. The downside is that the user must enter the decryption password each time they access the data. A Report woul have to contain a Password parameter that the user running the report fills with the data access password. Application must request the password from the user. Web sites must request password from visitor. And so on and so forth

2. Access control. The data is encrypted with a key that SQL Server itself has access to (ultimately the ecnryption chain goes all the way up to the Service Master Key and this is encrypted using DPAPI). This gives you no greater protection other that granting and denying SELECT would give you: is access control, not cryptographic control. Such a scheme protects solely against accidental loss of media (somebody finds a disk with your database, or you loose a laptop with the database on it). You can achieve the very same using Transparent Data Encryption or file level encryption (BitLocker).

The common data encryption scenario is to encrypt the data with a symmetric key, and then encryt the symmetric key with an asymmetric key (usually the private key of a certificate). The asymmetric key is then in turn encrypted with a password, and this password must be presented when trying to access data. The primary reason for this two level indirection is password change: when a password or a private key is compromissed the symmetric key is re-encrypted with a different asymmetric key or the asymmetric key is re-encrypted with a different password. This way the access password has changed without requiring a re-encryption of all the data. If the access would be granted directly to the symmetric key then a password compromise would possibly require to re-encrypt all the data, possible terabytes of data.

Where the two scenarios I presented differ is whether the asymmetric key is also ecnrypted with the database master key or not. Case 1) it isn't, case 2) it is. This is all explained in Encryption Hierarchy.

这篇关于如何控制什么用户可以解密SQL Server对称密钥加密的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！