在 SQL Server 2008 中加密数据库表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8427001/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Encrypting database tables in SQL Server 2008
提问by Ehsan
I have a Windows application using a database in SQL Server 2008.
我有一个使用 SQL Server 2008 中的数据库的 Windows 应用程序。
I do not want users to see the database tables.
我不希望用户看到数据库表。
How can I encrypttables in my database?
如何加密数据库中的表？
回答by demas
You have different options here.
您在这里有不同的选择。
You can use symmetric encryption for your data:
CREATE TABLE sales ( ... )
您可以对数据使用对称加密：
CREATE TABLE 销售 (...)
Create symmetric key:
创建对称密钥：
CREATE CERTIFICATE cert_sales WITH SUBJECT = N'Sales certificate',
START_DATE = N'2009-01-01', EXPIRY_DATE = N'2018-12-31';
CREATE SYMMETRIC KEY symkey_sales WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE cert_sales
Encrypt data:
加密数据：
TRUNCATE TABLE sales;
OPEN SYMMETRIC KEY symkey_sales DECRYPTION BY CERTIFICATE cert_sales;
INSERT INTO sales() SELECT a, ENCRYPTBYKEY(Key_Guid(N'symkey_sales'), B) FROM T2;
CLOSE SYMMETRIC KEY symkey_sales;
Decrypt data:
解密数据：
OPEN SYMMETRIC KEY symkey_sales DECRYPTION BY CERTIFICATE cert_sales;
SELECT a, CAST(DecryptByKey(B) as nvarchar(100)) FROM sales;
CLOSE SYMMETRIC KEY symkey_sales;
- You can use asymmetric encryption for your data
- You can use Transparrent Data Encryptionfor encrypt all database files:
- 您可以对数据使用非对称加密
- 您可以使用透明数据加密来加密所有数据库文件：
Create master key:
创建主密钥：
USE master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My$Strong$Password3'
Create certificate:
创建证书：
CREATE CERTIFICATE DEK_EncCert WITH SUBJECT = 'DEK Encryption Certificate'
Create DEK:
创建 DEK：
USE MySecretDB
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DEK_EncCert
Turn on encryption:
开启加密：
ALTER DATABASE MySecretDB SET ENCRYPTION ON
- You can use BitLocker - complete volume encryption
- 您可以使用 BitLocker - 完整的卷加密
回答by TomTom
Encryption wont help - SQL Server level encryption encrypts the files. The data is visible once you log in.
加密无济于事 - SQL Server 级别的加密会加密文件。登录后即可查看数据。
The only proper solution is called "programming". Basically go client/server and don't have users connect to the database.
唯一合适的解决方案称为“编程”。基本上去客户端/服务器并且没有用户连接到数据库。
Alternatively you could use permissions on the tables + an application password to elevate the rights for the application (not the user), but that is unsafe too (because you have to put the password somewhere).
或者，您可以使用表的权限 + 应用程序密码来提升应用程序（而不是用户）的权限，但这也不安全（因为您必须将密码放在某处）。
回答by iposner
The users won't see the content of the tables if you don't grant them SELECT permission. This means that they should NOT connect as members of the dbo group. Instead create one or more groups for the various security groups of users and assign permissions to the database objects you do want them to access to those groups.
如果您不授予他们 SELECT 权限，用户将看不到表的内容。这意味着他们不应作为 dbo 组的成员进行连接。而是为不同的用户安全组创建一个或多个组，并为您确实希望他们访问这些组的数据库对象分配权限。
Note that if you have a group of objects that will be collectively permissioned to one or more user groups, you can create these groups in a separate schemaand then grant the user group permission to access the entire schema. This makes permissioning a one-time affair as you add database objects to the schema.
请注意，如果您有一组对象将共同授予一个或多个用户组的权限，您可以在单独的架构中创建这些组，然后授予用户组访问整个架构的权限。当您将数据库对象添加到架构时，这使得许可成为一次性的事情。
