[转载]SQL Server 2005加密体系
<p>信息来源:<a id="ArticleTitle1_ArticleTitle1_AuthorLink" href="/user/softj">softj</a>'s Blog</p><p>SQL Server 2005中引入了一套完整的加密方法，具体的术语呢就偷个懒不写了，大家可以看BOL么。</p><p>大致的结构呢就是在安装sQL Server 2005的时候利用SQL Server服务账号生成一个服务主密钥Service Master Key，然后数据库的管理员可以在数据库上创建Database Master Key，当然也可以不创建，同时数据库管理员可以为Database User创建证书、对称密钥或者非对称密钥。这三种对象都可以用于加密用户数据，但一般推荐利用证书签署代码，利用证书或者非对称密钥加密对称密钥，利用对称密钥加密用户数据。</p><p>以下还是给一段代码，因为考虑到我跟王辉兄弟当时在成都宾馆里研究这个东西就是苦于找不到完整的范例代码，后来还是GTEC的徐强大拿给了个Link，然后我们又东拼西凑才出了一段代码，不敢独享，拿来与大家分享。</p><p>先给利用证书签署代码的范例，这段代码的好处是不用给Database User大的权限，就可以让用户修改部分数据，这也是SQL Server 2005中权限粒度化的一种表现：</p><p>--------------------------------------------------------------------------------</p><p>--创建实验用数据库<br />USE master<br />IF EXISTS(SELECT [name] FROM sys.databases WHERE [name] = 'Sales')<br />DROP DATABASE Sales<br />CREATE DATABASE Sales<br />IF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'ryan' AND [type] = 'S')<br />DROP LOGIN ryan<br />CREATE lOGIN ryan WITH PASSWORD = <a href="mailto:'P@ssw0rd'">'P@ssw0rd'</a><br />IF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'teddy' AND [type] = 'S')<br />DROP LOGIN teddy<br />CREATE lOGIN teddy WITH PASSWORD = <a href="mailto:'P@ssw0rd'">'P@ssw0rd'</a></p><p>--创建用户ryan，并创建数据库主密钥<br />USE Sales<br />IF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = 'ryan' AND [type] = 'S')<br />DROP User ryan<br />CREATE USER ryan FOR LOGIN ryan WITH DEFAULT_SCHEMA = dbo<br />GO</p><p>CREATE MASTER KEY ENCRYPTION BY PASSWORD = <a href="mailto:'P@ssw0rd'">'P@ssw0rd'</a><br />GO</p><p>--创建证书<br />IF EXISTS(SELECT [name] FROM sys.certificates WHERE [name] = 'CERT_MAINTAIN')<br />DROP CERTIFICATE CERT_MAINTAIN<br />CREATE CERTIFICATE CERT_MAINTAIN<br />WITH SUBJECT = 'Certificate For Database Maintainance',<br />START_DATE = '01/01/2006',<br />EXPIRY_DATE = '12/31/2015'<br />GO</p><p>SELECT * FROM sys.certificates</p><p>--利用证书创建数据库用户，并授予该用户管理数据库用户的权限<br />CREATE USER USER_MAINTAIN FOR CERTIFICATE CERT_MAINTAIN<br />GRANT ALTER ANY USER TO USER_MAINTAIN</p><p>--创建存储过程<br />IF EXISTS(SELECT [name] FROM sys.procedures WHERE [name] = 'usp_AddUser')<br />DROP PROCEDURE dbo.usp_AddUser<br />GO<br />CREATE PROCEDURE dbo.usp_AddUser<br />@UserName varchar(50)<br />AS<br />IF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = @UserName AND [type] = 'S')<br />EXEC ('DROP USER ' + @UserName)</p><p>EXEC ('CREATE USER ' + @UserName)<br />GO</p><p>--完成准备工作，开始测试加密<br />GRANT EXEC ON dbo.usp_AddUser TO ryan<br />EXECUTE AS LOGIN = 'ryan'<br />BEGIN TRY<br />EXEC dbo.usp_AddUser 'teddy'<br />END TRY<br />BEGIN CATCH<br />SELECT ERROR_MESSAGE() AS 'Error Msg'<br />END CATCH<br />REVERT</p><p>--利用证书签署存储过程代码<br />ADD SIGNATURE TO dbo.usp_AddUser BY CERTIFICATE CERT_MAINTAIN<br />ALTER CERTIFICATE CERT_MAINTAIN REMOVE PRIVATE KEY</p><p>--在此尝试执行脚本<br />EXECUTE AS LOGIN = 'ryan'<br />EXEC dbo.usp_AddUser 'teddy'<br />REVERT</p><p>紧接上一篇，再给一段用密钥加密数据的范例，这段代码比较简单，大黄不准像蹂躏大余一样说我灌水！！！</p><p>--------------------------------------------------------------------------------</p><p><br />--创建实验用数据库<br />USE master<br />IF EXISTS(SELECT [name] FROM sys.databases WHERE [name] = 'Sales')<br />DROP DATABASE Sales<br />CREATE DATABASE Sales<br />IF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'ryan' AND [type] = 'S')<br />DROP LOGIN ryan<br />CREATE lOGIN ryan WITH PASSWORD = <a href="mailto:'P@ssw0rd'">'P@ssw0rd'</a><br />IF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = 'teddy' AND [type] = 'S')<br />DROP LOGIN teddy<br />CREATE lOGIN teddy WITH PASSWORD = <a href="mailto:'P@ssw0rd'">'P@ssw0rd'</a><br />GO</p><p>--创建用户ryan，并创建数据库主密钥<br />USE Sales<br />IF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = 'ryan' AND [type] = 'S')<br />DROP User ryan<br />CREATE USER ryan FOR LOGIN ryan WITH DEFAULT_SCHEMA = dbo<br />GO<br />IF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = 'teddy' AND [type] = 'S')<br />DROP User teddy<br />CREATE USER teddy FOR LOGIN teddy WITH DEFAULT_SCHEMA = dbo<br />GO<br />CREATE MASTER KEY ENCRYPTION BY PASSWORD = <a href="mailto:'P@ssw0rd'">'P@ssw0rd'</a><br />GO<br />--使用服务主密钥加密数据库主密钥，<br />--在此删除，因为发现数据库主密钥创建时默认及利用服务主密钥加密<br />--利用服务主密钥加密的数据库主密钥称为自动密钥管理<br />--可以利用以下查询语句是否启用数据库主密钥的自动密钥管理<br />SELECT [name], is_master_key_encrypted_by_server FROM sys.databases WHERE [name] = 'Sales'<br />--以下语句用于启用数据库主密钥的自动管理<br />--ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY<br />GO</p><p>--为ryan创建证书<br />IF EXISTS(SELECT [name] FROM sys.certificates WHERE [name] = 'CERT_RYAN')<br />DROP CERTIFICATE CERT_RYAN<br />CREATE CERTIFICATE CERT_RYAN AUTHORIZATION ryan<br />--ENCRYPTION BY PASSWORD = <a href="mailto:'P@ssw0rd'">'P@ssw0rd'</a><br /> --建议不要使用密码，因为经过测试，是用密码的证书是利用密码保护，<br /> --而非数据库主密钥，可用以下语句测试证书的加密方法<br /> --SELECT [name], pvt_key_encryption_type_desc FROM sys.certificates <br /> --WHERE [name] = 'CERT_DB'<br />WITH SUBJECT = 'Certificate For Database',<br />START_DATE = '01/01/2006',<br />EXPIRY_DATE = '12/31/2015'<br />GO<br />--为teddy创建证书<br />IF EXISTS(SELECT [name] FROM sys.certificates WHERE [name] = 'CERT_TEDDY')<br />DROP CERTIFICATE CERT_TEDDY<br />CREATE CERTIFICATE CERT_TEDDY AUTHORIZATION teddy<br />--ENCRYPTION BY PASSWORD = <a href="mailto:'P@ssw0rd'">'P@ssw0rd'</a><br /> --建议不要使用密码，因为经过测试，是用密码的证书是利用密码保护，<br /> --而非数据库主密钥，可用以下语句测试证书的加密方法<br /> --SELECT [name], pvt_key_encryption_type_desc FROM sys.certificates <br /> --WHERE [name] = 'CERT_DB'<br />WITH SUBJECT = 'Certificate For Database',<br />START_DATE = '01/01/2006',<br />EXPIRY_DATE = '12/31/2015'<br />GO</p><p>SELECT * FROM sys.certificates</p><p>--为ryan和teddy分别创建利用证书保护的对称密码<br />CREATE SYMMETRIC KEY Key_SYM_RYAN AUTHORIZATION ryan<br />WITH ALGORITHM = TRIPLE_DES<br />ENCRYPTION BY CERTIFICATE CERT_RYAN<br />GO<br />CREATE SYMMETRIC KEY Key_SYM_TEDDY AUTHORIZATION teddy<br />WITH ALGORITHM = TRIPLE_DES<br />ENCRYPTION BY CERTIFICATE CERT_TEDDY<br />GO</p><p>--创建测试用表<br />IF EXISTS(SELECT [name] FROM sys.tables WHERE [name] = 'encryption')<br />DROP TABLE encryption<br />CREATE TABLE dbo.encryption <br />(<br />PT nchar(10), --Plain Text<br />ET varbinary(128), --Encrypted Text<br />)<br />GO<br />GRANT SELECT, INSERT ON encryption TO ryan<br />GRANT SELECT, INSERT ON encryption TO teddy</p><p>--完成准备工作，开始测试加密<br />EXECUTE AS LOGIN = 'ryan'<br />OPEN SYMMETRIC KEY Key_SYM_RYAN DECRYPTION BY CERTIFICATE CERT_RYAN<br />INSERT INTO encryption <br />VALUES (N'RYAN',EncryptByKey(Key_GUID('Key_SYM_RYAN'), N'RYAN'))<br />CLOSE ALL SYMMETRIC KEYS<br />REVERT</p><p>EXECUTE AS LOGIN = 'teddy'<br />OPEN SYMMETRIC KEY Key_SYM_TEDDY DECRYPTION BY CERTIFICATE CERT_TEDDY<br />INSERT INTO encryption <br />VALUES (N'TEDDY',EncryptByKey(Key_GUID('Key_SYM_TEDDY'), N'TEDDY'))<br />CLOSE ALL SYMMETRIC KEYS<br />REVERT</p><p>--测试数据已经被加密<br />SELECT * FROM encryption</p><p>--解密数据<br />EXECUTE AS LOGIN = 'ryan'<br />OPEN SYMMETRIC KEY Key_SYM_RYAN DECRYPTION BY CERTIFICATE CERT_RYAN<br />SELECT PT, CONVERT(nchar,DecryptByKey(ET)) AS ET FROM encryption<br />CLOSE ALL SYMMETRIC KEYS<br />REVERT<br />EXECUTE AS LOGIN = 'teddy'<br />OPEN SYMMETRIC KEY Key_SYM_TEDDY DECRYPTION BY CERTIFICATE CERT_TEDDY<br />SELECT PT, CONVERT(nchar,DecryptByKey(ET)) AS ET FROM encryption<br />CLOSE ALL SYMMETRIC KEYS<br />REVERT</p><p><br /></p>页:
[1]