• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

SQL Server 2005加密体系(二)_sqlserver

sqlserver 搞代码 3年前 (2018-06-16) 101次浏览 已收录 0个评论

SQL Server 2005加密体系(二)
紧接上一篇,再给一段用密钥加密数据的范例,这段代码比较简单,大黄不准像蹂躏大余一样说我灌水!!!

——————————————————————————–

–创建实验用数据库
USE master
IF EXISTS(SELECT [name] FROM sys.databases WHERE [name] = ‘Sales’)
 DROP DATABASE Sales

http://www.gaodaima.com/36188.htmlSQL Server 2005加密体系(二)_sqlserver

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 = ‘[email protected]
IF EXISTS(SELECT principal_id FROM sys.server_principals WHERE [name] = ‘teddy’ AND [type] = ‘S’)
 DROP LOGIN teddy
CREATE lOGIN teddy WITH PASSWORD = ‘[email protected]
GO

–创建用户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
IF EXISTS(SELECT * FROM sys.database_principals WHERE [name] = ‘teddy’ AND [type] = ‘S’)
 DROP User teddy
CREATE USER teddy FOR LOGIN teddy WITH DEFAULT_SCHEMA = dbo
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘[email protected]
GO
–使用服务主密钥加密数据库主密钥,
–在此删除,因为发现数据库主密钥创建时默认及利用服务主密钥加密
–利用服务主密钥加密的数据库主密钥称为自动密钥管理
–可以利用以下查询语句是否启用数据库主密钥的自动密钥管理
SELECT [name], is_master_key_encrypted_by_server FROM sys.databases WHERE [name] = ‘Sales’
–以下语句用于启用数据库主密钥的自动管理
–ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

–为ryan创建证书
IF EXISTS(SELECT [name] FROM sys.certificates WHERE [name] = ‘CERT_RYAN’)
 DROP CERTIFICATE CERT_RYAN
CREATE CERTIFICATE CERT_RYAN AUTHORIZATION ryan
 –ENCRYPTION BY PASSWORD = ‘[email protected]
   –建议不要使用密码,因为经过测试,是用密码的证书是利用密码保护,
   –而非数据库主密钥,可用以下语句测试证书的加密方法
   –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_TEDDY
CREATE CERTIFICATE CERT_TEDDY AUTHORIZATION teddy
 –ENCRYPTION BY PASSWORD = ‘[email protected]
   –建议不要使用密码,因为经过测试,是用密码的证书是利用密码保护,
   –而非数据库主密钥,可用以下语句测试证书的加密方法
   –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 ryan
 WITH ALGORITHM = TRIPLE_DES
 ENCRYPTION BY CERTIFICATE CERT_RYAN
GO
CREATE SYMMETRIC KEY Key_SYM_TEDDY AUTHORIZATION teddy
 WITH ALGORITHM = TRIPLE_DES
 ENCRYPTION BY CERTIFICATE CERT_TEDDY
GO

–创建测试用表
IF EXISTS(SELECT [name] FROM sys.tables WHERE [name] = ‘encryption’)
 DROP TABLE encryption
CREATE TABLE dbo.encryption
(
 PT nchar(10),       –Plain Text
 ET varbinary(128),  –Encrypted Text
)
GO
GRANT SELECT, INSERT ON encryption TO ryan
GRANT SELECT, INSERT ON encryption TO teddy

–完成准备工作,开始测试加密
EXECUTE AS LOGIN = ‘ryan’
OPEN SYMMETRIC KEY Key_SYM_RYAN DECRYPTION BY CERTIFICATE CERT_RYAN
INSERT INTO encryption
 VALUES (N’RYAN’,EncryptByKey(Key_GUID(‘Key_SYM_RYAN’), N’RYAN’))
CLOSE ALL SYMMETRIC KEYS
REVERT

EXECUTE AS LOGIN = ‘teddy’
OPEN SYMMETRIC KEY Key_SYM_TEDDY DECRYPTION BY CERTIFICATE CERT_TEDDY
INSERT INTO encryption
 VALUES (N’TEDDY’,EncryptByKey(Key_GUID(‘Key_SYM_TEDDY’), N’TEDDY’))
CLOSE ALL SYMMETRIC KEYS
REVERT

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

解密数据
EXECUTE AS LOGIN = ‘ryan’
OPEN SYMMETRIC KEY Key_SYM_RYAN DECRYPTION BY CERTIFICATE CERT_RYAN
SELECT PT, CONVERT(nchar,DecryptByKey(ET)) AS ET FROM encryption
CLOSE ALL SYMMETRIC KEYS
REVERT
EXECUTE AS LOGIN = ‘teddy’
OPEN SYMMETRIC KEY Key_SYM_TEDDY DECRYPTION BY CERTIFICATE CERT_TEDDY
SELECT PT, CONVERT(nchar,DecryptByKey(ET)) AS ET FROM encryption
CLOSE ALL SYMMETRIC KEYS
REVERT

 

欢迎大家阅读《SQL Server 2005加密体系(二)_sqlserver,跪求各位点评,若觉得好的话请收藏本文,by 搞代码


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址