如何最好地加密/解密 SQL Server 数据以防止开发人员看到它？
这是一个有趣的问题，我正在寻找一种使其一切可行的模式。
我正在为学校系统构建一个智能客户端应用程序。它将包含有关学生的信息，包括他们的成绩单、病假等等。它将生成学生级别的报告，包括他们的成绩单，每份报告都包含老师的非常个人化的评论。该应用程序将通过网络服务从远程服务器检索数据。
所以这些数据是相当保密的。我将在数据库中对其进行加密，并在检索时对其进行解密 - 没有问题。
问题是我和我的团队真的永远不会看到生产明文数据。然后出现了一个有趣的问题来调查生产错误！我们希望打开与用户相同的记录以查看他们所看到的内容。但如果我们这样做，我们就违反了保密规定。
我的想法是这样的，但并不完美。
- 首先，在存储到数据库之前对数据进行加密，然后在 UI 上对其进行解密。那里没有什么新鲜事。
- 其次，在用户界面中放置一种机制来混淆特权数据。 （即，姓名和教师叙述是有特权的；年级不是。）我不会加密它，但会混淆它 - 即使一个简单的密钥转换就足够了。原因是这些报告充满了文字。如果我加密一个段落并在报告中显示结果，它将是一堵由大写字符组成的实心墙，看起来与原始文本完全不同。如果我对字母字符进行按键转换，它将无法阅读，但看起来仍然像段落、句子、项目符号列表等。可以更容易地看到出了什么问题，而不会增加视觉上的复杂性。
- 第三，我设置了一个配置设置，仅针对系统管理员角色的成员（而不是教师或学校管理员）执行此 UI 混淆。在开发过程中，我将此配置设置为False，我们针对假明文进行开发。对于生产，我们将其设置为 True，从那时起，我们只能看到模糊的文本。
最后，对于我们绝对必须查看学生记录的明文的情况，我们在 UI 中有一个覆盖设置，可以取消配置设置并显示明文。我们在人性层面上进行管理——通知学校管理部门，在这个日期，出于这个原因，我们需要查看这个学生的记录，等等。签署了签字，给出了抱怨的同意，律师被赶到他们的喷气式飞机上，冲洗并重复。
想法？我觉得这一定是人人皆知的。如果可能的话，请帮助我改进这个计划。
Here's an interesting problem, and I'm looking for a pattern that will keep it all workable.
I am building a smart-client app for a school system. It will contain information about students including their report cards, sick days, and so forth. it will generate student-level reports, including their report cards, each rich with very personal commentary by their teachers. The app will retrieve data from the remote server via web services.
So the data is quite confidential. I will encrypt it in the database, and decrypt it on retrieval - no problem there.
The problem is that my team and I should really never see the production plaintext data. An interesting problem emerges then for investigating production bugs! We'll want to open the same record as the user to see what they're seeing. But if we DO we're violating confidentiality.
My thought is this, and it's not perfect.
- First, encrypt the data before storing in the database, decrypt it at the UI. Nothing new there.
- Second, put a mechanism in the UI to obfuscate privileged data. (i.e., names and teacher narrative are privileged; grade level is not.) I won't encrypt it, but obfuscate it - even a simple key-shift would suffice. The reason being, these reports are full of text. If I encrypt a paragraph and show the result in a report, it will be a solid wall of uppercase characters, looking nothing like the original text. If I do a key-shift on the alphabetic characters, it will be unreadable, but will still look like paragraphs, sentences, bulleted lists and the like. It will be easier to see what's going wrong without adding visual complication.
- Third, I put in a configuration setting to perform this UI obfuscation just for members of say, the SysAdmin role, not the Teacher or SchoolAdmin. During development, I set this configuration to False, and we develop against fake plaintext. For production, we set it to True, and from that point forward, we only see obfuscated text.
Finally, for those cases where we absolutely MUST see the plaintext of a student's records, we have an override setting in the UI that countermands the configuration setting, and presents the plaintext. And we manage that at the human level - informing the school administration that on THIS date for THIS reason we will need to see THIS student's record, etc. Sign offs are signed, grumbling consent is given, lawyers are scrambled to their jets, rinse and repeat.
Thoughts? I feel like this must be well-trodden ground. Please help me improve on this plan, if possible.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
由于我们的团队遇到了类似的问题，因此我们必须使用大量的模拟数据存储。生产始终是猜测工作。没有人被允许知道任何密码。
编辑
如果您想完全安全，您可以允许外部公司处理数据，这使他们能够保护数据并保证您不会被起诉如果发生错误。只是我的.02
Having been on a team with a similar issue, we had to use a massive store of mock data. Production was ALWAYS guess work. Noone was allowed to know any of the ciphers.
EDIT
If you wanted to be completely safe you would allow an outside company to handle the data, this puts them in the position of protecting the data and guarantees you will not be sued if a mistake happens. Just my .02
因此，一般来说，我的处理方法如下：
SQL Server 具有加密功能 - 透明加密（这不适合您的情况，因为您将在查询中看到解密的数据）或基于密钥的加密，其中用户帐户在密钥上具有 ACL。使用此方法，您可以在 SQL Server 本身上创建 X509 证书，然后生成具有合适 ACL 的对称密钥。然后，您可以在存储过程中打开对称密钥，将未加密的数据返回到您的应用程序。当然，您应该通过安全方式连接到 SQL - 您可以在 SQL 服务器上放置 X509 证书来保护连接。
当然现在你有密钥管理的问题。您可以为您的应用程序创建一个特定的 Windows 帐户来运行，并使用随机的强密码，一旦您配置了应用程序池，该密码就会被丢弃，然后将该 NT 帐户添加到 SQL 中（如果您在域环境中，这很容易）为此，您必须镜像 IIS 和 SQL 服务器上的工作组帐户）。为了进行调试，您需要另一个有权访问密钥的帐户。该帐户的密码应该采用“碎玻璃”设置 - 存储在可审计的地方，或者一半的密码在必须同意的两个或更多人之间共享，并正式签署需要它（然后一旦使用就会更改） ）
有关于SQL加密的介绍 此处，但它不涵盖 ACL。 MSDN 有整个部分，其中还涵盖了身份验证器和为您提供多种选择。
或者您可以选择使用加密 Web 服务，调用该服务来加密数据，这会返回对密钥和密文的 GUID 引用。这样，密钥可以存储在受良好保护的数据库中，并且解密功能可以基于每个帐户受到保护。同样，你必须有一个碎玻璃帐户。当客户没有足够的信心自行管理 SQL 加密时，我就这样做了。
So generally I've approached this as follows:
SQL server has encryption functionality - either transparent encryption (which is not suitable for your case as you will see decrypted data in queries) or key based encryption where user accounts have ACLs on the key. With this method you create an X509 certificate on the SQL server itself, and then generate symmetric keys which have suitable ACLs. Within your stored procedures you can then open the symmetric key to return unencrypted data to your application. Of course you should be connecting to SQL via secure means - you can put an X509 certificate on the SQL server to protection connections.
Of course now you have the problem of key management. You can create a specific windows account for your application to run under, with a random strong password that is discarded as soon as you configure the application pool and then add that NT account into SQL (if you're within a domain environment this is easy to do, workgroups you must mirror the account on the IIS and SQL server). For debugging you'll need another account with access to the keys. The password for this account should be in a "break glass" set up - stored somewhere that is auditable, or half the password is shared between two or more people who must agree, with formal signoff that it's needed (and then it's changed once used)
There's an introduction to SQL encryption here, but it doesn't cover ACLs. MSDN has an entire section on it which also covers authenticators, and the various options available to you.
Or you take the option of having an encryption web service, which you call to encrypt data, this returns a GUID reference to a key and the cipher text. This way the key can be stored in a well protected database, and the decrypt functions can be protected on a per account basis. Again you'd have to have a break glass account. I've done this when customers don't feel confident enough to manage SQL encryption themselves.
实际上，您不需要在 UI 处解密数据。 SQL Server 具有为您进行实时加密的工具。如果有人需要查看明文，他们可以被分配到一个授予他们该权限的角色（并在完成后将其撤出）。
但是，如果查看数据违反了保密性，那么显然您永远无法看到生产数据。唯一的解决方案是使用经过修改的数据或完全随机的数据复制数据。
Actually, you don't need to decrypt the data at the UI. SQL Server has tools for doing real-time encryption for you. If someone needed to see the plaintext, they could be dropped into a role that would given them that permission (and taken out when they were done).
However, if seeing the data breaks confidentiality then clearly you can never see the production data. The only solution is a copy of the data with either munged data or completely random data.
您绝对应该拥有的一件事是不可变的审计跟踪。每当任何人访问任何机密数据时，都会创建审核记录。如果系统管理员请求访问他们通常不应该查看的报告数据，则会记录异常。
客户应制定业务流程来定期检查审核日志中的任何异常情况。这既可以对滥用系统起到威慑作用，又可以在有人作弊时进行事后调查。
One thing you should definitely have is an immutable audit-trail. Whenever anyone accesses any confidential data, an audit record is created. In cases like SysAdmins requesting access to report data that they shouldn't ordinarily be looking at, exceptions are logged.
The client should put a business process in place to regularly review any exceptions in the audit log. This should both act as a deterrent to abusing the system, and allow for investigations after the fact if someone does go rogue.