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



參考連結：

• Cryptography in SQL Server
  http://msdn.microsoft.com/en-us/library/cc837966(v=sql.100).aspx
• Encryption Hierarchy
  http://msdn.microsoft.com/en-us/library/ms189586(v=sql.90).aspx
• CREATE CERTIFICATE (Transact-SQL)http://msdn.microsoft.com/zh-tw/library/ms187798(v=sql.90).aspx
• CREATE ASYMMETRIC KEY (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms174430(v=sql.90).aspx
• Cryptographic Functions (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms173744(v=sql.90).aspx
• CREATE MASTER KEY (Transact-SQL)http://technet.microsoft.com/en-us/library/ms174382.aspx
• BACKUP MASTER KEY (Transact-SQL)http://technet.microsoft.com/en-us/library/ms174387.aspx
• BACKUP CERTIFICATE (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms178578(v=sql.90).aspx
• RESTORE MASTER KEY (Transact-SQL)http://msdn.microsoft.com/zh-tw/library/ms186336.aspx
• OPEN MASTER KEY (Transact-SQL)http://msdn.microsoft.com/zh-tw/library/ms174433.aspx
• CLOSE MASTER KEY (Transact-SQL)http://msdn.microsoft.com/zh-tw/library/ms188387.aspx
• How to backup asymmetric key in SQL 2005http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/0ce48171-b988-423d-9559-786245f7010c/
• SQL Server 2005: certificates vs asymmetric keyshttp://blogs.msdn.com/b/lcris/archive/2006/03/13/550904.aspx

關鍵字：Certificates、Master Key、Symmetric key、Asymmetric key