[實作快速摘要]
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
