数据来源：牛可
微软TechNet
讲述SQL Server 2005的数据加密功能和配置以及如何通过它实现对敏感数据的保护。
演示用的脚本提供给大家作为参考：
/*
[课程]使用数据库加密保护敏感数据
DEMO 1
了解SQL2005加密层次结构
[过程]
过程一共分为4个部分
*/
-- ==================(I)服务主密钥=====================
-- 1.)备份服务主密钥到文件
BACKUP SERVICE MASTER KEY TO FILE = ' C:/DBFile/SMK.bak '
ENCRYPTION BY PASSWORD = ' P@ssw0rd '
-- 2.)生成新的服务主密钥
ALTER SERVICE MASTER KEY REGENERATE;
GO
-- 3.)从备份文件还原服务主密钥
RESTORE SERVICE MASTER KEY FROM FILE = ' C:/DBFile/SMK.bak '
DECRYPTION BY PASSWORD = ' P@ssw0rd '
-- ==================(II)数据库主密钥=====================
-- 1.)为Northwind数据库创建数据库主密钥
USE Northwind
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' P@ssw0rd '
GO
-- 2.)查看数据库加密状态
SELECT [ name ] , is_master_key_encrypted_by_server
FROM sys.databases WHERE name = ' Northwind ' ;
GO
-- 3.)查看数据库主密钥的信息
USE Northwind
SELECT * FROM sys.symmetric_keys
GO
-- 4.)对数据库主密钥进行备份
USE Northwind
GO
BACKUP MASTER KEY
TO FILE = ' C:/DBFile/DMK.bak '
ENCRYPTION BY PASSWORD = ' P@ssw0rd!@ '
GO
-- 5.)删除服务主密钥对数据库主密钥的保护
-- 创建非对称密钥成功，自动使用服务主密钥解密并使用该数据库主密钥
CREATE ASYMMETRIC KEY asy_TestKey1 WITH ALGORITHM = RSA_1024
GO
-- 删除服务主密钥对数据库主密钥的保护
ALTER MASTER KEY
DROP ENCRYPTION BY SERVICE MASTER KEY
GO
-- 查看数据库的加密状态
SELECT [ name ] , is_master_key_encrypted_by_server
FROM sys.databases WHERE name = ' Northwind ' ;
-- 创建非对称密钥失败，数据库主密钥未打开
CREATE ASYMMETRIC KEY asy_TestKey2 WITH ALGORITHM = RSA_1024
GO
-- 打开数据库主密钥未
OPEN MASTER KEY DECRYPTION BY PASSWORD = ' P@ssw0rd '
SELECT * FROM sys.openkeys
-- 创建非对称密钥成功
CREATE ASYMMETRIC KEY asy_TestKey2 WITH ALGORITHM = RSA_1024
GO
-- 恢复服务主密钥对数据库主密钥的保护
ALTER MASTER KEY
ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
-- ==================(III)证书=====================
-- 1.)让SQL2005创建自签名的证书
USE Northwind
GO
CREATE CERTIFICATE cert_TestCert1
ENCRYPTION BY PASSWORD = ' P@ssw0rd '
WITH SUBJECT = ' TestCert1 ' ,
START_DATE = ' 1/31/2006 ' ,
EXPIRY_DATE = ' 1/31/2008 '
GO
SELECT * FROM sys.certificates
-- 2.)从文件导入证书
USE Northwind
GO
CREATE CERTIFICATE cert_TestCert2
FROM FILE = ' C:/DBFile/MSCert.cer '
GO
SELECT * FROM sys.certificates
-- 3.)备份导出证书和私钥
BACKUP CERTIFICATE cert_TestCert1
TO FILE = ' c:/DBFile/TestCert1.cer '
WITH PRIVATE KEY
(DECRYPTION BY PASSWORD = ' P@ssw0rd ' ,
FILE = ' c:/DBFile/TestCert1_pvt ' ,
ENCRYPTION BY PASSWORD = ' Pa$w0rd ' )
-- 4.)使用证书加密、解密数据
DECLARE @cleartext varbinary ( 200 )
DECLARE @cipher varbinary ( 200 )
SET @cleartext = CONVERT ( varbinary ( 200 ), ' Test text string ' )
SET @cipher = EncryptByCert(Cert_ID( ' cert_TestCert1 ' ), @cleartext)
SELECT @cipher
SELECT CONVERT ( varchar ( 200 ), DecryptByCert(Cert_ID( ' cert_TestCert1 ' ), @cipher, N ' P@ssw0rd ' )) AS [ ClearText ]
-- 5.)删除证书私钥
ALTER CERTIFICATE cert_TestCert1
REMOVE PRIVATE KEY
Go
-- 加密成功，解密失败
DECLARE @cleartext varbinary ( 200 )
DECLARE @cipher varbinary ( 200 )
SET @cleartext = CONVERT ( varbinary ( 200 ), ' Test text string ' )
SET @cipher = EncryptByCert(Cert_ID( ' cert_TestCert1 ' ), @cleartext)
SELECT @cipher
SELECT CONVERT ( varchar ( 200 ), DecryptByCert(Cert_ID( ' cert_TestCert1 ' ), @cipher, N ' P@ssw0rd ' )) AS [ ClearText ]
-- ==================(IV)非对称密钥=====================
-- 1.)使用sn.ext生成非对成密钥文件
-- sn -k C:/DBFile/asy_Test.key
-- 2.)从文件创建非对称密钥
USE Northwind
GO
CREATE ASYMMETRIC KEY asy_Test
FROM FILE = ' C:/DBFile/asy_Test.key '
ENCRYPTION BY PASSWORD = ' P@ssw0rd '
GO
SELECT * FROM sys.asymmetric_keys
/*
[课程]使用数据库加密保护敏感数据
DEMO 2
使用密钥对列数据进行加密
[过程]
过程一共分为4个部分
*/
-- ==================(I)准备=====================
-- 1.)创建示例表
USE Northwind
IF EXIST dbo.EmpSalary DROP TABLE dbo.EmpSalary;
CREATE TABLE dbo.EmpSalary(
EmpID int ,
Title nvarchar ( 50 ),
Salary varbinary ( 500 )
)
GO
-- 2.)创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' P@ssw0rd '
GO
-- 3.)
-- 4.)创建用于加密的对称密钥
CREATE SYMMETRIC KEY sym_Salary
WITH ALGORITHM = AES_192
ENCRYPTION BY PASSWORD = ' P@ssw0rd ' ;
SELECT * FROM sys.symmetric_keys WHERE [ name ] = ' sym_Salary '
-- ==================(II)加密列数据=====================
-- 1.)打开对称密钥
OPEN SYMMETRIC KEY sym_Salary
DECRYPTION BY PASSWORD = ' P@ssw0rd '
SELECT * FROM sys.openkeys -- 查看打开的对称密钥
-- 2.)向表中插入数据，并对Salary列的数据进行加密
INSERT INTO EmpSalary VALUES ( 1 , ' CEO ' , EncryptByKey(KEY_GUID( ' sym_Salary ' ), ' 20000 ' ))
INSERT INTO EmpSalary VALUES ( 2 , ' Manager ' , EncryptByKey(KEY_GUID( ' sym_Salary ' ), ' 10000 ' ))
INSERT INTO EmpSalary VALUES ( 3 , ' DB Admin ' , EncryptByKey(KEY_GUID( ' sym_Salary ' ), ' 5000 ' ))
-- 3.)关闭打开的对称密钥
CLOSE SYMMETRIC KEY sym_Salary
SELECT * FROM sys.openkeys -- 查看打开的对称密钥
-- 4.)查看表中存放的数据
SELECT * FROM EmpSalary
-- ==================(III)解密并访问被加密了的数据列=====================
-- 1.)打开对称密钥
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = ' P@ssw0rd '
-- 2.)使用对称密钥解密并访问被加密了的数据列
SELECT EmpID, Title, CAST (DecryptBykey(Salary) AS VARCHAR ( 20 )) AS Salary FROM EmpSalary
-- 3.)关闭对称密钥
CLOSE SYMMETRIC KEY sym_Salary
-- ==================(III)绕过加密数据的攻击=====================
-- 1.)攻击者使用其它数据行的加密数据替换某一行的数据
SELECT * FROM EmpSalary
UPDATE EmpSalary SET Salary =
( SELECT Salary FROM EmpSalary WHERE EmpID = 1 )
WHERE EmpID = 3
-- 2.)查看被攻击后解密的数据
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = ' P@ssw0rd '
SELECT EmpID, Title, CAST (DecryptBykey(Salary) AS VARCHAR ( 20 )) AS Salary FROM EmpSalary
CLOSE SYMMETRIC KEY sym_Salary
-- ==================(IV)使用验证器防止绕过加密数据的攻击=====================
-- 1.)删除前面添加的数据行
DELETE FROM EmpSalary
-- 2.)向表中插入数据，并对Salary列的数据使用验证器进行加密，第四个参数是加密因子
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = ' P@ssw0rd '
INSERT INTO EmpSalary VALUES ( 1 , ' CEO ' , EncryptByKey(KEY_GUID( ' sym_Salary ' ), ' 20000 ' , 1 , ' 1 ' ))
INSERT INTO EmpSalary VALUES ( 2 , ' Manager ' , EncryptByKey(KEY_GUID( ' sym_Salary ' ), ' 10000 ' , 1 , ' 2 ' ))
INSERT INTO EmpSalary VALUES ( 3 , ' DB Admin ' , EncryptByKey(KEY_GUID( ' sym_Salary ' ), ' 5000 ' , 1 , ' 3 ' ))
CLOSE SYMMETRIC KEY sym_Salary
-- 3.)解密并访问被加密了的数据列
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = ' P@ssw0rd '
SELECT EmpID, Title, CAST (DecryptBykey(Salary, 1 , CAST (EmpID AS VARCHAR ( 3 ))) AS VARCHAR ( 20 )) AS Salary FROM EmpSalary
CLOSE SYMMETRIC KEY sym_Salary
-- 4.)攻击者使用相同的方法篡改数据
SELECT * FROM EmpSalary
UPDATE EmpSalary SET Salary =
( SELECT Salary FROM EmpSalary WHERE EmpID = 1 )
WHERE EmpID = 3
-- 5.)被篡改后的加密了的数据列变成无效
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = ' P@ssw0rd '
SELECT EmpID, Title, CAST (DecryptBykey(Salary, 1 , CAST (EmpID AS VARCHAR ( 3 ))) AS VARCHAR ( 20 )) AS Salary FROM EmpSalary
CLOSE SYMMETRIC KEY sym_Salary
/*
[课程]使用数据库加密保护敏感数据
DEMO 3
使用证书签署存储过程
[过程]
过程一共分为2个部分
*/
-- ==================(I)示例准备=====================
-- 1.)创建数据库主密钥
USE Northwind
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' P@ssw0rd '
-- 2.)创建签署存储过程所需要的证书
CREATE CERTIFICATE cert_Products
WITH SUBJECT = ' Products Sign ' ,
START_DATE = ' 2006/1/1 ' ,
EXPIRY_DATE = ' 2008/1/1 '
-- 3.)创建SPDeveloper登录帐户和用户，该用户创建访问Products表的存储过程
CREATE LOGIN [ SPDeveloper ] WITH PASSWORD = N ' P@ssw0rd ' , DEFAULT_DATABASE = [ Northwind ]
GO
CREATE USER [ SPDeveloper ] FOR LOGIN SPDeveloper WITH DEFAULT_SCHEMA = [ SPDeveloper ]
GO
CREATE SCHEMA products AUTHORIZATION SPDeveloper
GO
EXEC sp_addrolemember @rolename = ' db_owner ' , @membername = ' SPDeveloper '
-- 4.)以SPDeveloper的身份创建存储过程products.usp_Products
EXECUTE AS USER = ' SPDeveloper '
GO
CREATE PROCEDURE products.usp_Products
AS
SELECT TOP 5 * FROM dbo.Products
GO
REVERT
SELECT USER
-- 4.)创建普通用户Jerry
CREATE LOGIN jerry WITH PASSWORD = N ' P@ssw0rd ' , DEFAULT_DATABASE = [ Northwind ]
CREATE USER jerry FOR LOGIN jerry
-- ==================(II)使用证书签署存储过程=====================
-- 1.)授予用户Jerry执行存储过程的权限
GRANT EXECUTE ON products.usp_Products TO jerry
-- 2.)以Jerry的身份执行存储过程失败，因为拥有全链是断裂的
EXECUTE AS USER = ' jerry '
SELECT USER
GO
EXECUTE products.usp_Products
GO
REVERT
-- 3.)使用证书在当前数据库创建用户ProductsReader，
-- 并为该用户赋予读取Products表的权限
CREATE USER ProductsReader FOR CERTIFICATE cert_Products
GO
GRANT SELECT ON Products TO ProductsReader
-- 4.)使用证书签署当前存储过程
ADD SIGNATURE TO products.usp_Products BY CERTIFICATE cert_Products
-- 4.)以Jerry的身份重新执行存储过程，成功，
-- 因为存储过程将以ProductsReader的权限上下文执行
EXECUTE AS USER = ' jerry '
SELECT USER
GO
EXECUTE products.usp_Products
讲师: 牛可
时间: 2006年8月9日 10:00--11:30
产品: SQL Server
技术等级: 200
欢迎大家积极参与讨论课后问题及答案
1.
在SQL Server 2005中，数据库的主密钥可以直接用来加密保护：（AB）
A.
证书的私钥
B.
非对称密钥的私钥
C.
非对称密钥的公钥
D.
服务主密钥
2.
当采用加密技术来保护数据库中的大量敏感数据时，为了兼顾性能和数据的安全性，最佳的做法是：（C）
A.
使用证书加密所有敏感数据，并用对称密钥加密保护证书的私钥
B.
使用非对称密钥的公钥加密所有敏感数据，并用对称密钥加密保护该密钥对的私钥
C.
使用对称密钥加密所有敏感数据，并用证书加密保护该对称密钥
D.
使用非对称密钥的私钥加密所有敏感数据，并用证书加密保护该密钥对的公钥
3.
在
SQL Server 2005
中使用证书签署存储过程的目的是：（D）
A.
确保只有拥有该证书对应私钥的用户才能执行该存储过程
B.
加密存储过程，防止其它人查看到存储过程中的
T-SQL
语句
C.
加密存储过程执行返回的数据结果集
D.
让该存储过程以证书所对应的数据库用户的权限执行
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
