问题描述
以前，用户输入了一吨信息包括SSN，配偶SSN和CC数据。当用户完成该过程时，信息被推送到PDF，压缩（然后被加密），然后FTP返回到我们的服务器。我们将所有内容都保存在数据库中，除了SSN和CC之外，会话中断时被删除。
现在，我们需要保存该信息数据库以及某些情况下用户A完成后，用户B需要进入并登录表单。用户B完成后，将创建文件并删除SSN / CC数据。这意味着数据必须存在于我们的数据库中，从几分钟到几个月。有一个设置的到期日，我从数据库中删除数据并重新开始。注意：我没有使用CC数据实际收费，所以我不能把它交给像Authorize.net或Paypal这样的第三方。
解释说，我需要知道最好的方式来加密这些东西并保护它。在我的代码中使用用户的GUID作为密钥，或者只是SQL Server 2005列加密并将解密功能限制在Web用户之间，我之间撕裂了。
I像AES，因为它使少数几个使用数据库访问权限的用户使用网络用户的密码来获取所有的CC数据。他们可以访问源代码，并可以复制解密方法，但至少比只是运行一些查询更难。
不幸的是，我没有时间推动不存储CC数据的方式，但是我有一些下一个版本的想法。我必须在本周做出选择并实施加密。
我不明白在应用程序代码中如何使用AES加密用户GUID作为对称密钥将保护数据不被数据库访问的人解密。在大多数系统中，用户ID GUID也不会以明文形式存储在数据库中？如果是这样，那么访问数据库的任何人都可以通过传递等价解密函数来解密任何数据。
根据应用程序服务器如何连接到数据库服务器内置的列加密功能在SQL Server中应该是一个很好的解决方案。如果使用集成身份验证，您可以避免为应用程序用户提供明文密码。通过使用可用的访问控制来保护加密密钥，您可以设置它们，以便其他用户（或可选的甚至不是DBA）都可以从数据库中任意解密数据。
2008年6月，我介绍了使用可用的SQL Server 2005和2008中的加密功能
以下是该演示文稿的示例代码，概述如何完成此操作，以便只有适当的用户可以查看解密的数据：
- 为数据库创建主密钥，这将用于加密数据库中的每个其他密钥，并依次加密服务主键
CREATE MASTER KEY ENCRYPTION BY password ='MasterKey1 $'/ pre>
- 为用户创建用于保护自己的对称密钥的证书
创建对称密钥来加密数据，因为它们更快，并且没有基于密钥大小的固有数据大小限制。CREATE CERTIFICATE data_cert授权数据WITH SUBJECT ='数据证书'
- 请注意，您还可以使用ENCRYPTION BY PASSWORD选项，以防止不知道密码的DBA打开证书，从而无法解密数据
- 为每个用户创建对称密钥保护他们的数据
- 注意如果在XP上运行SQL Server，AES算法不可用，您必须使用3DESCREATE SYMMETRIC KEY data_key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE data_cert
- 注意你也可以使用ENCRYPTION BY PASSWORD这里也是为了防止不了解密码的DBA打开这个密钥。
- 授予对称密钥的权限，这样只有正确的用户可以访问它们
SYMMETRIC KEY上的GRANT VIEW定义:: data_key TO [DOMAIN\ApplicationServiceAccount]
这给你一个对称密钥，只能由适当的用户打开用于加密/解密表中的数据。您还可以获得数据库引擎中密码学最佳实践功能的优势，例如每个单元格都使用唯一的初始化向量进行加密，以及SQL Server中的密钥管理功能，可让您轻松地定期更改密钥。
I read through quite a few similar questions here on SO but none were in quite the same situation as I am.
Previously, user enters in a ton of info including SSN, Spouse SSN, and CC data. When the user completed the process, the info was pushed on PDFs, zipped up (which then got encrypted), and then FTPed back into our server. We saved everything in the DB except SSNs and CC, which were erased when the session died.
Now, we need to save that info the database as well for some cases where after User A is done, User B needs to come in and sign off on the forms. After User B is done, the files are created and SSNs/CC data deleted. This means that data has to live in our DB from a few minutes to possibly a month. There is a set expiration date where I wipe out the data from the DB and make them start over. Note: I am not using the CC data to actually make a charge so I can't hand it off to a third party like Authorize.net or Paypal.
With that explained, I need to know the best way to encrypt this stuff and protect it. I'm torn between doing AES in my code using the user's GUID as the key or just SQL Server 2005 column encrypting and limiting the decrypt function to the web user.
I like AES because it keeps the few people who have DB access from using the web user's password to grab all the CC data. They would have access to the source code and could replicate the decrypt method but at least that's a little harder than just running some queries.
Unfortunately, I don't have time push through a way of not storing the CC data but I have some ideas for the next release. I have to make a choice and implement the encryption this week.
解决方案I do not understand how using AES encryption in your application code with the users GUID as the symmetric key will protect the data from being decrypted by people with database access. In most systems wouldn't the user id GUID also be stored in the database in clear text? If so then anyone with access to the database will be able to decrypt any data by passing it through the equivalent decryption function.
Depending on how your application server connects to the database server the built in column encryption functionality in SQL Server should be a good solution. If you use integrated authentication you can avoid having a clear text password for the application user. By using the access controls available to secure your encryption keys you can set them up so that no other user (and optionally not even the DBA) can arbitrarily decrypt the data from the database.
In June 2008 I gave a presentation on using the encryption functionality in SQL Server 2005 and 2008 that is available here
Here is sample code from that presentation that gives an overview of how to accomplish this so that only the appropriate users can view the decrypted data:
-- create the master key for the database, this will be used to encrypt every other key in the DB and is in turn encrypted by the service master key
CREATE MASTER KEY ENCRYPTION BY password = 'MasterKey1$'-- create certificates for the user to be used to protect its own symmetric keys-- symmetric keys are created to encrypt data because they are faster and do not have inherent data size limitations based on key size
CREATE CERTIFICATE data_cert AUTHORIZATION data WITH SUBJECT = 'Data Cert'-- note that you can also use the ENCRYPTION BY PASSWORD option here to prevent a DBA that does not know the password from opening the certificate and thus being unable to decrypt the data
-- create symmetric keys for each user to protect their data-- note if running SQL Server on XP the AES algorithms are not available, you must use 3DES
CREATE SYMMETRIC KEY data_key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE data_cert-- note you can also use ENCRYPTION BY PASSWORD here as well to prevent a DBA who does not know the password from opening this key
-- grant permissions to the symmetric keys so that only the correct user can access them
GRANT VIEW DEFINITION ON SYMMETRIC KEY::data_key TO [DOMAIN\ApplicationServiceAccount]This gives you a symmetric key that can only be opened by the appropriate user to use to encrypt/decrypt the data in the tables. You also gain the benefits of the cryptography best practices functionality in the database engine such as each cell being encrypted with a unique initialization vector as well as the key management functionality in SQL Server that allows you to easily change your keys regularly.
这篇关于存储SSN / CC数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！