在SQL Server 2008中加密数据库表 [英] Encrypting database tables in SQL Server 2008

问题描述

我不希望用户看到数据库表。

如何在我的数据库中加密表？

解决方案

• 您可以对数据使用对称加密：

  
  
  

  CREATE TABLE sales（
  ...
  ）

创建对称密钥：

  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 
  

加密数据：

  TRUNCATE TABLE销售; 
 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; 
  

解密数据：

  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; 
  

• 您可以为数据使用非对称加密


• 您可以使用跨数据加密加密所有数据库文件：

创建主密钥：

  USE master 
 go 
 CREATE MASTER KEY加密PASSWORD ='My $ Strong $ Password $ 123 '
  

创建证书：

  CREATE CERTIFICATE DEK_EncCert WITH SUBJECT ='DEK加密证书'
  

创建DEK：

 使用MySecretDB 
 go 
使用ALGORITHM创建数据库加密密钥= AES_256 
服务器加密CERTIFICATE DEK_EncCert 
  

打开加密：

  ALTER DATABASE MySecretDB SET ENCRYPTION ON 
  

• 您可以使用BitLocker - 完整卷加密

I have a Windows application using a database in SQL Server 2008.

I do not want users to see the database tables.

How can I encrypt tables in my database?

解决方案

You have different options here.

• You can use symmetric encryption for your data:

  CREATE TABLE sales ( ... )

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 Encryption for encrypt all database files:

Create master key:

USE master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My$Strong$Password$123'

Create certificate:

CREATE CERTIFICATE DEK_EncCert WITH SUBJECT = 'DEK Encryption Certificate'

Create 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

这篇关于在SQL Server 2008中加密数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！