SQL Server 加密解密策略

6.5  加密金鑰和內建加密函式

工業和政府開始要求對資料庫資料進行加密。在美國，健康保健和信用卡行業最先受到影響。根據新的法律，例如，醫生不能私自訪問其他醫生的病人記錄。在SQL Server 2005以前，資料加密僅僅由第三方的加密產品來支援。在SQL Server 2005中，資料加密是一個內建功能。有時候，資料加密在其他資料庫的列級別上實現，具體做法是使用DDL擴充套件語句宣告要加密的列。對於醫生，這種型別的加密不夠充分，除非每個醫生的病人資訊被儲存在不同的表中。SQL Server 2005沒有實現列級加密，它提供的是資料加密函式。由開發者決定呼叫這些函式，在執行INSERT語句時手工加密資料，在執行SELECT語句時解密資料。

為了加密和解密資料，我們需要使用金鑰。所以首先需要回答的兩個問題是“金鑰將存放在哪裡？”，“金鑰本身是如何加密的？”。在討論加密函式之前，我們需要先回答這些問題。

在很多情況下，SQL Server 2005通過使金鑰成為資料庫物件並通過正常的DDL語句儲存和管理它們。金鑰和資料庫捆綁在一起，加密物件分等級配置。這個鏈條的最頂端是伺服器主機金鑰，這個金鑰在安裝SQL Server 2005時自動生成，是使用DPAPI(資料保護API，一個Windows作業系統特徵)生成和儲存的。這個金鑰儲存在master資料庫中。金鑰的生成首先使用執行SQL Server服務的使用者。稍後將討論SQL Server服務的使用者發生改變時對金鑰有何影響以及如何備份和還原金鑰。每一個資料庫都有一個資料庫主金鑰。對每一個新的資料庫，資料庫主金鑰不是自動生成的是可選的。可以使用CREATE MASTER KEY DDL語句手工生成，如下所示：

-- Generating the database master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPW#'

資料庫主金鑰是一個128位元組的DES3金鑰。資料庫主金鑰生成時，被儲存在兩個地方。一個副本使用服務主金鑰加密並儲存在master資料庫；另一個副本使用提供的密碼進行加密並儲存在資料庫本身中。使用Service Broker特性時，加密會話之類的功能需要有資料庫主金鑰。有關Service Broker的詳情，參見第11章。

SQL Server可以儲存的三種金鑰型別分別是X.509證照、對稱金鑰和非對稱金鑰。這些金鑰在資料庫級定義，它們不是資料庫架構的一部分。對稱金鑰和非對稱金鑰用來加密和解密資料或其他金鑰。證照用來加密資料和金鑰，但也可用於SQL Server 2005的其他功能，如在HTTP終端上的SSL加密。SQL Server 2005可以生成它自己擁有的證照，也可以使用外部源頭生成的證照。使用由外部源頭生成的證照時，它不使用證照認證鏈、過期策略以及撤銷列表。可以用下面的DDL語句生成一個證照(最好為期一年)：

-- Generating a certificate,

-- no password, so encrypted by database master key

CREATE CERTIFICATE mycert

 AUTHORIZATION certuser WITH SUBJECT = 'Cert for certuser'

每種型別的金鑰可以用不同的機制來保護。表6.2給出了每類金鑰保護的可能性。①

表6.2  SQL Server安全性金鑰的加密選擇

保密型別	可以使用的加密方法	多個可能的加密
(資料庫)主金鑰	密碼(1個或多個)，服務主金鑰(預設的，但它是可選的，並且可以被刪除)	是
證照(私有金鑰)	資料庫主金鑰，密碼	否
非對稱金鑰(私有金鑰)	資料庫主金鑰，密碼	否
對稱金鑰	密碼，證照，非對稱金鑰，對稱金鑰	是

使用DDL語句建立對稱金鑰和非對稱金鑰時，要指定金鑰、擁有者、金鑰使用的加密演算法，以及用來加密金鑰本身的機制。SQL Server支援不同的加密演算法。

現在我們放下基本資訊，排練一下使用加密金鑰的場景並完整給出所涉及的DDL。

一張表包含一個加密資料列。User1和User2將只能夠看到自己的加密資料，Admin1能夠看到由User1和User2加密的資料。程式清單6.2闡述瞭如何建立相應的對稱金鑰和證照以保護它們。每一個證照和對稱金鑰被相應的使用者擁有，如程式清單6.2所示。Admin1(管理員)必須能訪問這兩個金鑰和兩個證照。

程式清單6.2  建立由證照保護的對稱金鑰

-- Generate a certificate for user1 and user2

CREATE CERTIFICATE User1cert

   AUTHORIZATION User1 WITH SUBJECT = 'Cert for User1'

CREATE CERTIFICATE User2cert

   AUTHORIZATION User2 WITH SUBJECT = 'Cert for User2'

GO

-- symmetric keys to be used for encryption

-- they are faster than certificates

CREATE SYMMETRIC KEY Key1 AUTHORIZATION User1

  WITH ALGORITHM = TRIPLE_D ENCRYPTION BY CERTIFICATE User1cert

CREATE SYMMETRIC KEY Key2 AUTHORIZATION User2

  WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE User2cert

GO

服務主金鑰、資料庫主金鑰和證照必須像其他資料庫物件那樣備份、還原和維護。可以使用資料庫來備份和還原證照、對稱金鑰和非對稱金鑰。證照也可以分別備份到磁碟或從磁碟還原。可以為證照提供只備份和還原公鑰或公鑰-私鑰對。資料庫主金鑰不過是另一種對稱金鑰，但有專門的DDL來建立和操縱它。分離一個有資料庫主金鑰的資料庫並把這個資料庫附加到另一個例項時，需要開啟資料庫主金鑰(通過一個密碼)並使用新例項的服務主金鑰加密它。也可以使用一個資料庫主金鑰，而不使用服務主金鑰來加密資料庫主金鑰並將其儲存在主資料庫中。詳情參見後文。

服務主金鑰是特殊的，因為：

它和執行SQL Server服務程式的使用者繫結

它用於加密資料庫主金鑰

它用於加密伺服器範圍內的其他認證，例如認證證照

可以像資料庫主金鑰和證照一樣，使用DDL語句來備份和還原服務主金鑰。但是，如果沒有顯式解密和重新加密服務主金鑰，則不能更改執行SQL Server服務程式使用者的標識。否則會使所有的金鑰不可用。SQL Server Configuration Manager包含一個特殊函式，用於改變SQL Server服務的一個使用者。你必須使用這個實用工具來保護金鑰的可行性。

6.6  加 密 函 數

既然瞭解了什麼是金鑰，讓我們討論一下用於資料加密的函式。為此用途定義的15T_SQL內建函式和一個相關的輔助函式如下所示：

EncryptByAsymKey

DecryptByAsymKey

EncryptByCert

DecryptByCert

EncryptByPassPhrase

DecryptByPassPhrase

EncryptByKey

DecryptByKey

DecryptByKeyAutoCert

DecryptByKeyAutoAsymKey

SignByAsymKey

SignByCert

VerifySignedByAsymKey

VerifySignedByCert

HashBytes

首先，談談金鑰和加密/解密函式。對稱金鑰使用相同的金鑰來加密和解密。這提出了一個金鑰分佈的問題，因為分佈在雙方的金鑰必須沒有變動。然而，對於加密/解密而言，對稱金鑰比非對稱金鑰要快幾個數量級。非對稱金鑰使用一對金鑰來實現加密/解密。只有非對稱金鑰的擁有者才有私鑰部分，外部世界擁有公鑰部分。公鑰是公開的，以至於它們可以在金鑰目錄中釋出。非對稱金鑰對的一個用途是：用你的私鑰加密一些資料(比如一個合法文件)的雜湊。一個擁有公鑰的使用者可以重新計算雜湊並用你的公鑰解密被加密的雜湊並確認這個雜湊來自你，因為只有你擁有用來加密它的私鑰。這稱作簽名。外部世界可以使用你的公鑰加密資料來傳送給你，只有你有正確的私鑰來解密它。這就是所謂的資料封裝。非對稱金鑰加密和解密資料較慢，但常用來生成和保護一個在單個資料加密會話中使用的對稱的“會話金鑰”。證照僅僅是附加了後設資料的非對稱金鑰，這些後設資料包括金鑰發行者和終止日期等。SQL Server支援X509.V3證照，它可以儲存但不能使用X509.V3證照中的V3特殊欄位。總的說來，有三個常用的加密規則需要記住：

對稱金鑰比非對稱金鑰快

資料的數量越大①，加密和解密所花費的時間越長

通常，用來加密的金鑰越長，加密越安全，但花費的加密和解密時間也越長

讓我們使用在前面定義的用來加密一個表中部分資料的一套金鑰。我們將演示兩個使用者各自能看到他們自己擁有的資料的場景。除此之外，這裡有一個管理員，他可以看到兩個使用者的資料。首先，我們定義一個用於儲存一個加密欄位的表：

-- The primary key and name data are public

-- The secret_data column is encrypted

CREATE TABLE dbo.secret_table (

  id INT PRIMARY KEY IDENTITY,

  first_name VARCHAR(20),

  last_name VARCHAR(50),

  secret_data VARBINARY(8000)

)

GO

GRANT INSERT, SELECT ON dbo.secret_table TO User1, User2

GO

注意，這個表定義中並沒有指定secret_data是用來儲存加密資料的。事實上，同一個列可以用來儲存加密資料和非加密資料，儘管這樣可能導致一些難處理的SQL語句。列被定義成VARBINARY型別(這是要求)，但並不支援新資料型別VARBINARY(MAX)。但這一列事實上至少有多大？能儲存多少加密資料？儲存需要的最小容量可以使用程式清單6.3中的演算法計算出。加密演算法使用“一般”的VARCHAR資料型別，而不是新的VARCHAR(MAX)資料型別，所以加密資料的最大數量恰好低於8000位元組。②試圖加密任何更大的資料都將導致一個截斷錯誤。

程式清單6.3  計算加密資料所需欄位大小的演算法

CREATE FUNCTION dbo.CalculateCipherLen(

  @KeyName sysname, @PTLen int, @UsesHash int = 0 )

RETURNS int

as

BEGIN

  declare @KeyType      nvarchar(2)

  declare @RetVal int

  declare @BLOCK int

  declare @IS_BLOCK     int

  declare @HASHLEN      int

  -- Hash length that

  SET @HASHLEN    = 20

  SET @RetVal     = NULL

  -- Look for the symmetric key in the catalog

  SELECT @KeyType = key_algorithm

    FROM sys.symmetric_keys WHERE name = @KeyName

  -- If parameters are valid

  IF( @KeyType is not null AND @PTLen > 0)

    BEGIN

      -- If hash is being used. NOTE: as we use this value to

      -- calculate the length, we only use 0 or 1

  IF( @UsesHash <> 0 ) SET @UsesHash = 1

  -- 64 bit block ciphers

  IF( @KeyType = N'R2' OR @KeyType = N'D'

      OR @KeyType = N'D3' OR @KeyType = N'DX' )

    BEGIN

      SET @BLOCK = 8

      SET @IS_BLOCK = 1

    END

  -- 128 bit block ciphers

  ELSE IF( @KeyType = N'A1' OR @KeyType = N'A2' OR @KeyType = N'A3' )

    BEGIN

      SET @BLOCK = 16

SET @IS_BLOCK = 1

    END

    -- Stream ciphers, only RC4 is supported as a stream cipher

    ELSE

      BEGIN

       SET @IS_BLOCK = 0

      END

    -- Calculate the expected length.

    -- The formula is different for block ciphers & stream ciphers

    IF( @IS_BLOCK = 1 )

      BEGIN

      SET @RetVal =

        ( FLOOR( (8 + @PTLen + (@UsesHash * @HASHLEN) )/@BLOCK)+1 ) *

          @BLOCK + 16 + @BLOCK

      END

      ELSE

  BEGIN

            SET @RetVal = @PTLen + (@UsesHash * @HASHLEN) + 28

        END

      END

      RETURN @RetVal

END

GO

現在讓我們向表中放入一些資料。需要知道這個事實：加密演算法使作為一個索引或帶有UNIQUE，PRIMARY KEY和FOREIGN KEY列約束的列基本無用。

為了使用對稱金鑰，使用者不僅必須有使用它們的許可權，還必須顯式開啟金鑰。為了開啟金鑰，必須提供金鑰相應的密碼。這依賴於金鑰是如何使用CREATE和ALTER DDL語句來加密的。如果金鑰是使用資料庫主金鑰加密的，則只需要訪問金鑰。資料庫主金鑰本身可以自動開啟，只要它像本章前面描述的那樣(由服務主金鑰加密)儲存在master資料庫中。這是預設的。對於加密和解密，都需要開啟金鑰。讓我們為每一個使用者向資料庫中插入一行加密的資料。如程式清單6.4所示。

程式清單6.4  向表中插入資料的同時加密資料

-- insert rows into secret_table, using encryption

EXECUTE AS USER='user1'

OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE user1cert;

INSERT dbo.secret_table VALUES('Joe', 'User',

  EncryptByKey(Key_GUID('Key1'),'some secret number1'))

CLOSE SYMMETRIC KEY Key1

GO

REVERT

GO

EXECUTE AS USER='user2'

OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE user2cert;

INSERT dbo.secret_table VALUES('Jill', 'Smith',

  EncryptByKey(Key_GUID('Key2'),'some other secret number2'))

CLOSE SYMMETRIC KEY Key2

GO

REVERT

GO

用完金鑰後應該將金鑰關掉，儘管所有開啟的金鑰(稱作keyring)在會話結束是自動關閉的。即使是在使用連線池，情況也如此，但每個會話一個金鑰，並且和執行上下文無關。現在讓我們來獲取資料。

在沒有使用解密函式的情況下，如果試圖訪問包含了加密資料的列，則無論你是否開啟相應的解密密碼，返回的都是加密的資料。為了取得不加密的資料，必須在SELECT語句中使用一個加密演算法。一個簡單的替代方法是把它封裝在一個檢視裡。當解密函式被指定作為SELECT語句的一部分時，SQL Server試圖使用你所擁有的所有金鑰來解密。只要有一個金鑰成功，就會有正確的資料返回。否則，解密函式將返回空。程式清單6.5說明了這個問題。

程式清單6.5  只有可以訪問金鑰的使用者可以訪問資料

EXECUTE AS USER='user1'

OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE user1cert

-- you get the encrypted value

SELECT id, first_name, last_name, secret_data

  FROM secret_table

-- you either get the unencrypted value or NULL

SELECT id, first_name, last_name,

       cast(decryptByKey(secret_data) AS VARCHAR(256))

  FROM secret_table

CLOSE SYMMETRIC KEY Key1

GO

REVERT

GO

EXECUTE AS USER='user2'

OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE user2cert

-- you get the encrypted value

SELECT id, first_name, last_name, secret_data

  FROM secret_table

-- you either get the unencrypted value or NULL

SELECT id, first_name, last_name,

       cast(decryptByKey(secret_data) AS VARCHAR(256))

  FROM secret_table

CLOSE SYMMETRIC KEY Key2

GO

REVERT

GO

EXECUTE AS USER='admin1'

OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE user1cert

OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE user2cert

-- cyphertext

SELECT id, first_name, last_name, secret_data

  FROM secret_table

-- sees both rows decrypted

SELECT id, first_name, last_name,

       cast(decryptByKey(secret_data) AS VARCHAR(256))

  FROM secret_table

CLOSE ALL SYMMETRIC KEYS

GO

REVERT

GO

除了對每一類金鑰的加密/解密函式以及傳遞基於短語的加密/解密，在使用證照或非對稱金鑰時還有自動的解密函式。為了方便，也提供了這些函式，因為一個單獨的證照或非對稱金鑰有時用來加密表中的所有資料。如程式清單6.6所示。在這種情況下，你可以使用一個檢視來檢視錶，金鑰管理是自動進行的。

程式清單6.6  在一個檢視上使用DecryptByKeyAutoCert函式

-- Generate a certificate owned by DBO

CREATE CERTIFICATE somecert

   AUTHORIZATION DBO WITH SUBJECT = 'Cert for Encrypted column'

GRANT CONTROL ON CERTIFICATE::somecert TO lowpriv_user

GO

CREATE SYMMETRIC KEY somekey AUTHORIZATION DBO

  WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE somecert

GRANT VIEW DEFINITION ON SYMMETRIC KEY::somekey TO lowpriv_user

GO

CREATE TABLE dbo.secret_col_table (

  id INT PRIMARY KEY IDENTITY,

  secret_col VARBINARY(8000)

)

GO

OPEN SYMMETRIC KEY somekey DECRYPTION BY CERTIFICATE somecert

INSERT dbo.secret_col_table VALUES(

  EncryptByKey(Key_GUID('somekey'), 'more secret data'))

CLOSE SYMMETRIC KEY somekey

GO

CREATE VIEW dbo.public_view AS

 SELECT id, convert(varchar(100),

   DecryptByKeyAutocert(cert_id('somecert'), null, secret_col))

     AS dbo.secret_col

 FROM dbo.secret_col_table

GO

-- now the lowpriv_user can access the column

GRANT SELECT ON dbo.public_view TO lowpriv_user

GO

注意，使用一個定義使用自動解密函式的檢視時，低許可權的使用者不僅可以訪問檢視，而且還可以訪問證照(CONTROL)，能檢視(檢視定義)加密金鑰。可以使用一個幫助函式加以簡化，該函式使用了EXECUTE AS OWNER特徵。關於EXECUTE AS，詳見本章後文的描述。

 --示例一,使用證照加密資料.
 
 --建立測試資料表
 CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
 GO
 --建立證照一,該證照使用資料庫主金鑰來加密
 CREATE CERTIFICATE Cert_Demo1 
 WITH 
   SUBJECT=N'cert1 encryption by database master key',
   START_DATE='2008-01-01',
   EXPIRY_DATE='2008-12-31'
 GO
 
 --建立證照二,該證照使用密碼來加密
 CREATE CERTIFICATE Cert_Demo2
   ENCRYPTION BY PASSWORD='liangCK.123'
 WITH 
   SUBJECT=N'cert1 encrption by password',
   START_DATE='2008-01-01',
   EXPIRY_DATE='2008-12-31'
 GO
 
 --此時,兩個證照已經建立完,現在可以用這兩個證照來對資料加密
 --在對錶tb做INSERT時,使用ENCRYPTBYCERT加密
 
 INSERT tb(data)
   SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo1'),N'這是證照1加密的內容-liangCK');  --使用證照1加密
 
 INSERT tb(data)
   SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo2'),N'這是證照2加密的內容-liangCK');  --使用證照2加密
 
 
 --ok.現在已經對資料加密保證了.現在我們SELECT看看
 
 SELECT * FROM tb ;
 
 --現在對內容進行解密顯示.
 --解密時,使用DECRYPTBYCERT
 
 SELECT 證照1解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo1'),data)),
        --使用證照2解密時,要指定DECRYPTBYCERT的第三個引數,
        --因為在建立時,指定了ENCRYPTION BY PASSWORD.
        --所以這裡要通過這個密碼來解密.否則解密失敗
        證照2解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo2'),data,N'liangCK.123'))
 FROM tb ;
 
 --我們可以看到,因為第2條記錄是證照2加密的.所以使用證照1將無法解密.所以返回NULL
 
 /*
 證照1解密                                              證照2解密
 -------------------------------------------------- --------------------------------------------------
 這是證照1加密的內容-liangCK                                 NULL
 NULL                                               這是證照2加密的內容-liangCK
 
 (2 行受影響)
 */
 
 GO
 
 --刪除測試證照與資料表
 DROP CERTIFICATE Cert_Demo1;
 DROP CERTIFICATE Cert_Demo2;
 DROP TABLE tb;
 GO
 
 
 --示例二,使用對稱金鑰加密資料,
 --對稱金鑰又使用證照來加密.
 
 --建立測試資料表tb
 CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
 GO
 
 --建立證照,該證照用於加密對稱金鑰.
 
 CREATE CERTIFICATE Cert_Demo
   ENCRYPTION BY PASSWORD=N'liangCK.123'
 WITH
   SUBJECT=N'cert encryption by password',
   START_DATE='2008-01-01',
   EXPIRY_DATE='2008-12-31'
 GO
 
 --建立對稱金鑰
 
 CREATE SYMMETRIC KEY Sym_Demo
 WITH
    ALGORITHM=DES  --使用DES加密演算法
 ENCRYPTION BY CERTIFICATE Cert_Demo --使用Cert_Demo證照加密
 GO 
 
 --要使用Sym_Demo對稱金鑰.必需使用OPEN SYMMETRIC KEY來開啟它
 
 OPEN SYMMETRIC KEY Sym_Demo
    DECRYPTION BY CERTIFICATE Cert_Demo
       WITH PASSWORD=N'liangCK.123'
 
 --插入加密資料
 
 INSERT tb(data)
   SELECT ENCRYPTBYKEY(KEY_GUID(N'Sym_Demo'),N'這是加密的資料,能顯示出來嗎?')
   
 --關閉金鑰
 CLOSE SYMMETRIC KEY Sym_Demo
 
 --插入完加密資料,現在使用SELECT來查詢一下資料
 
 SELECT * FROM tb 
 
 GO
 
 --現在來解密此資料
 
 --同樣,還是要先開啟對稱金鑰
 
 OPEN SYMMETRIC KEY Sym_Demo
    DECRYPTION BY CERTIFICATE Cert_Demo
       WITH PASSWORD=N'liangCK.123'
 
 SELECT CONVERT(NVARCHAR(50),DECRYPTBYKEY(data)) --這裡可見,資料已經解密出來了.
 FROM tb 
 
 CLOSE SYMMETRIC KEY Sym_Demo
 GO
 
 --刪除測試
 DROP SYMMETRIC KEY Sym_Demo
 DROP CERTIFICATE Cert_Demo
 DROP TABLE tb
 
 
 
 --示例三,還有一種方法加密資料更簡單
 --就是使用EncryptByPassPhrase
 
 --建立測試資料表tb
 
 CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));
 GO
 
 INSERT tb(data)
   SELECT EncryptByPassPhrase(N'這是密碼,用來加密的',N'這是要加密的內容');
   
 --解密
 
 SELECT CONVERT(NVARCHAR(50),DECRYPTBYPASSPHRASE(N'這是密碼,用來加密的',data))
 FROM tb 
 
 GO
 DROP TABLE tb

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-545480/，如需轉載，請註明出處，否則將追究法律責任。