/*[课程]使用数据库加密保护敏感数据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 GOCREATE 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 NorthwindSELECT * FROM sys.symmetric_keysGO--4.)对数据库主密钥进行备份USE NorthwindGOBACKUP 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 KEYGO--      查看数据库的加密状态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 KEYCLOSE MASTER KEY--==================(III)证书=====================--1.)让SQL2005创建自签名的证书USE NorthwindGOCREATE CERTIFICATE cert_TestCert1     ENCRYPTION BY PASSWORD = 'P@ssw0rd'    WITH SUBJECT = 'TestCert1',    START_DATE = '1/31/2006',    EXPIRY_DATE = '1/31/2008'GOSELECT * FROM sys.certificates--2.)从文件导入证书USE NorthwindGOCREATE CERTIFICATE cert_TestCert2    FROM FILE = 'C:\DBFile\MSCert.cer'GOSELECT * 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 @cipherSELECT CONVERT(varchar(200), DecryptByCert(Cert_ID('cert_TestCert1'), @cipher, N'P@ssw0rd')) AS [ClearText]--5.)删除证书私钥ALTER CERTIFICATE cert_TestCert1    REMOVE PRIVATE KEYGo--    加密成功，解密失败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 @cipherSELECT 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 NorthwindGOCREATE ASYMMETRIC KEY asy_Test      FROM FILE = 'C:\DBFile\asy_Test.key'      ENCRYPTION BY PASSWORD = 'P@ssw0rd'GOSELECT * 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.      加密存储过程执行返回的数据结果集