演示用的腳本提供給大家作為參考：

--------------------------------------------------------------------------------

/*[課程]使用數據庫加密保護敏感數據demo 1了解sql2005加密層次結構[過程]過程一共分為4個部分*/--==================(i)服務主密鑰=====================--1.)備份服務主密鑰到文件backup service master key to file = 'c:/dbfile/smk.bak'encryption by password = '[email protected]'--2.)生成新的服務主密鑰alter service master key regenerate;go--3.)從備份文件還原服務主密鑰restore service master key from file = 'c:/dbfile/smk.bak' decryption by password = '[email protected]'--==================(ii)數據庫主密鑰=====================--1.)為northwind數據庫創建數據庫主密鑰use northwind gocreate master key encryption by password = '[email protected]'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 = '[email protected][email protected]'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 = '[email protected]'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 = '[email protected]'    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 = '[email protected]' ,          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'[email protected]')) 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'[email protected]')) 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 = '[email protected]'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 = '[email protected]'
go

--3.)

--4.)創建用于加密的對稱密鑰
create symmetric key sym_salary
    with algorithm = aes_192
    encryption by password = '[email protected]';

select * from sys.symmetric_keys where [name] = 'sym_salary'

--==================(ii)加密列數據=====================

--1.)打開對稱密鑰
open symmetric key sym_salary
    decryption by password = '[email protected]'

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 = '[email protected]'

--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 = '[email protected]'
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 = '[email protected]'
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 = '[email protected]'
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 = '[email protected]'
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 = '[email protected]'

--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'[email protected]', 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'[email protected]', 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.      加密存儲過程執行返回的數據結果集