2012年5月17日 星期四

SQL Server 欄位資料加解密介紹與使用

        企業中資料庫通常都有存放客戶的基本資料,為了保護這些資料,通常都是透過權限的控管,讓限定的使用者可以存取資料庫,但是這樣還是有資料外洩的可能性,所以通常會再資料進行加密的動作,在SQL Server 2005以前,必須透過外部的程式將資料加密後,再將資料寫回,但是幸運的,SQL Server 2005就新增了對稱與非對稱式的加密方法,讓使用者可以透過內建函數就可以達成欄位資料加解密的動作。

在對稱式與非對稱式加解密的選擇上,簡單的區分,如果是在OLTP的系統上,比較不建議使用非對稱式,因為非對稱式較耗損資源,容易加重系統的負荷,而對稱式就會是比較好的選擇,因為又可以達到資料加密,而且對效能的影響相對的又比較的少,底下是SQL Server本身的加解密架構圖,下列我以範例分別進行說明。

關於對稱式與非對稱式金鑰的比較分析,請參考下列的連結。

對稱和非對稱式加密的描述
http://support.microsoft.com/kb/246071


在測試進行前,請先透過下列的語法建立測試資料表:
CREATE TABLE [dbo].[Customer](
[sn] [smallint] NOT NULL,
[name] [nvarchar](50) NOT NULL,
[sid] [varchar](15) NOT NULL,
[e_sid] [varbinary](max) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[sn] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


1、Master Key 管理與建立

1-1 每一個資料庫皆會對應一把Master Key,你可以透過下列的語法確認目前的資料庫是否有包含 Master Key。
select database_id, name, is_master_key_encrypted_by_server
from sys.databases

1-2 Create Master Key:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

1-3 Backup Master key:
BACKUP MASTER KEY TO FILE = 'phyical path\mk_name'
ENCRYPTION BY PASSWORD = 'password'

PS:密碼的部份必須符合複雜性檢查,而且請你一定要記得備份Master Key,以免日後發生問題時,無法進行資料解密的動作。

1-4 Restore Master Key:
在還原Master Key之前,必須先將Master Key開啟,然後再將Master Key還原,如果無法還原時,只須在還原時加上Force的參數即可。
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
GO

RESTORE MASTER KEY
FROM FILE = 'phyical path\mk_name'
DECRYPTION BY PASSWORD = 'password'
ENCRYPTION BY PASSWORD = 'password'
GO

CLOSE MASTER KEY;
GO

Force選項說明: Specifies that the RESTORE process should continue, even if the current database master key is not open, or if SQL Server cannot decrypt some of the private keys that are encrypted with it.


2、憑證(Certificate)管理與建立
憑證主要相依於Master Key,所以在建立憑證前,請記得一定要先進行第一個程序。

2-1 查詢目前資料庫中已建立的憑證
select * from sys.certificates

2-2 Create Certificate:
CREATE CERTIFICATE cary_test_cert
WITH SUBJECT = 'cary test',
EXPIRY_DATE = '20180430';
GO

PS:憑證日期的部份,你可以設定一個較久的時間,但是就算是憑證過期也還是可以使用,此日期根據官方說法主要是提供其他的程式進行確認,所以不必擔心。

參考連結:
CREATE CERTIFICATE (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms187798(v=sql.90).aspx

2-3 Backup Certificate:
BACKUP CERTIFICATE cary_test_cert TO FILE = 'phyical path\certificate_name'
WITH PRIVATE KEY ( FILE = 'phyical path\pk_name' ,
ENCRYPTION BY PASSWORD = 'password');
GO

2-4 Create Certificate from backup file:
CREATE CERTIFICATE cary_test_cert
FROM FILE = 'phyical path\certificate_name'
WITH PRIVATE KEY (FILE = 'phyical path\pk_name',
DECRYPTION BY PASSWORD = 'password');
GO


3、對稱式金鑰使用與管理

3-1 查詢目前資料庫中已建立的對稱式金鑰
select * from sys.symmetric_keys

3-2 Create Symmetric Key
CREATE SYMMETRIC KEY cary_test_sym_key WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE cary_test_cert;
GO

3-3 透過對稱式金鑰進行加密
OPEN SYMMETRIC KEY cary_test_sym_key
    DECRYPTION BY CERTIFICATE cary_test_cert;

UPDATE cary_test.dbo.Customer
SET e_sid
    = EncryptByKey(Key_GUID('cary_test_sym_key'), sid);
GO

CLOSE SYMMETRIC KEY cary_test_sym_key

3-4 透過對稱式金鑰進行解密
OPEN SYMMETRIC KEY cary_test_sym_key
   DECRYPTION BY CERTIFICATE cary_test_cert;
GO

SELECT sn, name, sid, e_sid,
    CONVERT(varchar, DecryptByKey(e_sid))
    AS 'decrypt_e_sid'
    FROM cary_test.dbo.Customer;
GO

CLOSE SYMMETRIC KEY cary_test_sym_key

3-5 對稱式金鑰主要透過憑證進行產生,金鑰本身無法進行備份,而是在資料庫進行備份時也會一起進行備份,但是如果你有需要將資料庫單獨進行步署時,可以將憑證先行備份後,再還原到新的機器上即可。


4、非對稱式金鑰使用與管理

4-1 查詢目前資料庫中已建立的非對稱式金鑰
select * from sys.asymmetric_keys

4-2 Create Asymmetric Key
CREATE ASYMMETRIC KEY cary_test_asym_key
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = N'password'
GO

4-3 使用非對稱式金鑰
4-3-1 透過非對稱式金鑰加密
UPDATE cary_test.dbo.Customer
SET e_sid
    = EncryptByAsymKey(AsymKey_ID('cary_test_asym_key'), sid)
GO

4-3-2 透過非對稱式金鑰解密
SELECT sn, name, sid, e_sid,
CONVERT(varchar(max),
    DecryptByAsymKey( AsymKey_Id('cary_test_asym_key'),
    e_sid , N'password')) 'decrypt_e_sid'
FROM cary_test.dbo.Customer

4-3-3 透過非對稱式金鑰加密時沒有備份的方式,所以如果你有可能會要將資料庫或金鑰步署到其他台時,請使用憑證的方式進行加密。

4-4 使用非對稱式金鑰與憑證
4-4-1 使用非對稱式金鑰與憑證進行加密
UPDATE cary_test.dbo.Customer
SET e_sid =
EncryptByCert(Cert_ID('cary_test_cert'), sid)
GO

4-4-2 使用非對稱式金鑰與憑證進行解密
select sn, name, sid, e_sid,
convert(varchar(max),DecryptByCert(Cert_Id('cary_test_cert'),
e_sid)) 'decrypt_e_sid'
from cary_test.dbo.Customer


5、權限設定
對於加解密的部份需要嚴格的進行控管,所以請單獨設定允許特定的使用者可以進行加解密,以免同樣的造成資料的外洩。

5-1 對稱式金鑰權限設定
5-1-1 賦予權限:
GRANT CONTROL ON SYMMETRIC KEY::[key_name] TO [user_name]
GO

5-1-2 取消權限:
REVOKE CONTROL ON SYMMETRIC KEY::[key_name] TO [user_name]
GO


5-2 對稱式金鑰權限設定
5-2-1 賦予權限:
GRANT CONTROL ON ASYMMETRIC KEY::[key_name] TO [user_name]
GO

5-2-2 取消權限:
REVOKE CONTROL ON ASYMMETRIC KEY::[key_name] TO [user_name]
GO


5-3 對稱式金鑰權限設定
5-3-1 賦予權限:
GRANT CONTROL ON CERTIFICATE::[certificate_name] TO [user_name]
GO

5-3-2 取消權限:
REVOKE CONTROL ON CERTIFICATE::[ certificate _name] TO [user_name]
GO



參考連結:

關鍵字:CertificatesMaster KeySymmetric keyAsymmetric key

8 則留言:

  1. 大大不好意思~想請教您一個問題,我使用您的方法

    在Server A上進行加密,資料庫可正常運作、進行加解密
    但是複製一份資料庫、憑證、私密金鑰到新的伺服器Server B上
    還原憑證、私密金鑰後原本有加密的欄位,查出來資料都變成NULL

    嘗試在Server A把憑證、私密金鑰刪除,再用備份的憑證、私密金鑰檔還原,可以還原,但是進行查詢,查出來的資料也都變成NULL了

    不知道您有遇到過這樣的問題、解決的方法嗎?
    環境是win2008、sql2012exp

    感謝您

    回覆刪除
    回覆
    1. Hello Simonban:

      請問一下您用的加密方式為何,可否把你的語法貼上來我確認看看,之前接的case上,通常是語法的錯誤或誤用所造成。

      另外如果你透過我提供的範例進行時也會有錯誤產生嗎?

      刪除
    2. 大家好~
      我目前遇到的問題跟sim好像一樣,把在serverA備份出來的MASTER KEY與CERTIFICATE 還原到serverB,那請問SYMMETRIC KEY是要自己產生嗎?
      我用還原好的憑證來產生金鑰,卻無法將在serverA加密的欄位解密,查詢出來都是null,請問我還有甚麼動作沒做到嗎?

      謝謝~

      刪除
  2. caryhsu 您好:
    請教您一下關於金鑰的管理
    在加密完欄位後,理論上是要 Backup 憑證和 DMK 然後在資料庫 DROP 他們,當需要解密欄位在重新 Restore、Create 回來是否比較安全?

    但當我用憑證加密對稱金鑰,在 Drop 憑證和金鑰後,把憑證從備份的 File Create 回來,對稱金鑰也要重新用此憑證 Create 回來,然後才可以解密?

    USE AdventureWorks2012;
    CREATE CERTIFICATE Shipping11
    FROM FILE = 'c:\Shipping\Certs\Shipping11.cer'
    WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk',
    DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');
    GO

    我不太懂在備份憑證時 PRIVATE KEY 扮演的角色是什麼,似乎是加密此備份憑證的 Key?

    回覆刪除
    回覆
    1. 關於你的第一個問題,取決在於你何時要解密資料,與解密資料的人是否有權限restore與create key而定,如果是一般前端的應用程式,正常的情況下,不該有權限進行這樣的動作,但主要還是看你的需求而定。

      關於第二個問題,你可以參考下列的說明,你應該就會更清楚了。 ^^

      Certificates
      A public key certificate, usually just called a certificate, is a digitally-signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key. Certificates are issued and signed by a certification authority (CA). The entity that receives a certificate from a CA is the subject of that certificate. Typically, certificates contain the following information.

      Encryption Hierarchy
      http://msdn.microsoft.com/en-us/library/ms189586(v=sql.90).aspx

      刪除
  3. CREATE ASYMMETRIC KEY cary_test_asym_key
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = N'password '
    GO

    N'password ' 後面多了一個空格。 造成後面 select 產生錯誤。

    回覆刪除