搜集博客园邀月工作室的有关加密对称加密的文章

SQL Server 2008中的代码安全（五）：非对称密钥加密

SQL Server 2008中SQL应用系列--目录索引

非对称密钥包含数据库级的内部公钥和私钥，它可以用来加密和解密SQL Server数据库中的数据，它可以从外部文件或程序集中导入，也可以在SQL Server数据库中生成。它不像证书，不可以备份到文件。这意味着一旦在SQL Server中创建了它，没有非常简单的方法在其他用户数据库中重用相同的密钥。非对称密钥对于数据库加密属于高安全选项，因而需要更多的SQL Server资源。

我们看一组例子：

示例一、创建非对称密钥

创建非对称密钥使用如下命令:

CREATEASYMMETRIC KEY  创建非对称密钥。（http://msdn.microsoft.com/en-us/library/ms174430.aspx）

--以下语句创建一个非对称密钥asymDemoKey

use DB_Encrypt_Demo
go

CREATE ASYMMETRIC KEY asymDemoKey --创建非对称密钥名称
WITH ALGORITHM = RSA_512 --加密安全类型
ENCRYPTION BY PASSWORD ='TestASYM123!'--密码

 

示例二、查看当前数据库中的非对称密钥

使用目录视图sys.asymmetric_keys(http://msdn.microsoft.com/en-us/library/ms188399.aspx)来查看。

--查看当前数据库中的非对称密钥
use DB_Encrypt_Demo
go

SELECT name, algorithm_desc, pvt_key_encryption_type_desc
FROM sys.asymmetric_keys

----结果返回

/*
name algorithm_desc pvt_key_encryption_type_desc
asymDemoKey RSA_512 ENCRYPTED_BY_PASSWORD
*/ 

示例三、修改非对称密钥的私钥密码

你可以使用带有ENCRYPTION BYPASSWORD和DECRYPTION BYPASSWORD选项的ALTERASYMMETRIC KEY（http://technet.microsoft.com/en-us/library/ms189440.aspx）修改私钥的密码。

--修改私钥密码
ALTER ASYMMETRIC KEY asymDemoKey--要修改的密钥名称
WITH PRIVATE KEY--私钥
(ENCRYPTION BY PASSWORD ='newpasswordE4D352F280E0',--指定新密码
DECRYPTION BY PASSWORD ='TestASYM123!')--旧密码是用来解密的  

 

示例四、使用非对称密钥对数据进行加密和解密。

由于同时需要公钥和密钥，在维护保密数据时使用非对称密钥来加密数据是非常安全的方式。但同时用于大数据集时将消耗更多的资源。

不推荐使用非对称密钥对数据加密，但它仍然是一个选择。一旦将非对称密钥加到数据库，就可以用来加密和解密数据。

用到以下两个sql函数:

EncryptByAsymKey加密数据。（http://technet.microsoft.com/en-us/library/ms186950.aspx）

DecryptByAsymKey解密数据。（http://msdn.microsoft.com/en-us/library/ms189507.aspx）

注意，在通过证书加密时，DecryptByAsymKey返回的是varbinary类型的加密数据。

下面是一个例子：

use DB_Encrypt_Demo
go

--创建需要加密的数据
CreateTable BankUser
(PKID intprimarykeyidentity(10001,1)
,UserNo varbinary(1000) null
,CurState smallintdefault(0) notnull
)
go

insertinto BankUser
(UserNo,CurState)
VALUES (EncryptByAsymKey(AsymKey_ID('asymDemoKey'),'137492837583249ABR'),1)
--插入一条记录，字段UserNo存储了加密的号码值
go

select PKID,Curstate, cast(UserNo asnvarchar(1000)) as UserNo
from BankUser
where PKID=10001

 

明文结果： 

查看未加密的数据：

SELECT PKID,Curstate,
cast
(DecryptByAsymKey(AsymKey_ID('asymDemoKey'),UserNo,N'newpasswordE4D352F280E0')
asvarchar(1000)) as UserNo --需要原始私钥
from BankUser where PKID=10001

示例五、删除非对称密钥

命令：DROPASYMMETRIC KEY 删除指定的非对称密钥( http://msdn.microsoft.com/en-us/library/ms188389.aspx)

例子：

DROP ASYMMETRIC KEY asymDemoKey

 

小结：

1、本文主要介绍非对称密钥的创建、删除、查看以及用它来修改私钥、进行数据的加密和解密。

2、非对称密钥包含数据库级的内部公钥和私钥，它可以用来加密和解密SQL Server数据库中的数据。

3、非对称密钥对于数据库加密属于高安全选项，因而需要更多的SQL Server资源，不推荐使用。

下文将主要介绍相对简单的并且广泛应用的对称密钥加密(Symmetric Key Encryption)

邀月注：本文版权由邀月和博客园共同所有，转载请注明出处。
助人等于自助!  3w@live.cn

分类: SQL Server 2008 Tips, Tips

标签: downmoon, sql, ASYMMETRIC KEY, 非对称密钥

 

SQL Server 2008中的代码安全（六）：对称密钥加密

 SQL Server 2008中SQL应用系列--目录索引

证书和非对称密钥使用数据库级的内部公钥加密数据，并且使用数据库级内部私钥解密数据。而对称密钥相对简单，它们包含一个同时用来加密和解密的密钥。困此，使用对称密钥加密数据更快，并且用在大数据时更加合适。尽管复杂度是考虑使用它的因素，但它仍然是一个很好的加密数据的选择。

 我们看一组例子：

示例一、创建对称密钥

对称密钥的特性是：在数据库会话中使用它对数据进行加密和解密前必须首先打开。

创建对称密钥使用如下命令:CREATESYMMETRIC KEY  创建对称密钥。（http://msdn.microsoft.com/en-us/library/ms188357.aspx）

use DB_Encrypt_Demo
go

-- 创建一个用于加密对称密钥的非对称密钥
CREATE ASYMMETRIC KEY symDemoKey --名称
WITH ALGORITHM = RSA_512 --加密算法
ENCRYPTION BY PASSWORD ='TestSYM456!'--密码

-- 创建一个对称密钥
CREATE SYMMETRIC KEY sym_Demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY symDemoKey

示例二、查看当前数据库中的对称密钥

使用目录视图sys.symmetric_keys(http://msdn.microsoft.com/en-us/library/ms189446.aspx)来查看。

--查看当前数据库中的非对称密钥
use DB_Encrypt_Demo
go
SELECT name, algorithm_desc FROM sys.symmetric_keys
----结果返回
/*
name algorithm_desc
sym_Demo TRIPLE_DES
*/

示例三、修改非对称密钥的加密方式

你可以使用ALTERSYMMETRIC KEY（http://technet.microsoft.com/en-us/library/ms189440.aspx）命令修改对称密钥的加密方式。但执行前必须使用OPENSYMMETRIC KEY(http://msdn.microsoft.com/en-us/library/ms190499.aspx)命令打开它。

use DB_Encrypt_Demo
go

--先用私钥密码打开对称密钥
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY ASYMMETRIC KEY symDemoKey
WITH PASSWORD ='TestSYM456!'

--打开之后，先增加密码加密，取代原密钥
ALTER SYMMETRIC KEY sym_Demo
ADD ENCRYPTION BY PASSWORD ='newnew!456'
--再删除非对称密钥加密
ALTER SYMMETRIC KEY sym_Demo
DROP ENCRYPTION BY ASYMMETRIC KEY symDemoKey
--完成操作后，关闭对称密钥
CLOSE SYMMETRIC KEY sym_Demo

 

示例四、使用对称密钥对数据进行加密和解密。

1、为了使用对称密钥对数据进行加密，必须首先打开它，然后使用函数EncryptByKey 加密数据。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)

2、使用DecryptByKey来解密使用对称密钥加密的数据。注意DecryptByKey不像甩EncryptByKey，无须使用对称密钥GUID。因此，为了解密，必须打开正确的对称密钥会话，否则会显示null。

下面是一个例子：

/****************************3w@live.cn***********************/
USE DB_Encrypt_Demo
GO
--创建测试数据表，用于对称加密
CREATETABLE dbo.PWDQuestion
(CustomerID intNOTNULLPRIMARYKEY,
PasswordHintQuestion nvarchar(300) NOTNULL,
PasswordHintAnswer varbinary(200) NOTNULL)
GO
--插入加密数据
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD ='newnew!456'
INSERT dbo.PWDQuestion
(CustomerID, PasswordHintQuestion, PasswordHintAnswer)
VALUES
(12, '您出生的医院名称？',
EncryptByKey(Key_GUID('sym_Demo '), '杭州市一'))
CLOSE SYMMETRIC KEY sym_Demo

查看未加密的数据：

--解密数据
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD ='newnew!456'
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer) asvarchar(200))PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID =12
--打开后切记关闭！！！3w@live.cn
CLOSE SYMMETRIC KEY sym_Demo

--不打开直接读取
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer) asvarchar(200))PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID =12

至此，好像已经大功告成了，别，千万别高兴得太早！

这里有个问题，如果恶意用户不知道CustomerID＝13的PasswordHintAnswer列的真实值，但知道CustomerID＝14的PasswordHintAnswer列的真实值,则完全可以通过恶意替换PasswordHintAnswer列而绕过加密！！3w@live.cn此时，我们索性连CustomerID列作为验证列也一起加密，以绝后患 !

注意：加密的验证列也可以由另一个相关表的列作为参数传入。

看一个完整的例子：

truncatetable dbo.PWDQuestion
go

--添加两个未加密的行
INSERT dbo.PWDQuestion
(CustomerID, PasswordHintQuestion, PasswordHintAnswer)
select13, '您出生的医院名称？',cast('浙江妇保院'asvarbinary)
unionall
select14, '您出生的医院名称？',cast('浙江妇保二院'asvarbinary)


--打开对称密钥,连CustomerID列一起加密
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD ='newnew!456'
UPDATE dbo.PWDQuestion
SET PasswordHintAnswer =
EncryptByKey(Key_GUID('sym_Demo'),
PasswordHintAnswer,1,--1表示使用验证器值
CAST(CustomerID asvarbinary))
WHERE CustomerID in (13,14)
--打开后切记关闭！！！3w@live.cn
CLOSE SYMMETRIC KEY sym_Demo


--此时必须这样查看原数据
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD ='newnew!456'
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer, 1,--1表示使用验证器值
CAST(CustomerID asvarbinary)) asvarchar(200))PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID =13
--打开后切记关闭！！！3w@live.cn
CLOSE SYMMETRIC KEY sym_Demo

恶意替换开始：

/**********************************************************
--我们用刚才的CustomerID = 13的PasswordHintAnswer列值
--替换CustomerID = 14的PasswordHintAnswer列值，
--再用刚才读取14的方法读取真实值
**********************************************************/

update dbo.PWDQuestion set PasswordHintAnswer=
(select PasswordHintAnswer from dbo.PWDQuestion where CustomerID =14)
where CustomerID =13

此时，我们再查看：

OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD ='newnew!456'
SELECT CustomerID,PasswordHintQuestion,
CAST(DecryptByKey(PasswordHintAnswer, 1,--1表示使用验证器值
CAST(CustomerID asvarbinary)) asvarchar(200))PasswordHintAnswer,
PasswordHintAnswer as binaryValue
FROM dbo.PWDQuestion
WHERE CustomerID in(13,14)
--打开后切记关闭！！！3w@live.cn
CLOSE SYMMETRIC KEY sym_Demo

郎勒个郎！爽吧！虽然复制了相同的二进制数据，可是读取结果令攻击者大失所望啊！

示例五、删除对称密钥

命令：DROPSYMMETRIC KEY 删除指定的对称密钥( http://technet.microsoft.com/en-us/library/ms182698.aspx)

例子：

DROP SYMMETRIC KEY symDemoKey

注意：如果加密密钥打开没有关闭，则drop失败。

小结：

1、本文主要介绍对称密钥的创建、删除、查看以及用它来修改加密方式、进行数据的加密和解密。

2、对称密钥的特性是：在数据库会话中使用它对数据进行加密和解密前必须首先打开。

3、对称密钥可用于大数据的加密。

下文将主要介绍证书加密(Certificate Encryption)

邀月注：本文版权由邀月和博客园共同所有，转载请注明出处。
助人等于自助!  3w@live.cn

SQL Server 2008中的代码安全（四）：主密钥

SQL Server 2008中SQL应用系列--目录索引

在SQL Server中的加密由层次结构形式进行处理以提供多级别的安全。SQL Server包含两个用于加密数据的密钥类型。如下图：

1、服务器主密钥（Service Master Key）,位于层次结构的最顶端，并且在安装SQL Server时自动创建，用于加密系统数据、链接的服务器登录名以及数据库主密钥。在第一次通过SQL Server使用服务主密钥来加密证书、数据库主密钥或链接的服务器主密码时，服务主密钥会自动生成，并且使用SQL Server服务账户的Windows证书来生成它。如果必须改变SQL Server服务账号，微软建议使用SQL Server配置管理器，因为这个工具将执行生成新服务主密钥需要的合适的解密和加密方法，而且可以使加密层次结构保持完整。服务主密钥也用于加密其下的数据库主密钥。

2、数据库主密钥（Database Master Key）,用于加密证书，以及非对称密钥和对称密钥。所有数据库都可以只包含一个数据库主密钥，在创建它时，通过服务主密钥对其加密。创建非对称密钥时，可以决定在加密非对称密钥对应的私钥是否包含密码。如果示包含密码，将使用数据库主密钥来加密私钥。

我们看一组例子：

示例一、备份及还原服务主密钥

用到以下两个sql命令:

BACKUPSERVICE MASTER KEY  导出服务主密钥。（http://msdn.microsoft.com/zh-cn/library/ms190337.aspx）

RESTORESERVICE MASTER KEY从备份文件中导入服务主密钥。（http://msdn.microsoft.com/zh-cn/library/ms187972.aspx）

--以下语句备份服务主密钥到C:\SqlBackup\SMK.bak

BACKUP SERVICE MASTER KEY
TOFILE='C:\SqlBackup\SMK.bak'
ENCRYPTION BY PASSWORD ='MakeItAGoodOne!1AB'----注意该密码可以使用单引号
go

--恢复服务主密钥
RESTORE SERVICE MASTER KEY
FROMFILE='H:\SqlBackup\SMK.bak'
DECRYPTION BY PASSWORD ='MakeItAGoodOne!1AB'
go

 

如果该密钥没有实际变化，而执行密钥恢复时，会收到提示：

--The old and new master keys are identical. No datare-encryption is required.

示例二、创建、再生成和删除数据库主密钥

用到以下两个sql命令:

CREATE MASTERKEY 创建数据库主密钥（http://technet.microsoft.com/zh-cn/library/ms174382.aspx）

ALTER MASTERKEY 重新生成数据库主密钥（http://msdn.microsoft.com/en-us/library/ms186937%28SQL.90%29.aspx）

DROP MASTERKEY 删除数据库主密钥（http://msdn.microsoft.com/en-us/library/ms180071.aspx）

当数据库主密钥被显式创建时，会同时自动生成一个额外生成的安全层，用于加密数据库中的新证书和非对称密钥，更进一步保护已加密的数据。

IFNOTEXISTS (SELECT name
FROM sys.databases
WHERE name ='BookStore')
BEGIN
CREATEDATABASE BookStore
END
GO

USE BookStore
GO
--创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='password'
go

USE BookStore
GO
--重新生成数据库主密钥
ALTER MASTER KEY
[FORCE] REGENERATE WITH ENCRYPTION BY PASSWORD ='password'

--删除数据库主密钥
USE BookStore
GO
DROP MASTER KEY

 

注意：如果该数据库主密钥仍然被其他数据库对象使用，则不能被删除，这点与架构类似。

同时一旦创建数据库主密钥，就立刻备份它是一个好的习惯。

示例三、备份、恢复一个数据库主密钥

语法：

BACKUP MASTERKEY导出服务主密钥。（http://technet.microsoft.com/en-us/library/ms174387.aspx）

RESTOREMASTER KEY从备份文件中导入数据库主密钥。（http://msdn.microsoft.com/en-us/library/ms186336.aspx）

下面是一个完整示例：

--备份数据库主密钥
USE BookStore
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='MagneticFields!'
GO
BACKUP MASTER KEYTOFILE='H:\SqlBackup\BookStore_Master_Key.BAK'
ENCRYPTION BY PASSWORD ='4D280837!!!'

--恢复数据库主密钥
RESTORE MASTER KEYFROMFILE='H:\SqlBackup\BookStore_Master_Key.BAK'
DECRYPTION BY PASSWORD ='4D280837!!!'
ENCRYPTION BY PASSWORD ='MagneticFields!'

 

与服务主密钥类似，如果没有修改，则会收到如下提示：

The old and new master keys are identical. No datare-encryption is required.

示例三、从数据库主密钥删除服务主密钥

当一个数据库主密钥被创建时，它被默认使用两种方式加密：服务主密钥和被使用CREATE MASTERKEY 命令中使用的密码。如果你不想使用服务主密码加密数据库主密钥（这种情况下，拥有sysadmin特权的login在不知道数据库主密钥的前提下将不能访问加密数据），你可以使用ALTER MASTERKEY 命令删除服务主密钥。

简略语法如下：

ALTER MASTER KEY

ADD ENCRYPTION BY SERVICE MASTER KEY |

DROP ENCRYPTION BY SERVICE MASTER KEY

由于服务主密钥允许拥有足够许可（如sysadmin）的用户自动使用数据库主密钥解密，因此，一旦删除了服务主密钥的加密，而再想修改数据库主密钥时，你必须使用一个新的命令访问它。OPEN MASTERKEY, 语法如下：

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

下面是一个例子：

ALTER MASTER KEYDROP ENCRYPTION BY SERVICE MASTER KEY
--一旦执行，任何数据库主密钥的修改需要使用OPEN MASTER KEY的口令访问，这样是为了重新应用服务主密钥的加密
OPEN MASTER KEY DECRYPTION BY PASSWORD ='MagneticFieldS!'
--一旦服务主密钥被用于加密数据库主密钥，数据库主密钥不再需要被显式打开或关闭。
ALTER MASTER KEYADD ENCRYPTION BY SERVICE MASTER KEY
--关闭数据库主密钥
CLOSE MASTER KEY

 

小结：

1、本文主要介绍服务主密钥的备份与还原，数据库的主密钥的创建、重新生成、删除和备份、还原。

2、一旦创建主密钥，立刻备份它是一个很好的习惯。

下文将主要介绍非对称密钥加密(Asymmetric Key Encryption)

邀月注：本文版权由邀月和博客园共同所有，转载请注明出处。
助人等于自助!  3w@live.cn

分类: SQL Server 2008 Tips

标签: downmoon, sql, master key, serveice master key, 主密钥

SQL Server 2008中的代码安全（七）：证书加密

 SQL Server 2008中SQL应用系列--目录索引

证书可以在数据库中加密和解密数据。证书包含密钥对、关于证书拥有者的信息、证书可用的开始和结束过期日期。证书同时包含公钥和密钥，前者用来加密，后者解密。SQL Server可以生成它自己的证书，也可以从外部文件或程序集载入。因为可以备份然后从文件中载入它们，证书比非对称密钥更易于移植，而非对称密钥却做不到。这意味着可以在数据库中方便地重用同一个证书。

注意：证书和非对称密钥同样的消耗资源。

我们看一组例子：

示例一、创建数据库证书

创建数据库证书:CREATESYMMETRIC KEY  （http://msdn.microsoft.com/en-us/library/ms187798.aspx）

USE DB_Encrypt_Demo
GO
--创建证书
CREATE CERTIFICATE cert_Demo --证书名称
ENCRYPTION BY PASSWORD ='asdfG!!!'--加密证书的密码
WITH SUBJECT ='DB_Encrypt_Demo DatabaseEncryption Certificate',--证书主题
START_DATE ='3/14/2011', EXPIRY_DATE ='10/20/2012'--起止日期
GO

示例二、查看数据库中的证书

使用目录视图sys.certificates(http://msdn.microsoft.com/en-us/library/ms189774.aspx)来查看。

--查看当前数据库中的证书
use DB_Encrypt_Demo
go

--查看证书
SELECT name, pvt_key_encryption_type_desc, issuer_name
FROM sys.certificates

----结果返回
/*
name pvt_key_encryption_type_desc issuer_name
cert_Demo ENCRYPTED_BY_PASSWORD DB_Encrypt_Demo Database Encryption Certificate
*/

示例三、备份和还原证书

创建证书后，也可以使用BACKUPCERTIFICATE（http://msdn.microsoft.com/en-us/library/ms178578.aspx）命令备份到文件，为了安全地保存或在其他数据库中还原它。

--备份证书
BACKUP CERTIFICATE cert_Demo
TOFILE='H:\SqlBackup\certDemo.BAK'--证书备份路径,用来加密
WITH PRIVATE KEY (FILE='H:\SqlBackup\certDemoPK.BAK',--证书私钥文件路径，用来解密
ENCRYPTION BY PASSWORD ='1234GH!!!',--加密私钥密码
DECRYPTION BY PASSWORD ='asdfG!!!' )--解密私钥密码

--备份后，可以在其他数据库中使用这个证书，或使用DROP CERTIFICATE命令删除它。
DROP CERTIFICATE cert_Demo
GO
--从备份文件中还原证书到数据库中
CREATE CERTIFICATE cert_Demo
FROMFILE='H:\SqlBackup\certDemo.BAK'
WITH PRIVATE KEY (FILE='H:\SqlBackup\certDemoPK.BAK',
DECRYPTION BY PASSWORD ='1234GH!!!' ,--解密私钥密码
ENCRYPTION BY PASSWORD ='asdfG!!!')--加密私钥密码

 

示例四、管理证书的私钥

使用ALTERCERTIFICATE( http://msdn.microsoft.com/en-us/library/ms189511.aspx)命令为证书增加或删除私钥。这个命令允许删除私钥（默认通过数据库主密钥时行加密）、增加私钥或修改私钥的密码。

 

--从证书中删除私钥 
ALTER CERTIFICATE cert_Demo 
REMOVE PRIVATE KEY 

--从备份文件为既有证书重新增加私钥 
ALTER CERTIFICATE cert_Demo 
WITH PRIVATE KEY 
(FILE='H:\SqlBackup\certDemoPK.BAK', 
DECRYPTION BY PASSWORD ='1234GH!!!' ,--解密私钥密码 
ENCRYPTION BY PASSWORD ='asdfG!!!')--加密私钥密码 

--修改既有私钥的密码 
ALTER CERTIFICATE cert_Demo 
WITH PRIVATE KEY (DECRYPTION BY PASSWORD ='asdfG!!!', 
ENCRYPTION BY PASSWORD ='mynewpassword!!!13E')

示例五、使用证书加密和解密。

使用函数EncryptByCert加密数据。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)

--从证书中删除私钥
ALTER CERTIFICATE cert_Demo
REMOVE PRIVATE KEY

--从备份文件为既有证书重新增加私钥
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY
(FILE='H:\SqlBackup\certDemoPK.BAK',
DECRYPTION BY PASSWORD ='1234GH!!!' ,--解密私钥密码
ENCRYPTION BY PASSWORD ='asdfG!!!')--加密私钥密码

--修改既有私钥的密码
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY (DECRYPTION BY PASSWORD ='asdfG!!!',
ENCRYPTION BY PASSWORD ='mynewpassword!!!13E')

下面是一个例子：

USE DB_Encrypt_Demo
GO
--插入测试数据
INSERT dbo.PWDQuestion
(CustomerID, PasswordHintQuestion, PasswordHintAnswer)
VALUES
(10, '您出生的医院名称?',
EncryptByCert(Cert_ID('cert_Demo'), '北京四合院家中'))

--查看明文
SELECTCAST(PasswordHintAnswer asvarchar(200))PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID =10

 

--查看原文 3w@live.cn
SELECT PasswordHintQuestion,
CAST(DecryptByCert(Cert_ID('cert_Demo'),PasswordHintAnswer,
N'mynewpassword!!!13E')
asvarchar(200)) PasswordHintAnswer
FROM dbo.PWDQuestion WHERE CustomerID =10

示例六、使用对称密钥对数据进行加密和解密。

在前面的文章中，你已经看到打开用非对称密钥加密的对称密钥的演示，它分两个步骤，首先用OPENSYMMETRIC KEY命令，然后是实际的DecryptByKey函数调用。SQL Server也提供了能够将这两个步骤合二为一的额外的解密函数：DecryptByKeyAutoAsymKey（http://msdn.microsoft.com/en-us/library/ms365420.aspx）和DecryptByKeyAutoCert（http://msdn.microsoft.com/en-us/library/ms182559.aspx）

USE DB_Encrypt_Demo
GO

--本例使用数据库主密码加密，因而不需要密码。3w@live.cn
----Create master Key Encryption By password='123ASD!'
----go

--创建非对称密钥 3w@live.cn
CREATE ASYMMETRIC KEY asymDemo_V2
WITH ALGORITHM = RSA_512
--创建对称密钥 3w@live.cn 
CREATE SYMMETRIC KEY sym_Demo_V2
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY asymDemo_V2

--打开对称密钥，插入记录
OPEN SYMMETRIC KEY sym_Demo_V2
DECRYPTION BY ASYMMETRIC KEY asymDemo_V2
INSERT dbo.PWDQuestion
(CustomerID, PasswordHintQuestion, PasswordHintAnswer)
VALUES
(22, '您出生的医院名称?',
EncryptByKey(Key_GUID('sym_Demo_V2'), '邵逸夫医院'))
CLOSE SYMMETRIC KEY sym_Demo_V2

 

此时，使用DecryptByKeyAutoAsymKey解密数据，只需要一个操作

SELECTCAST(DecryptByKeyAutoAsymKey(ASYMKEY_ID('asymDemo_V2'),NULL,
PasswordHintAnswer) asvarchar)
FROM dbo.PWDQuestion
WHERE CustomerID =22

 小结：

1、本文主要介绍证书的创建、删除、查看以及用它来修改加密方式、进行数据的加密和解密。

2、证书加密和非对称密钥加密相对对称密钥加密更为消耗资源。

下文将主要介绍SQL Server中最为令人鼓舞的透明数据加密(TDE)

邀月注：本文版权由邀月和博客园共同所有，转载请注明出处。
助人等于自助!  3w@live.cn

分类: SQL Server 2008 Tips, Tips

标签: downmoon, sql, 证书加密

SQL Server 2008中的代码安全（八）：透明加密(TDE)

 SQL Server 2008中SQL应用系列--目录索引

SQL Server 2008引入透明数据加密（Transparent Data Encryption），即TDE，它允许你完全无需修改应用程序代码而对整个数据库加密。当一个用户数据库可用且已启用TDE时，在写入到磁盘时在页级实现加密。在数据页读入内存时解密。如果数据库文件或数据库备份被盗，没有用来加密的原始证书将无法访问。这几乎是SQL Server2008安全选项中最激动人心的功能了，有了它，我们至少可以将一些初级的恶意窥视拒之见外。

使用透明数据库加密的理由：
• 整个数据库都进行了加密
• 应用程序不受到加密和解密的影响
–索引和数据类型不受到影响(除了FileStream)
–不需要调用任何加密和解密函数
• 性能影响相当小性
• 没有密钥不能使用备份

下面的两个例子将展示如何启用和维护透明数据加密。

示例一、启用透明加密(TDE)

/********************TDE**************** 3w@live.cn****************/
USE Master
GO
--------删除旧主密钥**********************3w@live.cn
--------Drop master Key 
--------go
--创建主密钥**********************3w@live.cn
Create MASTER KEY ENCRYPTION
BY PASSWORD ='B19ACE32-AB68-4589-81AE-010E9092FC6B'
GO
--创建证书,用于透明数据加密**********************3w@live.cn
CREATE CERTIFICATE TDE_Server_Certificate
WITH SUBJECT ='Server-level cert for TDE'
GO

USE DB_Encrypt_Demo
GO
--第一步：现在开始透明加密**********************3w@live.cn
CREATE DATABASE ENCRYPTION KEY--创建数据库加密密钥
WITH ALGORITHM = TRIPLE_DES_3KEY--加密方式
ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate--使用服务器级证书加密
GO
/*
Warning: The certificate used for encrypting the database encryption key
has not been backed up.
You should immediately back up the certificate and the private key
associated with the certificate.
If the certificate ever becomes unavailable or
if you must restore or attach the database on another server,
you must have backups of both the certificate and the private key
or you will not be able to open the database.
*/

--第二步：打开加密开关**********************3w@live.cn
ALTER DATABASE DB_Encrypt_Demo
SET ENCRYPTION ON
GO

--查看数据库是否加密
SELECT is_encrypted
FROM sys.databases
WHERE name ='DB_Encrypt_Demo'

 

注意：一旦在数据库应用了加密，应该立刻备份服务器级证书！

没有加密DEK的证书，该数据库将无法打开，附加到别的服务器也无法使用，数据库文件亦不会被Hack。如果一个DBA想要合法地将数据库从一个SQL Server实例移动到另一个SQL Server实例，那么她应该首先备份服务器级证书，然后在新的SQL Server实例中创建证书。此时可以合法地备份、还原数据库或附加数据及日志文件。

示例二、管理和移除透明加密(TDE) 

USE DB_Encrypt_Demo
GO
--修改加密算法
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_128
Go

SELECT DB_NAME(database_id)databasenm,
CASE encryption_state
WHEN 0 THEN'No encryption'
WHEN 1 THEN'Unencrypted'
WHEN 2 THEN'Encryption in progress'
WHEN 3 THEN'Encrypted'
WHEN 4 THEN'Key change in progress'
WHEN 5 THEN'Decryption in progress'
END encryption_state,
key_algorithm,
key_length
FROM sys.dm_database_encryption_keys

/*
对所有用户数据库的加密处理也包含对tempdb的处理
databasenm encryption_state key_algorithm key_length
tempdb Encrypted AES 256
DB_Encrypt_Demo Encrypted AES 128
*/

 

注意：对所有用户数据库的加密处理也包含对tempdb的处理

除了更改DEK的算法，我们也可以更改用来加密DEK的服务器级证书（该证书应该定期更改）

USE master
GO
CREATE CERTIFICATE TDE_Server_Certificate_V2
WITH SUBJECT ='Server-level cert for TDEV2'
GO
USE DB_Encrypt_Demo
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate_V2--用新证书修改DEK

--移除数据库透明加密
ALTER DATABASE DB_Encrypt_Demo
SET ENCRYPTION OFF
GO

--移除TDE后，可以删除DEK
USE DB_Encrypt_Demo
GO
Drop DATABASE ENCRYPTION KEY
Go