SQL Server 2005加密体系

SQL Server 2005中引入了一套完整的加密方法，具体的术语呢就偷个懒不写了，大家可以看BOL么。

大致的结构呢就是在安装sQL Server 2005的时候利用SQL Server服务账号生成一个服务主密钥Service Master Key，然后数据库的管理员可以在数据库上创建Database Master Key，当然也可以不创建，同时数据库管理员可以为Database User创建证书、对称密钥或者非对称密钥。这三种对象都可以用于加密用户数据，但一般推荐利用证书签署代码，利用证书或者非对称密钥加密对称密钥，利用对称密钥加密用户数据。

以下还是给一段代码，因为考虑到我跟王辉兄弟当时在成都宾馆里研究这个东西就是苦于找不到完整的范例代码，后来还是GTEC的徐强大拿给了个Link，然后我们又东拼西凑才出了一段代码，不敢独享，拿来与大家分享。

先给利用证书签署代码的范例，这段代码的好处是不用给Database User大的权限，就可以让用户修改部分数据，这也是SQL Server 2005中权限粒度化的一种表现：

--------------------------------------------------------------------------------

--创建实验用数据库USE masterIF EXISTS(SELECT [name] FROM sys.databases WHERE [name] = 'Sales')DROP DATABASE SalesCREATE DATABASE SalesIF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'ryan' AND [type] = 'S')DROP LOGIN ryanCREATE lOGIN ryan WITH PASSWORD = 'P@ssw0rd'IF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'teddy' AND [type] = 'S')DROP LOGIN teddyCREATE lOGIN teddy WITH PASSWORD = 'P@ssw0rd'

--创建用户ryan，并创建数据库主密钥USE SalesIF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = 'ryan' AND [type] = 'S')DROP User ryanCREATE USER ryan FOR LOGIN ryan WITH DEFAULT_SCHEMA = dboGO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'GO

--创建证书IF EXISTS(SELECT [name] FROM sys.certificates WHERE [name] = 'CERT_MAINTAIN')DROP CERTIFICATE CERT_MAINTAINCREATE CERTIFICATE CERT_MAINTAINWITH SUBJECT = 'Certificate For Database Maintainance',START_DATE = '01/01/2006',EXPIRY_DATE = '12/31/2015'GO

SELECT * FROM sys.certificates

--利用证书创建数据库用户，并授予该用户管理数据库用户的权限CREATE USER USER_MAINTAIN FOR CERTIFICATE CERT_MAINTAINGRANT ALTER ANY USER TO USER_MAINTAIN

--创建存储过程IF EXISTS(SELECT [name] FROM sys.procedures WHERE [name] = 'usp_AddUser')DROP PROCEDURE dbo.usp_AddUserGOCREATE PROCEDURE dbo.usp_AddUser@UserName varchar(50)ASIF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = @UserName AND [type] = 'S')EXEC ('DROP USER ' + @UserName)

EXEC ('CREATE USER ' + @UserName)GO

--完成准备工作，开始测试加密GRANT EXEC ON dbo.usp_AddUser TO ryanEXECUTE AS LOGIN = 'ryan'BEGIN TRYEXEC dbo.usp_AddUser 'teddy'END TRYBEGIN CATCHSELECT ERROR_MESSAGE() AS 'Error Msg'END CATCHREVERT

--利用证书签署存储过程代码ADD SIGNATURE TO dbo.usp_AddUser BY CERTIFICATE CERT_MAINTAINALTER CERTIFICATE CERT_MAINTAIN REMOVE PRIVATE KEY

--在此尝试执行脚本EXECUTE AS LOGIN = 'ryan'EXEC dbo.usp_AddUser 'teddy'REVERT

紧接上一篇，再给一段用密钥加密数据的范例，这段代码比较简单，大黄不准像蹂躏大余一样说我灌水！！！

--------------------------------------------------------------------------------

--创建实验用数据库USE masterIF EXISTS(SELECT [name] FROM sys.databases WHERE [name] = 'Sales')DROP DATABASE SalesCREATE DATABASE SalesIF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'ryan' AND [type] = 'S')DROP LOGIN ryanCREATE lOGIN ryan WITH PASSWORD = 'P@ssw0rd'IF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'teddy' AND [type] = 'S')DROP LOGIN teddyCREATE lOGIN teddy WITH PASSWORD = 'P@ssw0rd'GO

--创建用户ryan，并创建数据库主密钥USE SalesIF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = 'ryan' AND [type] = 'S')DROP User ryanCREATE USER ryan FOR LOGIN ryan WITH DEFAULT_SCHEMA = dboGOIF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = 'teddy' AND [type] = 'S')DROP User teddyCREATE USER teddy FOR LOGIN teddy WITH DEFAULT_SCHEMA = dboGOCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'GO--使用服务主密钥加密数据库主密钥，--在此删除，因为发现数据库主密钥创建时默认及利用服务主密钥加密--利用服务主密钥加密的数据库主密钥称为自动密钥管理--可以利用以下查询语句是否启用数据库主密钥的自动密钥管理SELECT [name], is_master_key_encrypted_by_server FROM sys.databases WHERE [name] = 'Sales'--以下语句用于启用数据库主密钥的自动管理--ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEYGO

--为ryan创建证书IF EXISTS(SELECT [name] FROM sys.certificates WHERE [name] = 'CERT_RYAN')DROP CERTIFICATE CERT_RYANCREATE CERTIFICATE CERT_RYAN AUTHORIZATION ryan--ENCRYPTION BY PASSWORD = 'P@ssw0rd' --建议不要使用密码，因为经过测试，是用密码的证书是利用密码保护， --而非数据库主密钥，可用以下语句测试证书的加密方法 --SELECT [name], pvt_key_encryption_type_desc FROM sys.certificates --WHERE [name] = 'CERT_DB'WITH SUBJECT = 'Certificate For Database',START_DATE = '01/01/2006',EXPIRY_DATE = '12/31/2015'GO--为teddy创建证书IF EXISTS(SELECT [name] FROM sys.certificates WHERE [name] = 'CERT_TEDDY')DROP CERTIFICATE CERT_TEDDYCREATE CERTIFICATE CERT_TEDDY AUTHORIZATION teddy--ENCRYPTION BY PASSWORD = 'P@ssw0rd' --建议不要使用密码，因为经过测试，是用密码的证书是利用密码保护， --而非数据库主密钥，可用以下语句测试证书的加密方法 --SELECT [name], pvt_key_encryption_type_desc FROM sys.certificates --WHERE [name] = 'CERT_DB'WITH SUBJECT = 'Certificate For Database',START_DATE = '01/01/2006',EXPIRY_DATE = '12/31/2015'GO

SELECT * FROM sys.certificates

--为ryan和teddy分别创建利用证书保护的对称密码CREATE SYMMETRIC KEY Key_SYM_RYAN AUTHORIZATION ryanWITH ALGORITHM = TRIPLE_DESENCRYPTION BY CERTIFICATE CERT_RYANGOCREATE SYMMETRIC KEY Key_SYM_TEDDY AUTHORIZATION teddyWITH ALGORITHM = TRIPLE_DESENCRYPTION BY CERTIFICATE CERT_TEDDYGO

--创建测试用表IF EXISTS(SELECT [name] FROM sys.tables WHERE [name] = 'encryption')DROP TABLE encryptionCREATE TABLE dbo.encryption (PT nchar(10),;;;;--Plain TextET varbinary(128),; --Encrypted Text)GOGRANT SELECT, INSERT ON encryption TO ryanGRANT SELECT, INSERT ON encryption TO teddy

--完成准备工作，开始测试加密EXECUTE AS LOGIN = 'ryan'OPEN SYMMETRIC KEY Key_SYM_RYAN DECRYPTION BY CERTIFICATE CERT_RYANINSERT INTO encryption VALUES (N'RYAN',EncryptByKey(Key_GUID('Key_SYM_RYAN'), N'RYAN'))CLOSE ALL SYMMETRIC KEYSREVERT

EXECUTE AS LOGIN = 'teddy'OPEN SYMMETRIC KEY Key_SYM_TEDDY DECRYPTION BY CERTIFICATE CERT_TEDDYINSERT INTO encryption VALUES (N'TEDDY',EncryptByKey(Key_GUID('Key_SYM_TEDDY'), N'TEDDY'))CLOSE ALL SYMMETRIC KEYSREVERT

--测试数据已经被加密SELECT * FROM encryption

--解密数据EXECUTE AS LOGIN = 'ryan'OPEN SYMMETRIC KEY Key_SYM_RYAN DECRYPTION BY CERTIFICATE CERT_RYANSELECT PT, CONVERT(nchar,DecryptByKey(ET)) AS ET FROM encryptionCLOSE ALL SYMMETRIC KEYSREVERTEXECUTE AS LOGIN = 'teddy'OPEN SYMMETRIC KEY Key_SYM_TEDDY DECRYPTION BY CERTIFICATE CERT_TEDDYSELECT PT, CONVERT(nchar,DecryptByKey(ET)) AS ET FROM encryptionCLOSE ALL SYMMETRIC KEYSREVERT

sp; --而非数据库主密钥，可用以下语句测试证书的加密方法 --SELECT [name], pvt_key_encryption_type_desc FROM sys.certificates --WHERE [name] = 'CERT_DB'WITH SUBJECT = 'Certificate For Database',START_DATE = '01/01/2006',EXPIRY_DATE = '12/31/2015'GO

SELECT * FROM sys.certificates

--为ryan和teddy分别创建利用证书保护的对称密码CREATE SYMMETRIC KEY Key_SYM_RYAN AUTHORIZATION ryanWITH ALGORITHM = TRIPLE_DESENCRYPTION BY CERTIFICATE CERT_RYANGOCREATE SYMMETRIC KEY Key_SYM_TEDDY AUTHORIZATION teddyWITH ALGORITHM = TRIPLE_DESENCRYPTION BY CERTIFICATE CERT_TEDDYGO

--创建测试用表IF EXISTS(SELECT [name] FROM sys.tables WHERE [name] = 'encryption')DROP TABLE encryptionCREATE TABLE dbo.encryption (