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

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

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

--CREATE ASYMMETRIC KEY 

 

--以下語(yǔ)句創(chuàng)建一個(gè)非對(duì)稱密鑰asymDemoKey

use [pratice]
go

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

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

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

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

SELECT name, algorithm_desc, pvt_key_encryption_type_desc
FROM sys.asymmetric_keys

--示例三、修改非對(duì)稱密鑰的私鑰密碼

--你可以使用帶有ENCRYPTION BY PASSWORD和DECRYPTION BY PASSWORD選項(xiàng)的
--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!')--舊密碼是用來(lái)解密的

--示例四、使用非對(duì)稱密鑰對(duì)數(shù)據(jù)進(jìn)行加密和解密。
--
--由于同時(shí)需要公鑰和密鑰，在維護(hù)保密數(shù)據(jù)時(shí)使用非對(duì)稱密鑰來(lái)加密數(shù)據(jù)是非常安全的方式。
--但同時(shí)用于大數(shù)據(jù)集時(shí)將消耗更多的資源。
--
--不推薦使用非對(duì)稱密鑰對(duì)數(shù)據(jù)加密，但它仍然是一個(gè)選擇。一旦將非對(duì)稱密鑰加到數(shù)據(jù)庫(kù)，就可以用來(lái)加密和解密數(shù)據(jù)。
--
--用到以下兩個(gè)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）

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

use [pratice]
go

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

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

 

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

 

 

--示例五、刪除非對(duì)稱密鑰

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

DROP TABLE [dbo].[BankUser]

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

--SQL Server 2008引入透明數(shù)據(jù)加密（Transparent Data Encryption），即TDE
--它允許你完全無(wú)需修改應(yīng)用程序代碼而對(duì)整個(gè)數(shù)據(jù)庫(kù)加密。
--
--當(dāng)一個(gè)用戶數(shù)據(jù)庫(kù)可用且已啟用TDE時(shí)，在寫入到磁盤時(shí)在頁(yè)級(jí)實(shí)現(xiàn)加密。在數(shù)據(jù)頁(yè)讀入內(nèi)存時(shí)解密。
--如果數(shù)據(jù)庫(kù)文件或數(shù)據(jù)庫(kù)備份被盜，沒有用來(lái)加密的原始證書將無(wú)法訪問。
--這幾乎是SQL Server2008安全選項(xiàng)中最激動(dòng)人心的功能了，有了它，我們至少可以將一些初級(jí)的惡意窺視拒之見外。
--下面的兩個(gè)例子將展示如何啟用和維護(hù)透明數(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ù)庫(kù)加密密鑰
    WITH ALGORITHM = TRIPLE_DES_3KEY--加密方式
    ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate--使用服務(wù)器級(jí)證書加密
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.
*/

--第二步：打開加密開關(guān)
ALTER DATABASE [pratice] SET ENCRYPTION ON
GO

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

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

--沒有加密dek的證書，該數(shù)據(jù)庫(kù)將無(wú)法打開，附加到別的服務(wù)器也無(wú)法使用，數(shù)據(jù)庫(kù)文件亦不會(huì)被Hack。
--如果一個(gè)DBA想要合法地將數(shù)據(jù)庫(kù)從一個(gè)SQL Server實(shí)例移動(dòng)到另一個(gè)SQL Server實(shí)例，
--那么她應(yīng)該首先備份服務(wù)器級(jí)證書，然后在新的SQL Server實(shí)例中創(chuàng)建證書。
--此時(shí)可以合法地備份、還原數(shù)據(jù)庫(kù)或附加數(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

/*
對(duì)所有用戶數(shù)據(jù)庫(kù)的加密處理也包含對(duì)tempdb的處理 ，因?yàn)楸磉B接，臨時(shí)表都要用到tempdb
databasenm encryption_state key_algorithm key_length
tempdb Encrypted AES 256
DB_Encrypt_Demo Encrypted AES 128
*/

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

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ù)庫(kù)透明加密
ALTER DATABASE [pratice]
SET ENCRYPTION OFF
GO

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

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

--SQLServer 2008中的代碼安全（七） 證書加密
--
--證書可以在數(shù)據(jù)庫(kù)中加密和解密數(shù)據(jù)。
--證書包含密鑰對(duì)、關(guān)于證書擁有者的信息、證書可用的開始和結(jié)束過期日期。
--
--證書同時(shí)包含公鑰和密鑰，前者用來(lái)加密，后者解密。SQL Server可以生成它自己的證書，
--也可以從外部文件或程序集載入。因?yàn)榭梢詡浞萑缓髲奈募休d入它們，證書比非對(duì)稱密鑰更易于移植，
--而非對(duì)稱密鑰卻做不到。這意味著可以在數(shù)據(jù)庫(kù)中方便地重用同一個(gè)證書。

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

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

--創(chuàng)建數(shù)據(jù)庫(kù)證書: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ù)庫(kù)中的證書

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

--查看當(dāng)前數(shù)據(jù)庫(kù)中的證書
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ù)庫(kù)中還原它。
--備份證書
BACKUP CERTIFICATE cert_Demo
TO FILE = 'D:\certDemo.BAK'--證書備份路徑,用來(lái)加密
WITH PRIVATE KEY (FILE='D:\certDemoPK.BAK',--證書私鑰文件路徑，用來(lái)解密
ENCRYPTION BY PASSWORD = '465!!!',--加密私鑰密碼
DECRYPTION BY PASSWORD = '123!!!' )--解密私鑰密碼

--備份后，可以在其他數(shù)據(jù)庫(kù)中使用這個(gè)證書，或使用DROP CERTIFICATE命令刪除它。
DROP CERTIFICATE cert_Demo
GO
--從備份文件中還原證書到數(shù)據(jù)庫(kù)中
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)
--命令為證書增加或刪除私鑰。
--這個(gè)命令允許刪除私鑰（默認(rèn)通過數(shù)據(jù)庫(kù)主密鑰加密）、增加私鑰或修改私鑰的密碼。

--從證書中刪除私鑰
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)
)
--插入測(cè)試數(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

 

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

--在前面的文章中，你已經(jīng)看到打開用非對(duì)稱密鑰加密的對(duì)稱密鑰的演示，
--它分兩個(gè)步驟，首先用OPEN SYMMETRIC KEY命令，然后是實(shí)際的DecryptByKey函數(shù)調(diào)用。
--SQL Server也提供了能夠?qū)⑦@兩個(gè)步驟合二為一的額外的解密函數(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ù)庫(kù)主密碼加密，因而不需要密碼。
----Create master Key Encryption By password='123ASD!'
----go

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

--打開對(duì)稱密鑰，插入記錄
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

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

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

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

 

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

USE [pratice]
go

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

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

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

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

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

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

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

--2、使用DecryptByKey來(lái)解密使用對(duì)稱密鑰加密的數(shù)據(jù)。注意DecryptByKey不像EncryptByKey，
--無(wú)須使用對(duì)稱密鑰GUID。因此，為了解密，必須打開正確的對(duì)稱密鑰會(huì)話，否則會(huì)顯示null。
--
--下面是一個(gè)例子：
USE [pratice]
GO
--DROP TABLE [dbo].[PWDQuestion]
--GO
--創(chuàng)建測(cè)試數(shù)據(jù)表，用于對(duì)稱加密
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
--打開后切記關(guān)閉?。?！
CLOSE SYMMETRIC KEY sym_Demo

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

--至此，好像已經(jīng)大功告成了，別，千萬(wàn)別高興得太早！

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

--示例五、刪除對(duì)稱密鑰

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

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

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

--以下語(yǔ)句備份服務(wù)主密鑰到C:\SqlBackup\SMK.bak

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

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

 

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

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

--示例二、創(chuàng)建、再生成和刪除數(shù)據(jù)庫(kù)主密鑰
--
--用到以下兩個(gè)sql命令:
--
--CREATE MASTER KEY 創(chuàng)建數(shù)據(jù)庫(kù)主密鑰（http://technet.microsoft.com/zh-cn/library/ms174382.aspx）
--
--ALTER MASTER KEY 重新生成數(shù)據(jù)庫(kù)主密鑰（http://msdn.microsoft.com/en-us/library/ms186937%28SQL.90%29.aspx）
--
--DROP MASTER KEY 刪除數(shù)據(jù)庫(kù)主密鑰（http://msdn.microsoft.com/en-us/library/ms180071.aspx）
--
--當(dāng)數(shù)據(jù)庫(kù)主密鑰被顯式創(chuàng)建時(shí)，會(huì)同時(shí)自動(dòng)生成一個(gè)額外生成的安全層，
--用于加密數(shù)據(jù)庫(kù)中的新證書和非對(duì)稱密鑰，更進(jìn)一步保護(hù)已加密的數(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ù)庫(kù)主密鑰
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
go

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

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

--注意：如果該數(shù)據(jù)庫(kù)主密鑰仍然被其他數(shù)據(jù)庫(kù)對(duì)象使用，則不能被刪除，這點(diǎn)與架構(gòu)類似。
--
--同時(shí)一旦創(chuàng)建數(shù)據(jù)庫(kù)主密鑰，就立刻備份它是一個(gè)好的習(xí)慣。
--備份數(shù)據(jù)庫(kù)主密鑰
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!!!'

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

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

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

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

ALTER MASTER KEY

ADD ENCRYPTION BY SERVICE MASTER KEY |

DROP ENCRYPTION BY SERVICE MASTER KEY

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

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

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

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

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

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

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

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

--導(dǎo)讀：本文主要涉及EncryptByPassPhrase和DecryptByPassPhrase函數(shù)進(jìn)行通行短語(yǔ)（PassPhrase）加密。

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

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

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

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

--我們需要了解的一對(duì)函數(shù)是ENCRYPTBYPASSPHRASE（http://technet.microsoft.com/zh-cn/library/ms190357.aspx）和DECRYPTBYPASSPHRASE（http://technet.microsoft.com/zh-cn/library/ms188910.aspx）
--
--這一對(duì)函數(shù)必須使用相同的參數(shù)。
--
--我們看一個(gè)示例：
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  --一定要二進(jìn)制數(shù)據(jù)格式
)
GO

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

SELECT  MySecret FROM    SecretInfo

 

--想知道原文本的內(nèi)容，使用以下語(yǔ)句：

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

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