SQL Server 2008 - 透明数据加密未解密访问
我的问题是重复的 - Oracle 透明数据加密未解密访问 - 但对于SQL Server 2008。
我可以按照以下所有陈述都成立的方式设置 SQL Server 2008 数据库吗？
a) 某些列，可能是所有列都被加密，因此对数据库文件的直接文件访问将不允许攻击者检索任何记录
b) 加密的列为授权用户透明地解密，其中授权发生，例如通过具有某个角色或权限
c) 具有执行“正常”管理任务（调整、创建/删除架构对象、重新启动数据库、从数据字典中选择）的适当权限的管理员可以选择表，但只能看到加密列中的加密数据。
如果这是可能的，我该怎么做。如果不可能，我必须采取哪些选择才能至少“接近”这些要求？
我知道a）是可能的，但我不确定b）和c）。
谢谢。
My question is a duplicate of - Oracle Transparent Data Encryption undecrypted access - but for SQL Server 2008.
Can I set up a SQL Server 2008 database in a way that all of the following statements are true?
a) certain columns, potentially all columns are encrypted, so that direct file access to the database file wouldn't allow an attacker to retrieve any records
b) the encrypted columns are transparently decrypted for authorized user, where authorization happens e.g. by having a certain role or privilege
c) an admin who has suiteable privileges for doing 'normal' admin tasks (tuning, creating/droping schema objects, restarting database, selecting from data dictionary) can select the tables but will see only encrypted data in the encrypted columns.
If this is possible, how do I do it. If it is not possible, what are the options I have to at least get 'close' to these requirements?
I know a) is possible but I'm not sure about b) and c).
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
否，因为您无法仅对选定的列执行透明数据加密：
以下是您应该考虑的有关 TDE 的一些事项（由 Brad McGehee 的博客）：
TDE 不保护内存中的数据，因此任何拥有数据库 DBO 权限或 SQL Server 实例 SA 权限的人都可以看到敏感数据。换句话说，TDE 无法阻止 DBA 查看他们想查看的任何数据。
TDE 不
TDE 不是细粒度的。然后整个数据库都被加密。
TDE 不保护客户端应用程序和 SQL Server 之间的通信，因此必须使用其他加密方法来保护通过网络流动的数据。
TDE 不保护客户
FILESTREAM 数据未加密。
当 SQL Server 实例上的任何一个数据库打开 TDE 时，tempdb 数据库会自动加密，这可能会导致同一实例上运行的加密和非加密数据库的性能较差。
使用 TDE 加密的数据库无法利用 SQL Server 2008 的新备份压缩功能。如果您想同时利用备份压缩和加密，则必须使用第三方应用程序，例如 SQL Backup，它允许您执行这两项任务而不会受到任何影响。
您可能会感兴趣：如何：加密数据列
No, because you cannot perform Transparent Data Encryption on selected columns only:
Here are some things you should consider about TDE (courtesy of Brad McGehee's blog):
TDE does not protect data in memory, so sensitive data can be seen by anyone who has DBO rights to a database, or SA rights to the SQL Server instance. In other words, TDE cannot prevent DBAs from viewing any data they want to see.
TDE is not granular. Then entire database in encrypted.
TDE does not protect communications between client applications and SQL Server, so other encryption methods must be used to protect data flowing over the network.
FILESTREAM data is not encrypted.
When any one database on a SQL Server instance has TDE turned on, then the tempdb database is automatically encrypted, which can contribute to poor performance for both encrypted and non-encrypted databases running on the same instance.
Although fewer resources are required to implement TDE than column-level encryption, it still incurs some overhead, which may prevent it from being used on SQL Servers that are experiencing CPU bottlenecks.
Databases encrypted with TDE can’t take advantage of SQL Server 2008’s new backup compression. If you want to take advantage of both backup compression and encryption, you will have to use a third-party application, such as SQL Backup, which allows you to perform both of these tasks without penalty.
This might be of interest: How to: Encrypt a Column of Data