--创建实验用数据库 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' 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 = '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 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 = '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_TEDDY CREATE 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 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 |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-29 23:22 , Processed in 0.184214 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.