ENCRYPTBYASYMKEY() --非對稱密鑰
ENCRYPTBYCERT() --證書加密
ENCRYPTBYKEY() --對稱密鑰
ENCRYPTBYPASSPHRASE() --通行短語（PassPhrase）加密
復制代碼 代碼如下:
--SQLSERVER中的加密函數 2013-7-11
ENCRYPTBYASYMKEY() --非對稱密鑰
ENCRYPTBYCERT() --證書加密
ENCRYPTBYKEY() --對稱密鑰
ENCRYPTBYPASSPHRASE() --通行短語（PassPhrase）加密
---------------------------------------------------------------------------------------
--非對稱密鑰包含數據庫級的內部公鑰和私鑰，它可以用來加密和解密SQL Server數據庫中的數據，
--它可以從外部文件或程序集中導入，也可以在SQL Server數據庫中生成。它不像證書，不可以備份到文件。
--這意味著一旦在SQL Server中創建了它，沒有非常簡單的方法在其他用戶數據庫中重用相同的密鑰。
--非對稱密鑰對於數據庫加密屬於高安全選項，因而需要更多的SQL Server資源。
--我們看一組例子：
--示例一、創建非對稱密鑰
--
--創建非對稱密鑰使用如下命令:
--CREATE ASYMMETRIC KEY
--以下語句創建一個非對稱密鑰asymDemoKey
use [pratice]
go
CREATE ASYMMETRIC KEY asymDemoKey --創建非對稱密鑰名稱
WITH ALGORITHM = RSA_512 --加密安全類型
ENCRYPTION BY PASSWORD = '123!' --密碼
--示例二、查看當前數據庫中的非對稱密鑰
--使用目錄視圖sys.asymmetric_keys(http://msdn.microsoft.com/en-us/library/ms188399.aspx)來查看。
--查看當前數據庫中的非對稱密鑰
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!')--舊密碼是用來解密的
--示例四、使用非對稱密鑰對數據進行加密和解密。
--
--由於同時需要公鑰和密鑰，在維護保密數據時使用非對稱密鑰來加密數據是非常安全的方式。
--但同時用於大數據集時將消耗更多的資源。
--
--不推薦使用非對稱密鑰對數據加密，但它仍然是一個選擇。一旦將非對稱密鑰加到數據庫，就可以用來加密和解密數據。
--
--用到以下兩個sql函數:
--
--EncryptByAsymKey 加密數據。（http://technet.microsoft.com/en-us/library/ms186950.aspx）
--
--DecryptByAsymKey解密數據。（http://msdn.microsoft.com/en-us/library/ms189507.aspx）
--注意，在通過證書加密時，DecryptByAsymKey返回的是varbinary類型的加密數據。
--
--下面是一個例子：
use [pratice]
go
--創建需要加密的數據
Create Table BankUser
(PKID int primary key identity(1,1)
,UserNo varbinary(1000) null --一定要用二進制數據類型
,CurState datetime not null
)
go
insert into BankUser
(UserNo,CurState)
VALUES (EncryptByAsymKey(AsymKey_ID('asymDemoKey'),'137'),GETDATE())
--插入一條記錄，字段UserNo存儲了加密的號碼值
go
--查看未加密的數據：
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、本文主要介紹非對稱密鑰的創建、刪除、查看以及用它來修改私鑰、進行數據的加密和解密。
--
--2、非對稱密鑰包含數據庫級的內部公鑰和私鑰，它可以用來加密和解密SQL Server數據庫中的數據。
--
--3、非對稱密鑰對於數據庫加密屬於高安全選項，因而需要更多的SQL Server資源，不推薦使用。
--
--下文將主要介紹相對簡單的並且廣泛應用的對稱密鑰加密(Symmetric Key Encryption)
-----------------------------------------------------------------------------------------------------
--SQL Server 2008引入透明數據加密（Transparent Data Encryption），即TDE
--它允許你完全無需修改應用程序代碼而對整個數據庫加密。
--
--當一個用戶數據庫可用且已啟用TDE時，在寫入到磁盤時在頁級實現加密。在數據頁讀入內存時解密。
--如果數據庫文件或數據庫備份被盜，沒有用來加密的原始證書將無法訪問。
--這幾乎是SQL Server2008安全選項中最激動人心的功能了，有了它，我們至少可以將一些初級的惡意窺視拒之見外。
--下面的兩個例子將展示如何啟用和維護透明數據加密
--示例一、啟用透明加密(TDE)
USE Master
GO
--刪除舊主密鑰
--Drop MASTER KEY
--GO
--創建主密鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='B19ACE32-AB68-4589-81AE-010E9092FC6B'
GO
--創建證書,用於透明數據加密
CREATE CERTIFICATE TDE_Server_Certificate
WITH SUBJECT = 'Server-level cert for TDE'
GO
USE [pratice]
GO
--第一步：現在開始透明加密
CREATE DATABASE ENCRYPTION KEY--創建數據庫加密密鑰
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
--查看數據庫是否加密
SELECT is_encrypted FROM sys.databases
WHERE name = 'pratice'
--注意：一旦在數據庫應用了加密，應該立刻備份服務器級證書！
--沒有加密dek的證書，該數據庫將無法打開，附加到別的服務器也無法使用，數據庫文件亦不會被Hack。
--如果一個DBA想要合法地將數據庫從一個SQL Server實例移動到另一個SQL Server實例，
--那麼她應該首先備份服務器級證書，然後在新的SQL Server實例中創建證書。
--此時可以合法地備份、還原數據庫或附加數據及日志文件。
--示例二、管理和移除透明加密(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
/*
對所有用戶數據庫的加密處理也包含對tempdb的處理 ，因為表連接，臨時表都要用到tempdb
databasenm encryption_state key_algorithm key_length
tempdb Encrypted AES 256
DB_Encrypt_Demo Encrypted AES 128
*/
--除了更改dek的算法，我們也可以更改用來加密數據庫的服務器級證書（該證書應該定期更改）
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
--移除數據庫透明加密
ALTER DATABASE [pratice]
SET ENCRYPTION OFF
GO
--移除TDE後，可以刪除DEK
USE [pratice]
GO
DROP DATABASE ENCRYPTION KEY
Go
--注意：如果刪除DEK是SQL Server實例中最後一個使用TDE的用戶定義數據庫，
--在SQL Server實例重啟後，tempdb也將變為不加密的狀態。
--
--小結：
--
--1、本文主要介紹透明數據加密(TDE)的使用。
--
--2、對DEK的修改同時影響到tempdb數據庫的加密狀態。
------------------------------------------------------------------------------------------------
--SQLServer 2008中的代碼安全（七） 證書加密
--
--證書可以在數據庫中加密和解密數據。
--證書包含密鑰對、關於證書擁有者的信息、證書可用的開始和結束過期日期。
--
--證書同時包含公鑰和密鑰，前者用來加密，後者解密。SQL Server可以生成它自己的證書，
--也可以從外部文件或程序集載入。因為可以備份然後從文件中載入它們，證書比非對稱密鑰更易於移植，
--而非對稱密鑰卻做不到。這意味著可以在數據庫中方便地重用同一個證書。
--注意：證書和非對稱密鑰同樣的消耗資源。
--我們看一組例子：
--
--示例一、創建數據庫證書
--創建數據庫證書:CREATE SYMMETRIC KEY （http://msdn.microsoft.com/en-us/library/ms187798.aspx）
USE [pratice]
GO
--創建證書
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
--示例二、查看數據庫中的證書
--使用目錄視圖sys.certificates(http://msdn.microsoft.com/en-us/library/ms189774.aspx)來查看。
--查看當前數據庫中的證書
USE [pratice]
go
--查看證書
SELECT name, pvt_key_encryption_type_desc, issuer_name
FROM sys.certificates
--示例三、備份和還原證書
--創建證書後，也可以使用BACKUP CERTIFICATE（http://msdn.microsoft.com/en-us/library/ms178578.aspx）
--命令備份到文件，為了安全地保存或在其他數據庫中還原它。
--備份證書
BACKUP CERTIFICATE cert_Demo
TO FILE = 'D:\certDemo.BAK'--證書備份路徑,用來加密
WITH PRIVATE KEY (FILE='D:\certDemoPK.BAK',--證書私鑰文件路徑，用來解密
ENCRYPTION BY PASSWORD = '465!!!',--加密私鑰密碼
DECRYPTION BY PASSWORD = '123!!!' )--解密私鑰密碼
--備份後，可以在其他數據庫中使用這個證書，或使用DROP CERTIFICATE命令刪除它。
DROP CERTIFICATE cert_Demo
GO
--從備份文件中還原證書到數據庫中
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)
--命令為證書增加或刪除私鑰。
--這個命令允許刪除私鑰（默認通過數據庫主密鑰加密）、增加私鑰或修改私鑰的密碼。
--從證書中刪除私鑰
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')
--示例五、使用證書加密和解密。
--
--使用函數EncryptByCert加密數據。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)
USE [pratice]
GO
CREATE TABLE PWDQuestion
(
CustomerID INT ,
PasswordHintQuestion NVARCHAR(200) ,
PasswordHintAnswer NVARCHAR(200)
)
--插入測試數據
INSERT dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
VALUES ( 10, '您出生的醫院名稱?', 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
--示例六、使用對稱密鑰對數據進行加密和解密。
--在前面的文章中，你已經看到打開用非對稱密鑰加密的對稱密鑰的演示，
--它分兩個步驟，首先用OPEN SYMMETRIC KEY命令，然後是實際的DecryptByKey函數調用。
--SQL Server也提供了能夠將這兩個步驟合二為一的額外的解密函數：
--DECRYPTBYKEYAUTOASYMKEY（http://msdn.microsoft.com/en-us/library/ms365420.aspx）
--和DecryptByKeyAutoCert（http://msdn.microsoft.com/en-us/library/ms182559.aspx）
USE [pratice]
GO
--本例使用數據庫主密碼加密，因而不需要密碼。
----Create master Key Encryption By password='123ASD!'
----go
--創建非對稱密鑰
CREATE ASYMMETRIC KEY asymDemo_V2
WITH ALGORITHM = RSA_512
--創建對稱密鑰
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, '您出生的醫院名稱?', ENCRYPTBYKEY(KEY_GUID('sym_Demo_V2'), '邵逸夫醫院') )
CLOSE SYMMETRIC KEY sym_Demo_V2
--此時，使用DecryptByKeyAutoAsymKey解密數據，只需要一個操作
SELECT CAST(DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('asymDemo_V2'), NULL,
PasswordHintAnswer) AS VARCHAR)
FROM dbo.PWDQuestion
WHERE CustomerID = 22
--小結：
--
--1、本文主要介紹證書的創建、刪除、查看以及用它來修改加密方式、進行數據的加密和解密。
--
--2、證書加密和非對稱密鑰加密相對對稱密鑰加密更為消耗資源。
--
--下文將主要介紹SQL Server中最為令人鼓舞的透明數據加密(TDE)
-----------------------------------------------------------------------------------------
--SQL Server 2008中的代碼安全（六）：對稱密鑰加密
--證書和非對稱密鑰使用數據庫級的內部公鑰加密數據，並且使用數據庫級內部私鑰解密數據。
--而對稱密鑰相對簡單，它們包含一個同時用來加密和解密的密鑰。
--困此，使用對稱密鑰加密數據更快，並且用在大數據時更加合適。
--盡管復雜度是考慮使用它的因素，但它仍然是一個很好的加密數據的選擇。
--示例一、創建對稱密鑰
--
--對稱密鑰的特性是：在數據庫會話中使用它對數據進行加密和解密前必須首先打開。
--
--創建對稱密鑰使用如下命令:CREATE SYMMETRIC KEY 創建對稱密鑰。
--（http://msdn.microsoft.com/en-us/library/ms188357.aspx）
USE [pratice]
go
-- 創建一個用於加密對稱密鑰的非對稱密鑰
CREATE ASYMMETRIC KEY symDemoKey --名稱
WITH ALGORITHM = RSA_512 --加密算法
ENCRYPTION BY PASSWORD ='TestSYM456!'
--密碼
-- 創建一個對稱密鑰
CREATE SYMMETRIC KEY sym_Demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY symDemoKey
--示例二、查看當前數據庫中的對稱密鑰
--
--使用目錄視圖sys.symmetric_keys(http://msdn.microsoft.com/en-us/library/ms189446.aspx)來查看。
--查看當前數據庫中的非對稱密鑰
USE [pratice]
go
SELECT name, algorithm_desc
FROM sys.symmetric_keys
--示例三、修改非對稱密鑰的加密方式
--
--你可以使用ALTER SYMMETRIC KEY（http://technet.microsoft.com/en-us/library/ms189440.aspx）命令修改
--對稱密鑰的加密方式。
--但執行前必須使用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
--示例四、使用對稱密鑰對數據進行加密和解密。
--
--1、為了使用對稱密鑰對數據進行加密，必須首先打開它，
--然後使用函數EncryptByKey 加密數據。(http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)
--2、使用DecryptByKey來解密使用對稱密鑰加密的數據。注意DecryptByKey不像EncryptByKey，
--無須使用對稱密鑰GUID。因此，為了解密，必須打開正確的對稱密鑰會話，否則會顯示null。
--
--下面是一個例子：
USE [pratice]
GO
--DROP TABLE [dbo].[PWDQuestion]
--GO
--創建測試數據表，用於對稱加密
CREATE TABLE dbo.PWDQuestion
(
CustomerID INT NOT NULL PRIMARY KEY ,
PasswordHintQuestion NVARCHAR(300) NOT NULL ,
PasswordHintAnswer VARBINARY(200) NOT NULL
)
GO
--插入加密數據
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD ='newnew!456'
INSERT dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
VALUES ( 12, '您出生的醫院名稱？', ENCRYPTBYKEY(KEY_GUID('sym_Demo '), '杭州市一') )
CLOSE SYMMETRIC KEY sym_Demo
--查看未加密的數據：
--解密數據
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列作為驗證列也一起加密，以絕後患 !
--
--注意：加密的驗證列也可以由另一個相關表的列作為參數傳入。
--示例五、刪除對稱密鑰
--命令：DROP SYMMETRIC KEY 刪除指定的對稱密鑰( http://technet.microsoft.com/en-us/library/ms182698.aspx)
--
--例子：
DROP SYMMETRIC KEY sym_Demo
--注意：如果加密密鑰打開沒有關閉，則drop失敗。
--小結：
--
--1、本文主要介紹對稱密鑰的創建、刪除、查看以及用它來修改加密方式、進行數據的加密和解密。
--
--2、對稱密鑰的特性是：在數據庫會話中使用它對數據進行加密和解密前必須首先打開。
--
--3、對稱密鑰可用於大數據的加密。
-------------------------------------------------------------------------------------------
--SQL Server 2008中的代碼安全（四）：主密鑰
--在SQL Server中的加密由層次結構形式進行處理以提供多級別的安全。
--SQL Server包含兩個用於加密數據的密鑰類型。如下圖：
--1、服務器主密鑰（Service Master Key）,位於層次結構的最頂端，並且在安裝SQL Server時自動創建，
--用於加密系統數據、鏈接的服務器登錄名以及數據庫主密鑰。
--在第一次通過SQL Server使用服務主密鑰來加密證書、數據庫主密鑰或鏈接的服務器主密碼時，
--服務主密鑰會自動生成，並且使用SQL Server服務賬戶的Windows證書來生成它。
--如果必須改變SQL Server服務賬號，微軟建議使用SQL Server配置管理器，
--因為這個工具將執行生成新服務主密鑰需要的合適的解密和加密方法，而且可以使加密層次結構保持完整。
--服務主密鑰也用於加密其下的數據庫主密鑰。
--
--2、數據庫主密鑰（Database Master Key）,用於加密證書，以及非對稱密鑰和對稱密鑰。
--所有數據庫都可以只包含一個數據庫主密鑰，在創建它時，通過服務主密鑰對其加密。
--創建非對稱密鑰時，可以決定在加密非對稱密鑰對應的私鑰是否包含密碼。
--如果示包含密碼，將使用數據庫主密鑰來加密私鑰。
--
--我們看一組例子：
--
--示例一、備份及還原服務主密鑰
--例一、備份及還原服務主密鑰
--
--用到以下兩個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
--如果該密鑰沒有實際變化，而執行密鑰恢復時，會收到提示：
--The old and new master keys are identical. No data re-encryption is required.
--示例二、創建、再生成和刪除數據庫主密鑰
--
--用到以下兩個sql命令:
--
--CREATE MASTER KEY 創建數據庫主密鑰（http://technet.microsoft.com/zh-cn/library/ms174382.aspx）
--
--ALTER MASTER KEY 重新生成數據庫主密鑰（http://msdn.microsoft.com/en-us/library/ms186937%28SQL.90%29.aspx）
--
--DROP MASTER KEY 刪除數據庫主密鑰（http://msdn.microsoft.com/en-us/library/ms180071.aspx）
--
--當數據庫主密鑰被顯式創建時，會同時自動生成一個額外生成的安全層，
--用於加密數據庫中的新證書和非對稱密鑰，更進一步保護已加密的數據。
IF NOT EXISTS ( SELECT name
FROM sys.databases
WHERE name = 'BookStore' )
BEGIN
CREATE DATABASE BookStore
END
GO
USE BookStore
GO
--創建數據庫主密鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
go
USE BookStore
GO
--重新生成數據庫主密鑰
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD ='password'
--刪除數據庫主密鑰
USE BookStore
GO
DROP MASTER KEY
--注意：如果該數據庫主密鑰仍然被其他數據庫對象使用，則不能被刪除，這點與架構類似。
--
--同時一旦創建數據庫主密鑰，就立刻備份它是一個好的習慣。
--備份數據庫主密鑰
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!!!'
--恢復數據庫主密鑰
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.
--示例三、從數據庫主密鑰刪除服務主密鑰
--
--當一個數據庫主密鑰被創建時，它被默認使用兩種方式加密：
--服務主密鑰和被使用CREATE MASTER KEY 命令中使用的密碼。
--如果你不想使用服務主密碼加密數據庫主密鑰
--（這種情況下，擁有sysadmin特權的login在不知道數據庫主密鑰的前提下將不能訪問加密數據），
--你可以使用ALTER MASTER KEY 命令刪除服務主密鑰。
--
--簡略語法如下：
ALTER MASTER KEY
ADD ENCRYPTION BY SERVICE MASTER KEY |
DROP ENCRYPTION BY SERVICE MASTER KEY
--由於服務主密鑰允許擁有足夠許可（如sysadmin）的用戶自動使用數據庫主密鑰解密，
--因此，一旦刪除了服務主密鑰的加密，而再想修改數據庫主密鑰時，你必須使用一個新的命令訪問它。
--OPEN MASTER KEY, 語法如下：
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
--下面是一個例子：
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
--一旦執行，任何數據庫主密鑰的修改需要使用OPEN MASTER KEY的口令訪問，
--這樣是為了重新應用服務主密鑰的加密
OPEN MASTER KEY DECRYPTION BY PASSWORD ='123456!'
--一旦服務主密鑰被用於加密數據庫主密鑰，數據庫主密鑰不再需要被顯式打開或關閉。
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
--關閉數據庫主密鑰
CLOSE MASTER KEY
--小結：
--
--1、本文主要介紹服務主密鑰的備份與還原，數據庫的主密鑰的創建、重新生成、刪除和備份、還原。
--
--2、一旦創建主密鑰，立刻備份它是一個很好的習慣。
------------------------------------------------------------------------------------
--SQL Server 2008中的代碼安全（三）：通過PassPhrase加密
--導讀：本文主要涉及EncryptByPassPhrase和DecryptByPassPhrase函數進行通行短語（PassPhrase）加密。
--前言：
--
--在SQL Server 2005和SQL Server 2008之前。
--如果希望加密敏感數據，如財務信息、工資或身份證號，必須借助外部應用程序或算法。
--SQL Server 2005引入內建數據加密的能力，使用證書、密鑰和系統函數的組合來完成。
--
--與數字證書類似。SQL Server 證書包括公鑰和私鑰這一對密鑰，它們都用來加密和解密數據。
--SQL Server也擁有創建非對稱密鑰和對稱密鑰對象的能力。非對稱密鑰（asymmetric key）與證書相似，
--公鑰用來加密數據庫，私鑰用來解密數據。
--非對稱密鑰和證書都提供了強大的加密強度。但在完成復雜的加密|解密過程中具有更多的性能開銷。
--更適合對大量數據進行加密，且具有較低性能開銷的解決方案是對稱密鑰（symmetric key），
--它是對相同數據進行加密和解密的一個密鑰。
--
--SQL Server允許將這些加密能力放到加密層次結構中。
--當安裝了SQL Server後，在數據庫master中創建名為服務主密鑰的服務器級別證書，
--並將其默綁定到SQL Server服務賬號登錄名。
--服務主密鑰用來加密所有其他數據庫證書和創建在SQL Server實例中的密鑰。
--另外，你也可以在用戶數據庫中創建數據庫主密鑰（Database Master Key）,
--它可以用來加密數據庫證書和密鑰。
--
--在SQL Server 2008中，微軟引入了透明數據加密(TDE),它對整個數據庫進行加密，
--而不需要修改任何訪問它的應用程序。數據、日志文件和相關的數據庫備份都是加密的。
--假如數據庫被偷，如果沒有數據庫加密密鑰（DEK）是不能訪問數據的。
--本文及後面幾篇文章將會舉例說明。
--在SQL Server 2008中，還引入了對可擴展密鑰管理（EKM）的支持，
--也就意味著SQL Server可以使用硬件安全模塊（HSM）來存儲和和管理加密密鑰。
--HSM可以減少數據和實際的加密密鑰耦合。
--此部分內容共分六篇文章：
--1、通過PassPhrase加密
--2、主密鑰
--3、非對稱密鑰加密
--4、對稱密鑰加密
--5、證書加密
--6、透明數據加密
--一、通過通行短語(PassPhrase)加密
--
--對於不涉及證書及密鑰的應急的數據加密，可以直接基於用戶提供的密碼來加密和解密數據。
--通行短語(PassPhrase)是允許存在空格的密碼。
--這個PassPhrase不會存儲在數據庫中，因而也就意味著不會被使用存儲的系統數據“破解”。
--同時，可以使用空格創建一個長的、易於記憶的句子來加密和解密敏感數據。
--我們需要了解的一對函數是ENCRYPTBYPASSPHRASE（http://technet.microsoft.com/zh-cn/library/ms190357.aspx）和DECRYPTBYPASSPHRASE（http://technet.microsoft.com/zh-cn/library/ms188910.aspx）
--
--這一對函數必須使用相同的參數。
--
--我們看一個示例：
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 --一定要二進制數據格式
)
GO
INSERT SecretInfo ( MySecret ) SELECT ENCRYPTBYPASSPHRASE('123456','你好啊')
SELECT MySecret FROM SecretInfo
--想知道原文本的內容，使用以下語句：
SELECT CAST(DECRYPTBYPASSPHRASE('123456', MySecret) AS VARCHAR(MAX))
FROM SecretInfo
--注意：
--
--1、使用通行短語進行加密數據，不用擔心sysadmin服務器角色成員讀取數據
--（在後面文章會看到，服務器角色成員sysadmin擁有讀取其他形式的加密數據的內在權限。)
--
--
--2、假定沒有將密碼存儲在表中或在任何模塊（存儲過程、觸發器等）中使用密碼，
--加密的數據將防止從數據庫備份被偷竊或在SQL Server實例中的數據庫中滲透。
--如果通行短語短語密碼洩漏，數據就可以被解密。
