/*[課程]使用數(shù)據(jù)庫(kù)加密保護(hù)敏感數(shù)據(jù)DEMO 1了解SQL2005加密層次結(jié)構(gòu)[過(guò)程]過(guò)程一共分為4個(gè)部分*/--==================(I)服務(wù)主密鑰=====================--1.)備份服務(wù)主密鑰到文件BACKUP SERVICE MASTER KEY TO FILE = 'C:\DBFile\SMK.bak'ENCRYPTION BY PASSWORD = 'P@ssw0rd'--2.)生成新的服務(wù)主密鑰ALTER SERVICE MASTER KEY REGENERATE;GO--3.)從備份文件還原服務(wù)主密鑰RESTORE SERVICE MASTER KEY FROM FILE = 'C:\DBFile\SMK.bak' DECRYPTION BY PASSWORD = 'P@ssw0rd'--==================(II)數(shù)據(jù)庫(kù)主密鑰=====================--1.)為Northwind數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)主密鑰USE Northwind GOCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'GO--2.)查看數(shù)據(jù)庫(kù)加密狀態(tài)SELECT [name], is_master_key_encrypted_by_server     FROM sys.databases WHERE name = 'Northwind';GO--3.)查看數(shù)據(jù)庫(kù)主密鑰的信息USE NorthwindSELECT * FROM sys.symmetric_keysGO--4.)對(duì)數(shù)據(jù)庫(kù)主密鑰進(jìn)行備份USE NorthwindGOBACKUP MASTER KEY     TO FILE = 'C:\DBFile\DMK.bak'    ENCRYPTION BY PASSWORD = 'P@ssw0rd!@'GO--5.)刪除服務(wù)主密鑰對(duì)數(shù)據(jù)庫(kù)主密鑰的保護(hù)--     創(chuàng)建非對(duì)稱密鑰成功，自動(dòng)使用服務(wù)主密鑰解密并使用該數(shù)據(jù)庫(kù)主密鑰CREATE ASYMMETRIC KEY asy_TestKey1 WITH ALGORITHM = RSA_1024 GO--     刪除服務(wù)主密鑰對(duì)數(shù)據(jù)庫(kù)主密鑰的保護(hù)ALTER MASTER KEY     DROP ENCRYPTION BY SERVICE MASTER KEYGO--      查看數(shù)據(jù)庫(kù)的加密狀態(tài)SELECT [name], is_master_key_encrypted_by_server     FROM sys.databases WHERE name = 'Northwind';--     創(chuàng)建非對(duì)稱密鑰失敗，數(shù)據(jù)庫(kù)主密鑰未打開(kāi)CREATE ASYMMETRIC KEY asy_TestKey2 WITH ALGORITHM = RSA_1024 GO--     打開(kāi)數(shù)據(jù)庫(kù)主密鑰未OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd'SELECT * FROM sys.openkeys--     創(chuàng)建非對(duì)稱密鑰成功CREATE ASYMMETRIC KEY asy_TestKey2 WITH ALGORITHM = RSA_1024 GO--     恢復(fù)服務(wù)主密鑰對(duì)數(shù)據(jù)庫(kù)主密鑰的保護(hù)ALTER MASTER KEY     ADD ENCRYPTION BY SERVICE MASTER KEYCLOSE MASTER KEY--==================(III)證書(shū)=====================--1.)讓SQL2005創(chuàng)建自簽名的證書(shū)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.)從文件導(dǎo)入證書(shū)USE NorthwindGOCREATE CERTIFICATE cert_TestCert2    FROM FILE = 'C:\DBFile\MSCert.cer'GOSELECT * FROM sys.certificates--3.)備份導(dǎo)出證書(shū)和私鑰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.)使用證書(shū)加密、解密數(shù)據(jù)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.)刪除證書(shū)私鑰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)非對(duì)稱密鑰=====================--1.)使用sn.ext生成非對(duì)成密鑰文件--     sn -k C:\DBFile\asy_Test.key--2.)從文件創(chuàng)建非對(duì)稱密鑰USE NorthwindGOCREATE ASYMMETRIC KEY asy_Test      FROM FILE = 'C:\DBFile\asy_Test.key'      ENCRYPTION BY PASSWORD = 'P@ssw0rd'GOSELECT * FROM sys.asymmetric_keys

 /*
[課程]使用數(shù)據(jù)庫(kù)加密保護(hù)敏感數(shù)據(jù)

DEMO 2
使用密鑰對(duì)列數(shù)據(jù)進(jìn)行加密

[過(guò)程]
過(guò)程一共分為4個(gè)部分

*/

--==================(I)準(zhǔn)備=====================
--1.)創(chuàng)建示例表
USE Northwind
IF EXIST dbo.EmpSalary DROP TABLE dbo.EmpSalary;

CREATE TABLE dbo.EmpSalary(
    EmpID int,
    Title nvarchar(50),
    Salary varbinary(500)
)
GO

--2.)創(chuàng)建數(shù)據(jù)庫(kù)主密鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO

--3.)

--4.)創(chuàng)建用于加密的對(duì)稱密鑰
CREATE SYMMETRIC KEY sym_Salary
    WITH ALGORITHM = AES_192
    ENCRYPTION BY PASSWORD = 'P@ssw0rd';

SELECT * FROM sys.symmetric_keys WHERE [name] = 'sym_Salary'

--==================(II)加密列數(shù)據(jù)=====================

--1.)打開(kāi)對(duì)稱密鑰
OPEN SYMMETRIC KEY sym_Salary
    DECRYPTION BY PASSWORD = 'P@ssw0rd'

SELECT * FROM sys.openkeys        --查看打開(kāi)的對(duì)稱密鑰

--2.)向表中插入數(shù)據(jù)，并對(duì)Salary列的數(shù)據(jù)進(jìn)行加密
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.)關(guān)閉打開(kāi)的對(duì)稱密鑰
CLOSE SYMMETRIC KEY sym_Salary

SELECT * FROM sys.openkeys        --查看打開(kāi)的對(duì)稱密鑰

--4.)查看表中存放的數(shù)據(jù)
SELECT * FROM EmpSalary           

--==================(III)解密并訪問(wèn)被加密了的數(shù)據(jù)列=====================
--1.)打開(kāi)對(duì)稱密鑰
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = 'P@ssw0rd'

--2.)使用對(duì)稱密鑰解密并訪問(wèn)被加密了的數(shù)據(jù)列
SELECT EmpID, Title, CAST(DecryptBykey(Salary) AS VARCHAR(20)) AS Salary FROM EmpSalary

--3.)關(guān)閉對(duì)稱密鑰
CLOSE SYMMETRIC KEY sym_Salary

--==================(III)繞過(guò)加密數(shù)據(jù)的攻擊=====================
--1.)攻擊者使用其它數(shù)據(jù)行的加密數(shù)據(jù)替換某一行的數(shù)據(jù)
SELECT * FROM EmpSalary
UPDATE EmpSalary SET Salary =
    (SELECT Salary FROM EmpSalary WHERE EmpID = 1)
    WHERE EmpID = 3

--2.)查看被攻擊后解密的數(shù)據(jù)
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)使用驗(yàn)證器防止繞過(guò)加密數(shù)據(jù)的攻擊=====================
--1.)刪除前面添加的數(shù)據(jù)行
DELETE FROM EmpSalary

--2.)向表中插入數(shù)據(jù)，并對(duì)Salary列的數(shù)據(jù)使用驗(yàn)證器進(jìn)行加密，第四個(gè)參數(shù)是加密因子
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.)解密并訪問(wèn)被加密了的數(shù)據(jù)列
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.)攻擊者使用相同的方法篡改數(shù)據(jù)
SELECT * FROM EmpSalary
UPDATE EmpSalary SET Salary =
    (SELECT Salary FROM EmpSalary WHERE EmpID = 1)
    WHERE EmpID = 3

--5.)被篡改后的加密了的數(shù)據(jù)列變成無(wú)效
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

/*
[課程]使用數(shù)據(jù)庫(kù)加密保護(hù)敏感數(shù)據(jù)

DEMO 3
使用證書(shū)簽署存儲(chǔ)過(guò)程

[過(guò)程]
過(guò)程一共分為2個(gè)部分

*/

--==================(I)示例準(zhǔn)備=====================
--1.)創(chuàng)建數(shù)據(jù)庫(kù)主密鑰
USE Northwind
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'

--2.)創(chuàng)建簽署存儲(chǔ)過(guò)程所需要的證書(shū)
CREATE CERTIFICATE cert_Products
    WITH SUBJECT = 'Products Sign',
    START_DATE = '2006/1/1',
    EXPIRY_DATE = '2008/1/1'

--3.)創(chuàng)建SPDeveloper登錄帳戶和用戶，該用戶創(chuàng)建訪問(wèn)Products表的存儲(chǔ)過(guò)程
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的身份創(chuàng)建存儲(chǔ)過(guò)程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.)創(chuàng)建普通用戶Jerry
CREATE LOGIN jerry WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[Northwind]
CREATE USER jerry FOR LOGIN jerry

--==================(II)使用證書(shū)簽署存儲(chǔ)過(guò)程=====================
--1.)授予用戶Jerry執(zhí)行存儲(chǔ)過(guò)程的權(quán)限
GRANT EXECUTE ON products.usp_Products TO jerry

--2.)以Jerry的身份執(zhí)行存儲(chǔ)過(guò)程失敗，因?yàn)閾碛腥�鏈�(zhǔn)菙嗔训?BR>EXECUTE AS USER = 'jerry'
SELECT USER
GO

EXECUTE products.usp_Products
GO

REVERT

--3.)使用證書(shū)在當(dāng)前數(shù)據(jù)庫(kù)創(chuàng)建用戶ProductsReader，
--     并為該用戶賦予讀取Products表的權(quán)限
CREATE USER ProductsReader FOR CERTIFICATE cert_Products
GO
GRANT SELECT ON Products TO ProductsReader

--4.)使用證書(shū)簽署當(dāng)前存儲(chǔ)過(guò)程
ADD SIGNATURE TO products.usp_Products BY CERTIFICATE cert_Products

--4.)以Jerry的身份重新執(zhí)行存儲(chǔ)過(guò)程，成功，
--     因?yàn)榇鎯?chǔ)過(guò)程將以ProductsReader的權(quán)限上下文執(zhí)行
EXECUTE AS USER = 'jerry'
SELECT  USER
GO
EXECUTE products.usp_Products

                  
         講師: 牛可 

          時(shí)間:  2006年8月9日 10:00--11:30
          產(chǎn)品: SQL Server
          技術(shù)等級(jí):  200 

         歡迎大家積極參與討論

課后問(wèn)題及答案

1.       在SQL Server 2005中，數(shù)據(jù)庫(kù)的主密鑰可以直接用來(lái)加密保護(hù)：（AB）

A.       證書(shū)的私鑰

B.       非對(duì)稱密鑰的私鑰

C.      非對(duì)稱密鑰的公鑰

D.      服務(wù)主密鑰

2.       當(dāng)采用加密技術(shù)來(lái)保護(hù)數(shù)據(jù)庫(kù)中的大量敏感數(shù)據(jù)時(shí)，為了兼顧性能和數(shù)據(jù)的安全性，最佳的做法是：（C）

A.       使用證書(shū)加密所有敏感數(shù)據(jù)，并用對(duì)稱密鑰加密保護(hù)證書(shū)的私鑰

B.       使用非對(duì)稱密鑰的公鑰加密所有敏感數(shù)據(jù)，并用對(duì)稱密鑰加密保護(hù)該密鑰對(duì)的私鑰

C.      使用對(duì)稱密鑰加密所有敏感數(shù)據(jù)，并用證書(shū)加密保護(hù)該對(duì)稱密鑰

D.      使用非對(duì)稱密鑰的私鑰加密所有敏感數(shù)據(jù)，并用證書(shū)加密保護(hù)該密鑰對(duì)的公鑰

3.       在SQL Server 2005中使用證書(shū)簽署存儲(chǔ)過(guò)程的目的是：（D）

A.       確保只有擁有該證書(shū)對(duì)應(yīng)私鑰的用戶才能執(zhí)行該存儲(chǔ)過(guò)程

B.       加密存儲(chǔ)過(guò)程，防止其它人查看到存儲(chǔ)過(guò)程中的T-SQL語(yǔ)句

C.      加密存儲(chǔ)過(guò)程執(zhí)行返回的數(shù)據(jù)結(jié)果集