SQL Server 2005加密体系(一)

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 master
IF EXISTS(SELECT [name] FROM sys.databases WHERE [name] = 'Sales')
 DROP DATABASE Sales
CREATE DATABASE Sales
IF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'ryan' AND [type] = 'S')
 DROP LOGIN ryan
CREATE lOGIN ryan WITH PASSWORD = 'P@ssw0rd'
IF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'teddy' AND [type] = 'S')
 DROP LOGIN teddy
CREATE lOGIN teddy WITH PASSWORD = 'P@ssw0rd'

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

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

--创建证书
IF EXISTS(SELECT [name] FROM sys.certificates WHERE [name] = 'CERT_MAINTAIN')
 DROP CERTIFICATE CERT_MAINTAIN
CREATE CERTIFICATE CERT_MAINTAIN
 WITH 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_MAINTAIN
GRANT ALTER ANY USER TO USER_MAINTAIN

--创建存储过程
IF EXISTS(SELECT [name] FROM sys.procedures WHERE [name] = 'usp_AddUser')
 DROP PROCEDURE dbo.usp_AddUser
GO
CREATE PROCEDURE dbo.usp_AddUser
 @UserName varchar(50)
AS
IF 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 ryan
EXECUTE AS LOGIN = 'ryan'
BEGIN TRY
 EXEC dbo.usp_AddUser 'teddy'
END TRY
BEGIN CATCH
 SELECT ERROR_MESSAGE() AS 'Error Msg'
END CATCH
REVERT

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

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