整合資料加密到資料庫安全設計Data Encryption

[實作快速摘要]
Step1.確認與建立資料庫主要金鑰(Database Master Key)
Step2.建立Certificate準備來加密Symmetric Key
Step3.建立Symmetric Key時使用Certificate來加密
Step4.資料表加入一個加密欄位,加密欄位型別Data Type建議設定為varbinary
Step5.資料寫入時,使用Symmetric Key來加密欄位
PS.詳細Code請看後面的[combine Certificate and Symmetric Key]

[SQL Server 2005的資料加密技術]
1.對稱式金鑰 Symmetric Key
只用一把Key,相對於Asymmetric Key與Certificate較有效率,
但Asymmetric Key與Certificate加密安全性較高
例如Service Master Key與Database Master Key皆為Symmetric Key,

SELECT * FROM master.sys.symmetric_keys
SELECT * FROM sys.symmetric_keys

[寫入]
OPEN SYMMETRIC KEY KeyName DECRYPTION BY PASSWORD = 'Password'
EncryptByKey(Key_GUID('symKey_Name'), 'Insert_Value')
CLOSE SYMMETRIC KEY KeyName

[讀取]
OPEN SYMMETRIC KEY KeyName DECRYPTION BY PASSWORD = 'Password'
DecryptByKey([ColumnName])
CLOSE SYMMETRIC KEY KeyName

2.非對稱式金鑰 Asymmetric Key

SELECT * FROM sys.ssymmetric_keys

[寫入]
EncryptByAsymKey( AsymKey_ID('asymKey_Name'),'Insert_Value')

[讀取]
DecryptByAsymKey( AsymKey_ID('asymKey_Name'), [ColumnName], N'asymKey_Password')

3.憑證 Certificate

[寫入]
EncryptByCert( Cert_ID('Cert_Name'), 'Insert_Value' )
[讀取]
DecryptByCert( Cert_ID('Cert_Name'),[ColumnName], N'Cert_Password')


-- ====== [Service Master Key 與 Database Master Key] ==============
SELECT * FROM master.sys.symmetric_keys
GO

-- Backup Service Master Key
USE Master
GO
BACKUP SERVICE MASTER KEY
TO FILE='C:\Backup\SQL2K5.smk'
ENCRYPTION BY PASSWORD='3dH85Hhk003GHk2597gheij4';
GO

-- Restore Service Master Key
RESTORE SERVICE MASTER KEY
FROM FILE='C:\Backup\SQL2K5.smk'
DECRYPTION BY PASSWORD='3dH85Hhk003GHk2597gheij4';
GO

-- Regenerate Service Master Key
--ALTER SERVICE MASTER KEY REGENERATE
--GO

-- Create Demo Database
CREATE DATABASE [EncryptionDB]

-- Database Master Key
USE [EncryptionDB]
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD='23987hxJ#KL95234nl0zBe';
GO

SELECT [name] N'資料庫'
, [is_master_key_encrypted_by_server] N'已使用主要金鑰加密的資料庫'
FROM master.sys.databases;
GO

SELECT * FROM sys.symmetric_keys;
GO

-- ALTER MASTER KEY
--ALTER MASTER KEY
-- REGENERATE WITH ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';
--GO

-- Backup Database MASTER KEY
BACKUP MASTER KEY
TO FILE = 'C:\Backup\EncryptionDB_MasterKey.dmk'
ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';
GO

-- Restore Database Master Key
RESTORE MASTER KEY
FROM FILE = 'C:\Backup\EncryptionDB_MasterKey.dmk'
DECRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';
GO


-- ====== [Symmetric Key] ==============
USE [EncryptionDB]
GO
-- Create test table
CREATE TABLE dbo.tSYMMETRIC
(CustomerID int NOT NULL PRIMARY KEY,
PasswordHintQuestion nvarchar(300) NOT NULL,
PasswordHintAnswer varbinary(8000) NOT NULL)
GO

-- CREATE SYMMETRIC KEY
CREATE SYMMETRIC KEY sym01
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = '25ho878!45fR6HG%B3f';

--
SELECT * FROM sys.symmetric_keys

-- Encryption Data With EncryptByKey()
OPEN SYMMETRIC KEY sym01
DECRYPTION BY PASSWORD = '25ho878!45fR6HG%B3f';

INSERT dbo.tSYMMETRIC (CustomerID
,PasswordHintQuestion
,PasswordHintAnswer)
VALUES (1
, N'大人小朋友的可玩的'
, EncryptByKey(Key_GUID('sym01 '), 'Wii')
)

CLOSE SYMMETRIC KEY sym01;

-- Query
SELECT CustomerID
,PasswordHintQuestion
,CAST(DecryptByKey(PasswordHintAnswer) as varchar(4000)) N'密碼提示的答案'
FROM dbo.[tSYMMETRIC]

-- Query With DecryptByKey()
OPEN SYMMETRIC KEY sym01
DECRYPTION BY PASSWORD = '25ho878!45fR6HG%B3f';

SELECT CustomerID
,PasswordHintQuestion
,CAST(DecryptByKey(PasswordHintAnswer) as varchar(4000)) N'密碼提示的答案'
FROM dbo.[tSYMMETRIC]

CLOSE SYMMETRIC KEY sym01

-- DELETE SYMMETRIC KEY
DROP SYMMETRIC KEY sym01;


-- ====== [Asymmetric Key] ==============

-- Create TEST Table dbo.tASY
CREATE TABLE dbo.[tASY]
(
[EmployeeID] int NOT NULL PRIMARY KEY,
[mSalary] varbinary(8000) NOT NULL
)
GO

-- CREATE ASYMMETRIC KEY
CREATE ASYMMETRIC KEY asym01
WITH ALGORITHM = RSA_512
ENCRYPTION BY PASSWORD = 'bmsA$dk7i82bv55foajsd9764';
GO

--
SELECT [name] N'金鑰的名稱'
,[pvt_key_encryption_type_desc] N'私密金鑰加密方式'
,[algorithm_desc] N'金鑰使用的演算法'
FROM sys.[asymmetric_keys]

--EX1. EncryptByAsmKey()
INSERT dbo.[tASY]
VALUES (1
,EncryptByAsymKey(AsymKey_ID('asym01'),'999999')
)

--
SELECT * FROM dbo.[tASY]

--
SELECT [EmployeeID]
,CAST([mSalary] AS varchar(2000)) N'薪資'
FROM dbo.[tASY]

--EX2. DecryptByAsymKey()
SELECT [EmployeeID]
,CAST(
DecryptByAsymKey(
AsymKey_ID('asym01'), mSalary, N'bmsA$dk7i82bv55foajsd9764'
) as varchar(100)
) N'薪資'
FROM dbo.[tASY]

-- ====== [Certificate] ==============

-- 建立資料表 dbo.tCert
CREATE TABLE dbo.[tCert]
(
[uids] int NOT NULL PRIMARY KEY,
[cardid] varbinary(8000) NOT NULL
)
GO

-- CREATE CERTIFICATE
CREATE CERTIFICATE cs01
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Wii issue',
START_DATE = '2009/08/08',
EXPIRY_DATE= '2009/12/31'
GO

--
SELECT [name] N'憑證名稱'
,[pvt_key_encryption_type_desc] N'私密金鑰加密方式'
,issuer_name N'憑證發行者'
,[start_date] N'憑證生效時間'
,[expiry_date] N'憑證逾期時間'
FROM sys.[certificates]

--EX1.
-- EncryptByCert()
INSERT dbo.tCert
VALUES (1
,EncryptByCert(Cert_ID('cs01'), '55553635401028')
)

--
SELECT * FROM dbo.tCert

--
SELECT [uids]
,CAST([cardid] AS varchar(2000)) N'卡號'
FROM dbo.tCert

-- DecryptByCert()
SELECT [uids]
,CAST(
DecryptByCert(
Cert_ID('cs01'),[cardid], N'pGFD4bb925DGvbd2439587y'
) as varchar(1000)
) N'卡號'
FROM dbo.[tCert]


-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--EX2. Backup Certificate
BACKUP CERTIFICATE cs01
TO FILE = 'C:\Backup\SQLServer\cs01.cer'
WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y' ,
FILE = 'C:\Backup\SQLServer\cs01PK.pvk' ,
ENCRYPTION BY PASSWORD = 'zxxn34khUbhk$w4ecJH5gh' );
GO

-- 刪除 CERTIFICATE
DROP CERTIFICATE cs01

--
SELECT [uids]
,CAST(
DecryptByCert(
Cert_ID('cs01'),[cardid], N'pGFD4bb925DGvbd2439587y'
) as varchar(1000)
) N'卡號'
FROM dbo.[tCert]


--
SELECT [name] N'憑證名稱'
,[pvt_key_encryption_type_desc] N'私密金鑰加密方式'
,[issuer_name] N'憑證發行者'
,[start_date] N'憑證生效時間'
,[expiry_date] N'憑證逾期時間'
FROM sys.[certificates];

-- Restore Certificate
CREATE CERTIFICATE cs01
FROM FILE = 'C:\Backup\SQLServer\cs01.cer'
WITH PRIVATE KEY (FILE = 'C:\Backup\SQLServer\cs01PK.pvk',
DECRYPTION BY PASSWORD = 'zxxn34khUbhk$w4ecJH5gh',
ENCRYPTION BY PASSWORD ='pGFD4bb925DGvbd2439587y');
GO

--
SELECT [uids]
,CAST(
DecryptByCert(
Cert_ID('cs01'),[cardid], N'pGFD4bb925DGvbd2439587y'
) as varchar(1000)
) N'卡號'
FROM dbo.[tCert];


-- ====== [combine Certificate and Symmetric Key] ==============

CREATE TABLE dbo.EmployeeReview
(EmployeeID int NOT NULL,
ReviewDate datetime DEFAULT GETDATE() NOT NULL,
Comments varbinary(8000) NOT NULL)
GO

--02 建立 CERTIFICATE
CREATE CERTIFICATE cs02
WITH SUBJECT = 'PS3 issue',
START_DATE = '2009/08/08',
EXPIRY_DATE= '2009/12/31'
GO

--03 建立 SYMMETRIC KEY
CREATE SYMMETRIC KEY sym03
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE cs02 -- 利用 cs02 憑證來保護 sym03 對稱金鑰
GO

--
SELECT * FROM sys.symmetric_keys
WHERE [NAME] <> '##MS_DatabaseMasterKey##'
--
SELECT [name] N'憑證名稱'
,[pvt_key_encryption_type_desc] N'私密金鑰加密方式'
,[issuer_name] N'憑證發行者'
,[start_date] N'憑證生效時間'
,[expiry_date] N'憑證逾期時間'
FROM sys.certificates

--04 EncryptByKey()
OPEN SYMMETRIC KEY sym03
DECRYPTION BY CERTIFICATE cs02

INSERT INTO dbo.EmployeeReview
VALUES (1
,DEFAULT
,EncryptByKey(Key_GUID('sym03'),N'加薪到 $99,000')
);

CLOSE ALL SYMMETRIC KEYS

--
SELECT * FROM dbo.EmployeeReview

--
SELECT
EmployeeID
,ReviewDate
,CONVERT(varchar,Comments) AS Comments
FROM dbo.EmployeeReview;

--05 DecryptByKey()
OPEN SYMMETRIC KEY sym03
DECRYPTION BY CERTIFICATE cs02

SELECT
EmployeeID
,ReviewDate
,CONVERT(nvarchar,DecryptByKey(Comments)) AS Comments
FROM dbo.EmployeeReview

CLOSE ALL SYMMETRIC KEYS

--
USE master
GO
DROP DATABASE EncryptionDB
GO