SQL - SQL Server 2008 加密與解密實作心得 (二) | 張小呆的碎碎唸 - 點部落

2009-09-10

SQL - SQL Server 2008 加密與解密實作心得 (二)

44262
0
MS SQL
2011-12-02

摘要:SQL - SQL Server 2008 加密與解密實作心得 (二)

接續上一篇，繼續來介紹其他三種加密與解密的方式。

3.非對稱金鑰加解密：這種加解密方式必須要建立「非對稱金鑰」

Code：建立非對稱金鑰

Code：進行資料解密

CREATE ASYMMETRIC KEY 非對稱金鑰
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = '張小呆';
GO

結果：

Code：進行資料加密

Select *,
ENCRYPTBYASYMKEY(ASYMKEY_ID('非對稱金鑰'),
CONVERT(nvarchar(max), 身分證)) as '加密'
From dbo.使用者資料

結果：

Code：進行資料解密

Create Table #table(
f_使用者名稱 nvarchar(10),
f_身分證 char(10),
f_加密 varbinary(max)
);

Insert into #table
Select *,
ENCRYPTBYASYMKEY(ASYMKEY_ID('非對稱金鑰'),
CONVERT(nvarchar(max), 身分證)) as '加密'
From dbo.使用者資料

--進行資料解密
Select *,
CONVERT(nvarchar(10), DECRYPTBYASYMKEY(ASYMKEY_ID('非對稱金鑰'), f_加密, N'張小呆')) as '解密'
From #table

Drop Table #table

結果：

心得：非對稱金鑰安全性比對稱金鑰安全，因為非對稱金鑰安全性高，且加解密的方式也比對稱金鑰要來的複雜，所以在大量加密資料時，會佔用較多的系統資源。所以，這就要看各公司的內政嚕...

參考：
CREATE ASYMMETRIC KEY (Transact-SQL)
ALTER ASYMMETRIC KEY (Transact-SQL)
DROP ASYMMETRIC KEY (Transact-SQL)
選擇加密演算法
加密階層
了解可延伸金鑰管理 (EKM)

4.憑證加解密：這種加解密方式必須要建立「憑證」

Code：建立憑證

CREATE CERTIFICATE Shipping04
   ENCRYPTION BY PASSWORD = '張小呆'
   WITH SUBJECT = '憑證加解密',
   START_DATE = '2009/08/22',
   EXPIRY_DATE = '2009/12/31';
GO

結果：

Code：進行資料加密

Select *,
ENCRYPTBYCERT(Cert_ID('憑證加解密'),
CONVERT(nvarchar(max), 身分證)) as '加密'
From dbo.使用者資料

結果：

Create Table #table(
f_使用者名稱 nvarchar(10),
f_身分證 char(10),
f_加密 varbinary(max)
);

Insert into #table
Select *,
ENCRYPTBYCERT(Cert_ID('憑證加解密'),
CONVERT(nvarchar(max), 身分證)) as '加密'
From dbo.使用者資料

--進行資料解密
Select *,
CONVERT(nvarchar(10), DECRYPTBYCERT(Cert_ID('憑證加解密'), f_加密, N'張小呆')) as '解密'
From #table

Drop Table #table

結果：

心得：在加解密的過程當中，與非對稱金鑰的方式類似，也是一個高安全性的加解密方法，當然效能上也與非對稱金鑰相差不多...

參考：
CREATE CERTIFICATE (Transact-SQL)
DROP CERTIFICATE (Transact-SQL)
BACKUP CERTIFICATE (Transact-SQL)
SQL Server 憑證和非對稱金鑰
sys.certificates (Transact-SQL)
教學課程：使用憑證簽署預存程序

5.密碼片語加解密

Code：進行資料加密

Select *,
ENCRYPTBYPASSPHRASE('張小呆', Convert(nvarchar(max), 身分證,
CONVERT(int, SUBSTRING('A891234567', 2, len('A891234567'))))) as '加密'
From dbo.使用者資料

結果：

Code：進行資料解密

Create Table #table(
f_使用者名稱 nvarchar(10),
f_身分證 char(10),
f_加密 varbinary(max)
);

Insert into #table
Select *,
ENCRYPTBYPASSPHRASE('張小呆', Convert(nvarchar(max), 身分證,
CONVERT(int, SUBSTRING('A891234567', 2, len('A891234567'))))) as '加密'
From dbo.使用者資料

--進行資料解密
Select *,
CONVERT(nvarchar(10), DECRYPTBYPASSPHRASE('張小呆', f_加密, CONVERT(int, SUBSTRING('A891234567', 2, len('A891234567'))))) as '解密'
From #table

Drop Table #table

結果：

心得：密碼片語加密方式比 Convert 方式要來的好，也是一般使用的加解密方式。

這五種加解密方式，是較常用的，該使用哪一個，就要看公司的資安政策了，若是使用對稱加解密、非對稱加解密或憑證加解密，都要特別重視備份資料庫這項工作，以免發生重大的資安問題。

回首頁