--SQLSERVER中的加密函數(shù) 2013-7-11
ENCRYPTBYASYMKEY() --非對稱密鑰
ENCRYPTBYCERT()   --證書加密
ENCRYPTBYKEY()   --對稱密鑰
ENCRYPTBYPASSPHRASE()  --通行短語（PassPhrase）加密

---------------------------------------------------------------------------------------
--非對稱密鑰包含數(shù)據(jù)庫級的內部公鑰和私鑰，它可以用來加密和解密SQL Server數(shù)據(jù)庫中的數(shù)據(jù)，
--它可以從外部文件或程序集中導入，也可以在SQL Server數(shù)據(jù)庫中生成。它不像證書，不可以備份到文件。
--這意味著一旦在SQL Server中創(chuàng)建了它，沒有非常簡單的方法在其他用戶數(shù)據(jù)庫中重用相同的密鑰。
--非對稱密鑰對于數(shù)據(jù)庫加密屬于高安全選項，因而需要更多的SQL Server資源。
--我們看一組例子：

--示例一、創(chuàng)建非對稱密鑰
--
--創(chuàng)建非對稱密鑰使用如下命令:

--CREATE ASYMMETRIC KEY 

 

--以下語句創(chuàng)建一個非對稱密鑰asymDemoKey

use [pratice]
go

CREATE ASYMMETRIC KEY asymDemoKey --創(chuàng)建非對稱密鑰名稱
WITH ALGORITHM = RSA_512 --加密安全類型
ENCRYPTION BY PASSWORD = '123!' --密碼

--示例二、查看當前數(shù)據(jù)庫中的非對稱密鑰

--使用目錄視圖sys.asymmetric_keys(http://msdn.microsoft.com/en-us/library/ms188399.aspx)來查看。

--查看當前數(shù)據(jù)庫中的非對稱密鑰
USE [pratice]
go

SELECT name, algorithm_desc, pvt_key_encryption_type_desc
FROM sys.asymmetric_keys

--示例三、修改非對稱密鑰的私鑰密碼

--你可以使用帶有ENCRYPTION BY PASSWORD和DECRYPTION BY PASSWORD選項的
--ALTER ASYMMETRIC KEY（http://technet.microsoft.com/en-us/library/ms189440.aspx）修改私鑰的密碼

--修改私鑰密碼
ALTER ASYMMETRIC KEY asymDemoKey--要修改的密鑰名稱
WITH PRIVATE KEY --私鑰
(ENCRYPTION BY PASSWORD = '456',--指定新密碼
DECRYPTION BY PASSWORD = '123!')--舊密碼是用來解密的

--示例四、使用非對稱密鑰對數(shù)據(jù)進行加密和解密。
--
--由于同時需要公鑰和密鑰，在維護保密數(shù)據(jù)時使用非對稱密鑰來加密數(shù)據(jù)是非常安全的方式。
--但同時用于大數(shù)據(jù)集時將消耗更多的資源。
--
--不推薦使用非對稱密鑰對數(shù)據(jù)加密，但它仍然是一個選擇。一旦將非對稱密鑰加到數(shù)據(jù)庫，就可以用來加密和解密數(shù)據(jù)。
--
--用到以下兩個sql函數(shù):
--
--EncryptByAsymKey 加密數(shù)據(jù)。（http://technet.microsoft.com/en-us/library/ms186950.aspx）
--
--DecryptByAsymKey解密數(shù)據(jù)。（http://msdn.microsoft.com/en-us/library/ms189507.aspx）

--注意，在通過證書加密時，DecryptByAsymKey返回的是varbinary類型的加密數(shù)據(jù)。
--
--下面是一個例子：

use [pratice]
go

--創(chuàng)建需要加密的數(shù)據(jù)
Create Table BankUser
(PKID int primary key identity(1,1)
,UserNo varbinary(1000) null  --一定要用二進制數(shù)據(jù)類型
,CurState datetime   not null
)
go

insert into BankUser
(UserNo,CurState)
VALUES (EncryptByAsymKey(AsymKey_ID('asymDemoKey'),'137'),GETDATE())
--插入一條記錄，字段UserNo存儲了加密的號碼值
go

 

--查看未加密的數(shù)據(jù)： 
SELECT PKID,Curstate,
cast
(DecryptByAsymKey(AsymKey_ID('asymDemoKey'),UserNo,N'123')
as varchar(1000)) as UserNo --需要原始私鑰
from BankUser

 

 

--示例五、刪除非對稱密鑰

--命令：DROP ASYMMETRIC KEY 刪除指定的非對稱密鑰( http://msdn.microsoft.com/en-us/library/ms188389.aspx)
--
--例子：
DROP ASYMMETRIC KEY asymDemoKey

DROP TABLE [dbo].[BankUser]

--小結：
--
--1、本文主要介紹非對稱密鑰的創(chuàng)建、刪除、查看以及用它來修改私鑰、進行數(shù)據(jù)的加密和解密。
--
--2、非對稱密鑰包含數(shù)據(jù)庫級的內部公鑰和私鑰，它可以用來加密和解密SQL Server數(shù)據(jù)庫中的數(shù)據(jù)。
--
--3、非對稱密鑰對于數(shù)據(jù)庫加密屬于高安全選項，因而需要更多的SQL Server資源，不推薦使用。
--
--下文將主要介紹相對簡單的并且廣泛應用的對稱密鑰加密(Symmetric Key Encryption)
-----------------------------------------------------------------------------------------------------

--SQL Server 2008引入透明數(shù)據(jù)加密（Transparent Data Encryption），即TDE
--它允許你完全無需修改應用程序代碼而對整個數(shù)據(jù)庫加密。
--
--當一個用戶數(shù)據(jù)庫可用且已啟用TDE時，在寫入到磁盤時在頁級實現(xiàn)加密。在數(shù)據(jù)頁讀入內存時解密。
--如果數(shù)據(jù)庫文件或數(shù)據(jù)庫備份被盜，沒有用來加密的原始證書將無法訪問。
--這幾乎是SQL Server2008安全選項中最激動人心的功能了，有了它，我們至少可以將一些初級的惡意窺視拒之見外。
--下面的兩個例子將展示如何啟用和維護透明數(shù)據(jù)加密
--示例一、啟用透明加密(TDE)

USE Master
GO
--刪除舊主密鑰
--Drop MASTER KEY
--GO
--創(chuàng)建主密鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='B19ACE32-AB68-4589-81AE-010E9092FC6B'
GO
--創(chuàng)建證書,用于透明數(shù)據(jù)加密
CREATE CERTIFICATE TDE_Server_Certificate
WITH SUBJECT = 'Server-level cert for TDE'
GO

USE [pratice]
GO
--第一步：現(xiàn)在開始透明加密
CREATE DATABASE ENCRYPTION KEY--創(chuàng)建數(shù)據(jù)庫加密密鑰
    WITH ALGORITHM = TRIPLE_DES_3KEY--加密方式
    ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate--使用服務器級證書加密
GO
/*
Warning: The certificate used for encrypting the database encryption key
has not been backed up.
You should immediately back up the certificate and the private key
associated with the certificate.
If the certificate ever becomes unavailable or
if you must restore or attach the database on another server,
you must have backups of both the certificate and the private key
or you will not be able to open the database.
*/

--第二步：打開加密開關
ALTER DATABASE [pratice] SET ENCRYPTION ON
GO

--查看數(shù)據(jù)庫是否加密
SELECT  is_encrypted FROM    sys.databases
WHERE   name = 'pratice'

--注意：一旦在數(shù)據(jù)庫應用了加密，應該立刻備份服務器級證書！

--沒有加密dek的證書，該數(shù)據(jù)庫將無法打開，附加到別的服務器也無法使用，數(shù)據(jù)庫文件亦不會被Hack。
--如果一個DBA想要合法地將數(shù)據(jù)庫從一個SQL Server實例移動到另一個SQL Server實例，
--那么她應該首先備份服務器級證書，然后在新的SQL Server實例中創(chuàng)建證書。
--此時可以合法地備份、還原數(shù)據(jù)庫或附加數(shù)據(jù)及日志文件。

--示例二、管理和移除透明加密(TDE)
USE [pratice]
GO
--修改加密算法
ALTER DATABASE  ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_128
Go

SELECT  DB_NAME(database_id) databasenm,
        CASE encryption_state
          WHEN 0 THEN 'No encryption'
          WHEN 1 THEN 'Unencrypted'
          WHEN 2 THEN 'Encryption in progress'
          WHEN 3 THEN 'Encrypted'
          WHEN 4 THEN 'Key change in progress'
          WHEN 5 THEN 'Decryption in progress'
        END encryption_state, key_algorithm, key_length
FROM    sys.dm_database_encryption_keys

/*
對所有用戶數(shù)據(jù)庫的加密處理也包含對tempdb的處理 ，因為表連接，臨時表都要用到tempdb
databasenm encryption_state key_algorithm key_length
tempdb Encrypted AES 256
DB_Encrypt_Demo Encrypted AES 128
*/

--除了更改dek的算法，我們也可以更改用來加密數(shù)據(jù)庫的服務器級證書（該證書應該定期更改）

USE master
GO
CREATE CERTIFICATE TDE_Server_Certificate_V2
WITH SUBJECT = 'Server-level cert for TDE V2'
GO
USE [pratice]
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate_V2
--用新證書修改DEK

--移除數(shù)據(jù)庫透明加密
ALTER DATABASE [pratice]
SET ENCRYPTION OFF
GO

--移除TDE后，可以刪除DEK
USE [pratice]
GO
DROP DATABASE ENCRYPTION KEY
Go

--注意：如果刪除DEK是SQL Server實例中最后一個使用TDE的用戶定義數(shù)據(jù)庫，
--在SQL Server實例重啟后，tempdb也將變?yōu)椴患用艿臓顟B(tài)。
--
--小結：
--
--1、本文主要介紹透明數(shù)據(jù)加密(TDE)的使用。
--
--2、對DEK的修改同時影響到tempdb數(shù)據(jù)庫的加密狀態(tài)。
------------------------------------------------------------------------------------------------

--SQLServer 2008中的代碼安全（七） 證書加密
--
--證書可以在數(shù)據(jù)庫中加密和解密數(shù)據(jù)。
--證書包含密鑰對、關于證書擁有者的信息、證書可用的開始和結束過期日期。
--
--證書同時包含公鑰和密鑰，前者用來加密，后者解密。SQL Server可以生成它自己的證書，
--也可以從外部文件或程序集載入。因為可以備份然后從文件中載入它們，證書比非對稱密鑰更易于移植，
--而非對稱密鑰卻做不到。這意味著可以在數(shù)據(jù)庫中方便地重用同一個證書。

--注意：證書和非對稱密鑰同樣的消耗資源。

--我們看一組例子：
--
--示例一、創(chuàng)建數(shù)據(jù)庫證書

--創(chuàng)建數(shù)據(jù)庫證書:CREATE SYMMETRIC KEY （http://msdn.microsoft.com/en-us/library/ms187798.aspx）
USE [pratice]
GO
--創(chuàng)建證書
CREATE CERTIFICATE cert_Demo --證書名稱
ENCRYPTION BY PASSWORD = '123!!!' --加密證書的密碼
WITH SUBJECT = 'DB_Encrypt_Demo Database Encryption Certificate',--證書主題
START_DATE = '3/14/2012', EXPIRY_DATE = '10/20/2016'--起止日期
GO

--示例二、查看數(shù)據(jù)庫中的證書

--使用目錄視圖sys.certificates(http://msdn.microsoft.com/en-us/library/ms189774.aspx)來查看。

--查看當前數(shù)據(jù)庫中的證書
USE [pratice]
go
--查看證書
SELECT  name, pvt_key_encryption_type_desc, issuer_name
FROM    sys.certificates

--示例三、備份和還原證書

--創(chuàng)建證書后，也可以使用BACKUP CERTIFICATE（http://msdn.microsoft.com/en-us/library/ms178578.aspx）
--命令備份到文件，為了安全地保存或在其他數(shù)據(jù)庫中還原它。
--備份證書
BACKUP CERTIFICATE cert_Demo
TO FILE = 'D:\certDemo.BAK'--證書備份路徑,用來加密
WITH PRIVATE KEY (FILE='D:\certDemoPK.BAK',--證書私鑰文件路徑，用來解密
ENCRYPTION BY PASSWORD = '465!!!',--加密私鑰密碼
DECRYPTION BY PASSWORD = '123!!!' )--解密私鑰密碼

--備份后，可以在其他數(shù)據(jù)庫中使用這個證書，或使用DROP CERTIFICATE命令刪除它。
DROP CERTIFICATE cert_Demo
GO
--從備份文件中還原證書到數(shù)據(jù)庫中
CREATE CERTIFICATE cert_Demo
FROM FILE = 'D:\\certDemo.BAK'
WITH PRIVATE KEY (FILE = 'D:\certDemoPK.BAK',
DECRYPTION BY PASSWORD = '456!!!' ,--解密私鑰密碼
ENCRYPTION BY PASSWORD = '123!!!')--加密私鑰密碼

--示例四、管理證書的私鑰

--使用ALTER CERTIFICATE( http://msdn.microsoft.com/en-us/library/ms189511.aspx)
--命令為證書增加或刪除私鑰。
--這個命令允許刪除私鑰（默認通過數(shù)據(jù)庫主密鑰加密）、增加私鑰或修改私鑰的密碼。

--從證書中刪除私鑰
ALTER CERTIFICATE cert_Demo
REMOVE PRIVATE KEY

--從備份文件為既有證書重新增加私鑰
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY
(FILE = 'D:\certDemoPK.BAK',
DECRYPTION BY PASSWORD = '1234GH!!!' ,--解密私鑰密碼
ENCRYPTION BY PASSWORD = '123!!!')--加密私鑰密碼

--修改既有私鑰的密碼
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY (DECRYPTION BY PASSWORD = '123!!!',
ENCRYPTION BY PASSWORD = '789!!!13E')

--示例五、使用證書加密和解密。
--
--使用函數(shù)EncryptByCert加密數(shù)據(jù)。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)
USE [pratice]
GO
CREATE TABLE PWDQuestion
(
  CustomerID INT ,
  PasswordHintQuestion NVARCHAR(200) ,
  PasswordHintAnswer NVARCHAR(200)
)
--插入測試數(shù)據(jù)
INSERT  dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
VALUES  ( 10, '您出生的醫(yī)院名稱?', ENCRYPTBYCERT(CERT_ID('cert_Demo'), '北京四合院家中') )

--查看密文
SELECT [PasswordHintQuestion], CAST(PasswordHintAnswer AS VARCHAR(200)) PasswordHintAnswer
FROM    dbo.PWDQuestion
WHERE   CustomerID = 10

--查看原文
SELECT  PasswordHintQuestion,
        CAST(DECRYPTBYCERT(CERT_ID('cert_Demo'), PasswordHintAnswer,
                           N'789!!!13E') AS VARCHAR(200)) PasswordHintAnswer
FROM    dbo.PWDQuestion
WHERE   CustomerID = 10

 

--示例六、使用對稱密鑰對數(shù)據(jù)進行加密和解密。

--在前面的文章中，你已經看到打開用非對稱密鑰加密的對稱密鑰的演示，
--它分兩個步驟，首先用OPEN SYMMETRIC KEY命令，然后是實際的DecryptByKey函數(shù)調用。
--SQL Server也提供了能夠將這兩個步驟合二為一的額外的解密函數(shù)：
--DECRYPTBYKEYAUTOASYMKEY（http://msdn.microsoft.com/en-us/library/ms365420.aspx）
--和DecryptByKeyAutoCert（http://msdn.microsoft.com/en-us/library/ms182559.aspx）
USE [pratice]
GO

--本例使用數(shù)據(jù)庫主密碼加密，因而不需要密碼。
----Create master Key Encryption By password='123ASD!'
----go

--創(chuàng)建非對稱密鑰
CREATE ASYMMETRIC KEY asymDemo_V2
WITH ALGORITHM = RSA_512
--創(chuàng)建對稱密鑰
CREATE SYMMETRIC KEY sym_Demo_V2
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY asymDemo_V2

--打開對稱密鑰，插入記錄
OPEN SYMMETRIC KEY sym_Demo_V2
DECRYPTION BY ASYMMETRIC KEY asymDemo_V2
INSERT  dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
VALUES  ( 22, '您出生的醫(yī)院名稱?', ENCRYPTBYKEY(KEY_GUID('sym_Demo_V2'), '邵逸夫醫(yī)院') )
CLOSE SYMMETRIC KEY sym_Demo_V2

--此時，使用DecryptByKeyAutoAsymKey解密數(shù)據(jù)，只需要一個操作
SELECT  CAST(DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('asymDemo_V2'), NULL,
                                     PasswordHintAnswer) AS VARCHAR)
FROM    dbo.PWDQuestion
WHERE   CustomerID = 22

--小結：
--
--1、本文主要介紹證書的創(chuàng)建、刪除、查看以及用它來修改加密方式、進行數(shù)據(jù)的加密和解密。
--
--2、證書加密和非對稱密鑰加密相對對稱密鑰加密更為消耗資源。
--
--下文將主要介紹SQL Server中最為令人鼓舞的透明數(shù)據(jù)加密(TDE)
-----------------------------------------------------------------------------------------
--SQL Server 2008中的代碼安全（六）：對稱密鑰加密

--證書和非對稱密鑰使用數(shù)據(jù)庫級的內部公鑰加密數(shù)據(jù)，并且使用數(shù)據(jù)庫級內部私鑰解密數(shù)據(jù)。
--而對稱密鑰相對簡單，它們包含一個同時用來加密和解密的密鑰。
--困此，使用對稱密鑰加密數(shù)據(jù)更快，并且用在大數(shù)據(jù)時更加合適。
--盡管復雜度是考慮使用它的因素，但它仍然是一個很好的加密數(shù)據(jù)的選擇。

 

--示例一、創(chuàng)建對稱密鑰
--
--對稱密鑰的特性是：在數(shù)據(jù)庫會話中使用它對數(shù)據(jù)進行加密和解密前必須首先打開。
--
--創(chuàng)建對稱密鑰使用如下命令:CREATE SYMMETRIC KEY  創(chuàng)建對稱密鑰。
--（http://msdn.microsoft.com/en-us/library/ms188357.aspx）

USE [pratice]
go

-- 創(chuàng)建一個用于加密對稱密鑰的非對稱密鑰
CREATE ASYMMETRIC KEY symDemoKey --名稱
WITH ALGORITHM = RSA_512     --加密算法
ENCRYPTION BY PASSWORD ='TestSYM456!'
--密碼

-- 創(chuàng)建一個對稱密鑰
CREATE SYMMETRIC KEY sym_Demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY symDemoKey

--示例二、查看當前數(shù)據(jù)庫中的對稱密鑰
--
--使用目錄視圖sys.symmetric_keys(http://msdn.microsoft.com/en-us/library/ms189446.aspx)來查看。
--查看當前數(shù)據(jù)庫中的非對稱密鑰
USE [pratice]
go
SELECT  name, algorithm_desc
FROM    sys.symmetric_keys

--示例三、修改非對稱密鑰的加密方式
--
--你可以使用ALTER SYMMETRIC KEY（http://technet.microsoft.com/en-us/library/ms189440.aspx）命令修改
--對稱密鑰的加密方式。
--但執(zhí)行前必須使用OPEN SYMMETRIC KEY(http://msdn.microsoft.com/en-us/library/ms190499.aspx)命令打開它。
USE [pratice]
go

--先用私鑰密碼打開對稱密鑰
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY ASYMMETRIC KEY symDemoKey
WITH PASSWORD ='TestSYM456!'

--打開之后，先增加密碼加密，取代原密鑰
ALTER SYMMETRIC KEY sym_Demo
ADD ENCRYPTION BY PASSWORD ='newnew!456'
--再刪除非對稱密鑰加密
ALTER SYMMETRIC KEY sym_Demo
DROP ENCRYPTION BY ASYMMETRIC KEY symDemoKey
--完成操作后，關閉對稱密鑰
CLOSE SYMMETRIC KEY sym_Demo

--示例四、使用對稱密鑰對數(shù)據(jù)進行加密和解密。
--
--1、為了使用對稱密鑰對數(shù)據(jù)進行加密，必須首先打開它，
--然后使用函數(shù)EncryptByKey 加密數(shù)據(jù)。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)

--2、使用DecryptByKey來解密使用對稱密鑰加密的數(shù)據(jù)。注意DecryptByKey不像EncryptByKey，
--無須使用對稱密鑰GUID。因此，為了解密，必須打開正確的對稱密鑰會話，否則會顯示null。
--
--下面是一個例子：
USE [pratice]
GO
--DROP TABLE [dbo].[PWDQuestion]
--GO
--創(chuàng)建測試數(shù)據(jù)表，用于對稱加密
CREATE TABLE dbo.PWDQuestion
(
  CustomerID INT NOT NULL PRIMARY KEY ,
  PasswordHintQuestion NVARCHAR(300) NOT NULL ,
  PasswordHintAnswer VARBINARY(200) NOT NULL
)
GO
--插入加密數(shù)據(jù)
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD ='newnew!456'
INSERT  dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
VALUES  ( 12, '您出生的醫(yī)院名稱？', ENCRYPTBYKEY(KEY_GUID('sym_Demo '), '杭州市一') )
CLOSE SYMMETRIC KEY sym_Demo

--查看未加密的數(shù)據(jù)：
--解密數(shù)據(jù)
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD ='newnew!456'
SELECT  CustomerID, PasswordHintQuestion,
        CAST(DECRYPTBYKEY(PasswordHintAnswer) AS VARCHAR(200)) PasswordHintAnswer
FROM    dbo.PWDQuestion
WHERE   CustomerID = 12
--打開后切記關閉?。?！
CLOSE SYMMETRIC KEY sym_Demo

--不打開直接讀取
SELECT  CustomerID, PasswordHintQuestion,
        CAST(DECRYPTBYKEY(PasswordHintAnswer) AS VARCHAR(200)) PasswordHintAnswer
FROM    dbo.PWDQuestion
WHERE   CustomerID = 12

--至此，好像已經大功告成了，別，千萬別高興得太早！

--這里有個問題，如果惡意用戶不知道CustomerID＝13的PasswordHintAnswer列的真實值，
--但知道CustomerID＝14的PasswordHintAnswer列的真實值,
--則完全可以通過惡意替換PasswordHintAnswer列而繞過加密！！
--此時，我們索性連CustomerID列作為驗證列也一起加密，以絕后患 !
--
--注意：加密的驗證列也可以由另一個相關表的列作為參數(shù)傳入。

--示例五、刪除對稱密鑰

--命令：DROP SYMMETRIC KEY 刪除指定的對稱密鑰( http://technet.microsoft.com/en-us/library/ms182698.aspx)
--
--例子：

DROP SYMMETRIC KEY sym_Demo
--注意：如果加密密鑰打開沒有關閉，則drop失敗。

--小結：
--
--1、本文主要介紹對稱密鑰的創(chuàng)建、刪除、查看以及用它來修改加密方式、進行數(shù)據(jù)的加密和解密。
--
--2、對稱密鑰的特性是：在數(shù)據(jù)庫會話中使用它對數(shù)據(jù)進行加密和解密前必須首先打開。
--
--3、對稱密鑰可用于大數(shù)據(jù)的加密。
-------------------------------------------------------------------------------------------
--SQL Server 2008中的代碼安全（四）：主密鑰
--在SQL Server中的加密由層次結構形式進行處理以提供多級別的安全。
--SQL Server包含兩個用于加密數(shù)據(jù)的密鑰類型。如下圖：
--1、服務器主密鑰（Service Master Key）,位于層次結構的最頂端，并且在安裝SQL Server時自動創(chuàng)建，
--用于加密系統(tǒng)數(shù)據(jù)、鏈接的服務器登錄名以及數(shù)據(jù)庫主密鑰。
--在第一次通過SQL Server使用服務主密鑰來加密證書、數(shù)據(jù)庫主密鑰或鏈接的服務器主密碼時，
--服務主密鑰會自動生成，并且使用SQL Server服務賬戶的Windows證書來生成它。
--如果必須改變SQL Server服務賬號，微軟建議使用SQL Server配置管理器，
--因為這個工具將執(zhí)行生成新服務主密鑰需要的合適的解密和加密方法，而且可以使加密層次結構保持完整。
--服務主密鑰也用于加密其下的數(shù)據(jù)庫主密鑰。
--
--2、數(shù)據(jù)庫主密鑰（Database Master Key）,用于加密證書，以及非對稱密鑰和對稱密鑰。
--所有數(shù)據(jù)庫都可以只包含一個數(shù)據(jù)庫主密鑰，在創(chuàng)建它時，通過服務主密鑰對其加密。
--創(chuàng)建非對稱密鑰時，可以決定在加密非對稱密鑰對應的私鑰是否包含密碼。
--如果示包含密碼，將使用數(shù)據(jù)庫主密鑰來加密私鑰。
--
--我們看一組例子：
--
--示例一、備份及還原服務主密鑰
--例一、備份及還原服務主密鑰
--
--用到以下兩個sql命令:
--
--BACKUP SERVICE MASTER KEY  導出服務主密鑰。（http://msdn.microsoft.com/zh-cn/library/ms190337.aspx）
--
--RESTORE SERVICE MASTER KEY從備份文件中導入服務主密鑰。（http://msdn.microsoft.com/zh-cn/library/ms187972.aspx）

--以下語句備份服務主密鑰到C:\SqlBackup\SMK.bak

BACKUP SERVICE MASTER KEY
TO FILE='D:\SMK.bak'
ENCRYPTION BY PASSWORD ='123!1AB'
----注意該密碼可以使用單引號
go

--恢復服務主密鑰
RESTORE SERVICE MASTER KEY
FROM FILE='D:\SMK.bak'
DECRYPTION BY PASSWORD ='123!1AB'
go

 

--如果該密鑰沒有實際變化，而執(zhí)行密鑰恢復時，會收到提示：

--The old and new master keys are identical. No data re-encryption is required.

--示例二、創(chuàng)建、再生成和刪除數(shù)據(jù)庫主密鑰
--
--用到以下兩個sql命令:
--
--CREATE MASTER KEY 創(chuàng)建數(shù)據(jù)庫主密鑰（http://technet.microsoft.com/zh-cn/library/ms174382.aspx）
--
--ALTER MASTER KEY 重新生成數(shù)據(jù)庫主密鑰（http://msdn.microsoft.com/en-us/library/ms186937%28SQL.90%29.aspx）
--
--DROP MASTER KEY 刪除數(shù)據(jù)庫主密鑰（http://msdn.microsoft.com/en-us/library/ms180071.aspx）
--
--當數(shù)據(jù)庫主密鑰被顯式創(chuàng)建時，會同時自動生成一個額外生成的安全層，
--用于加密數(shù)據(jù)庫中的新證書和非對稱密鑰，更進一步保護已加密的數(shù)據(jù)。

IF NOT EXISTS ( SELECT  name
                FROM    sys.databases
                WHERE   name = 'BookStore' )
    BEGIN
        CREATE DATABASE BookStore
    END
GO

USE BookStore
GO
--創(chuàng)建數(shù)據(jù)庫主密鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
go

USE BookStore
GO
--重新生成數(shù)據(jù)庫主密鑰
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD ='password'

--刪除數(shù)據(jù)庫主密鑰
USE BookStore
GO
DROP MASTER KEY

--注意：如果該數(shù)據(jù)庫主密鑰仍然被其他數(shù)據(jù)庫對象使用，則不能被刪除，這點與架構類似。
--
--同時一旦創(chuàng)建數(shù)據(jù)庫主密鑰，就立刻備份它是一個好的習慣。
--備份數(shù)據(jù)庫主密鑰
USE BookStore
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MagneticFields!'
GO
BACKUP MASTER KEY TO FILE='D:\BookStore_Master_Key.BAK'
ENCRYPTION BY PASSWORD ='4D280837!!!'

--恢復數(shù)據(jù)庫主密鑰
RESTORE MASTER KEY FROM FILE='D:\BookStore_Master_Key.BAK'
DECRYPTION BY PASSWORD ='4D280837!!!'
ENCRYPTION BY PASSWORD ='MagneticFields!'

--與服務主密鑰類似，如果沒有修改，則會收到如下提示：

--The old and new master keys are identical. No data re-encryption is required.

--示例三、從數(shù)據(jù)庫主密鑰刪除服務主密鑰
--
--當一個數(shù)據(jù)庫主密鑰被創(chuàng)建時，它被默認使用兩種方式加密：
--服務主密鑰和被使用CREATE MASTER KEY 命令中使用的密碼。
--如果你不想使用服務主密碼加密數(shù)據(jù)庫主密鑰
--（這種情況下，擁有sysadmin特權的login在不知道數(shù)據(jù)庫主密鑰的前提下將不能訪問加密數(shù)據(jù)），
--你可以使用ALTER MASTER KEY 命令刪除服務主密鑰。
--
--簡略語法如下：

ALTER MASTER KEY

ADD ENCRYPTION BY SERVICE MASTER KEY |

DROP ENCRYPTION BY SERVICE MASTER KEY

--由于服務主密鑰允許擁有足夠許可（如sysadmin）的用戶自動使用數(shù)據(jù)庫主密鑰解密，
--因此，一旦刪除了服務主密鑰的加密，而再想修改數(shù)據(jù)庫主密鑰時，你必須使用一個新的命令訪問它。
--OPEN MASTER KEY, 語法如下：

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

--下面是一個例子：
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

--一旦執(zhí)行，任何數(shù)據(jù)庫主密鑰的修改需要使用OPEN MASTER KEY的口令訪問，
--這樣是為了重新應用服務主密鑰的加密
OPEN MASTER KEY DECRYPTION BY PASSWORD ='123456!'

--一旦服務主密鑰被用于加密數(shù)據(jù)庫主密鑰，數(shù)據(jù)庫主密鑰不再需要被顯式打開或關閉。
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

--關閉數(shù)據(jù)庫主密鑰
CLOSE MASTER KEY

--小結：
--
--1、本文主要介紹服務主密鑰的備份與還原，數(shù)據(jù)庫的主密鑰的創(chuàng)建、重新生成、刪除和備份、還原。
--
--2、一旦創(chuàng)建主密鑰，立刻備份它是一個很好的習慣。

------------------------------------------------------------------------------------
--SQL Server 2008中的代碼安全（三）：通過PassPhrase加密

--導讀：本文主要涉及EncryptByPassPhrase和DecryptByPassPhrase函數(shù)進行通行短語（PassPhrase）加密。

--前言：
--
--在SQL Server 2005和SQL Server 2008之前。
--如果希望加密敏感數(shù)據(jù)，如財務信息、工資或身份證號，必須借助外部應用程序或算法。
--SQL Server 2005引入內建數(shù)據(jù)加密的能力，使用證書、密鑰和系統(tǒng)函數(shù)的組合來完成。
--
--與數(shù)字證書類似。SQL Server 證書包括公鑰和私鑰這一對密鑰，它們都用來加密和解密數(shù)據(jù)。
--SQL Server也擁有創(chuàng)建非對稱密鑰和對稱密鑰對象的能力。非對稱密鑰（asymmetric key）與證書相似，
--公鑰用來加密數(shù)據(jù)庫，私鑰用來解密數(shù)據(jù)。
--非對稱密鑰和證書都提供了強大的加密強度。但在完成復雜的加密|解密過程中具有更多的性能開銷。
--更適合對大量數(shù)據(jù)進行加密，且具有較低性能開銷的解決方案是對稱密鑰（symmetric key），
--它是對相同數(shù)據(jù)進行加密和解密的一個密鑰。
--
--SQL Server允許將這些加密能力放到加密層次結構中。
--當安裝了SQL Server后，在數(shù)據(jù)庫master中創(chuàng)建名為服務主密鑰的服務器級別證書，
--并將其默綁定到SQL Server服務賬號登錄名。
--服務主密鑰用來加密所有其他數(shù)據(jù)庫證書和創(chuàng)建在SQL Server實例中的密鑰。
--另外，你也可以在用戶數(shù)據(jù)庫中創(chuàng)建數(shù)據(jù)庫主密鑰（Database Master Key）,
--它可以用來加密數(shù)據(jù)庫證書和密鑰。
--
--在SQL Server 2008中，微軟引入了透明數(shù)據(jù)加密(TDE),它對整個數(shù)據(jù)庫進行加密，
--而不需要修改任何訪問它的應用程序。數(shù)據(jù)、日志文件和相關的數(shù)據(jù)庫備份都是加密的。
--假如數(shù)據(jù)庫被偷，如果沒有數(shù)據(jù)庫加密密鑰（DEK）是不能訪問數(shù)據(jù)的。
--本文及后面幾篇文章將會舉例說明。

--在SQL Server 2008中，還引入了對可擴展密鑰管理（EKM）的支持，
--也就意味著SQL Server可以使用硬件安全模塊（HSM）來存儲和和管理加密密鑰。
--HSM可以減少數(shù)據(jù)和實際的加密密鑰耦合。

--此部分內容共分六篇文章：
--1、通過PassPhrase加密
--2、主密鑰
--3、非對稱密鑰加密
--4、對稱密鑰加密
--5、證書加密
--6、透明數(shù)據(jù)加密

--一、通過通行短語(PassPhrase)加密
--
--對于不涉及證書及密鑰的應急的數(shù)據(jù)加密，可以直接基于用戶提供的密碼來加密和解密數(shù)據(jù)。
--通行短語(PassPhrase)是允許存在空格的密碼。
--這個PassPhrase不會存儲在數(shù)據(jù)庫中，因而也就意味著不會被使用存儲的系統(tǒng)數(shù)據(jù)“破解”。
--同時，可以使用空格創(chuàng)建一個長的、易于記憶的句子來加密和解密敏感數(shù)據(jù)。

--我們需要了解的一對函數(shù)是ENCRYPTBYPASSPHRASE（http://technet.microsoft.com/zh-cn/library/ms190357.aspx）和DECRYPTBYPASSPHRASE（http://technet.microsoft.com/zh-cn/library/ms188910.aspx）
--
--這一對函數(shù)必須使用相同的參數(shù)。
--
--我們看一個示例：
USE [pratice]
go

-- Table used to store the encrypted data
-- for the purposes of this recipe
--DROP TABLE SecretInfo
--GO

CREATE TABLE SecretInfo
(
  MySecret varbinary(max) NOT NULL  --一定要二進制數(shù)據(jù)格式
)
GO

INSERT  SecretInfo ( MySecret ) SELECT  ENCRYPTBYPASSPHRASE('123456','你好啊')

SELECT  MySecret FROM    SecretInfo

 

--想知道原文本的內容，使用以下語句：

SELECT  CAST(DECRYPTBYPASSPHRASE('123456', MySecret) AS VARCHAR(MAX))
FROM    SecretInfo

--注意：
--
--1、使用通行短語進行加密數(shù)據(jù)，不用擔心sysadmin服務器角色成員讀取數(shù)據(jù)
--（在后面文章會看到，服務器角色成員sysadmin擁有讀取其他形式的加密數(shù)據(jù)的內在權限。)
--
--
--2、假定沒有將密碼存儲在表中或在任何模塊（存儲過程、觸發(fā)器等）中使用密碼，
--加密的數(shù)據(jù)將防止從數(shù)據(jù)庫備份被偷竊或在SQL Server實例中的數(shù)據(jù)庫中滲透。
--如果通行短語短語密碼泄漏，數(shù)據(jù)就可以被解密。