SQL 2005加密資料方法

--Author: liangCK 小樑<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

--示例一,使用證照加密資料.

--建立測試資料表

CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));

GO

--建立證照一,該證照使用資料庫主金鑰來加密

CREATE CERTIFICATE Cert_Demo1

WITH

SUBJECT=N'cert1 encryption by database master key',

START_DATE='<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />2008-01-01',

EXPIRY_DATE='2008-12-31'

GO

--建立證照二,該證照使用密碼來加密

CREATE CERTIFICATE Cert_Demo2

ENCRYPTION BY PASSWORD='liangCK.123'

WITH

SUBJECT=N'cert1 encrption by password',

START_DATE='2008-01-01',

EXPIRY_DATE='2008-12-31'

GO

--此時,兩個證照已經建立完,現在可以用這兩個證照來對資料加密

--在對錶tb做INSERT時,使用ENCRYPTBYCERT加密

INSERT tb(data)

SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo1'),N'這是證照1加密的內容-liangCK'); --使用證照1加密

INSERT tb(data)

SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo2'),N'這是證照2加密的內容-liangCK'); --使用證照2加密

--ok.現在已經對資料加密保證了.現在我們SELECT看看

SELECT * FROM tb ;

--現在對內容進行解密顯示.

--解密時,使用DECRYPTBYCERT

SELECT 證照1解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo1'),data)),

--使用證照2解密時,要指定DECRYPTBYCERT的第三個引數,

--因為在建立時,指定了ENCRYPTION BY PASSWORD.

--所以這裡要通過這個密碼來解密.否則解密失敗

證照2解密

=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo2'),data,N'liangCK.123'))

FROM tb ;

--我們可以看到,因為第2條記錄是證照2加密的.所以使用證照1將無法解密.所以返回NULL

/*

證照1解密證照2解密

-------------------------------------------------- --------------------------------------------------

這是證照1加密的內容-liangCK NULL

NULL 這是證照2加密的內容-liangCK

(2 行受影響)

*/

GO

--刪除測試證照與資料表

DROP CERTIFICATE Cert_Demo1;

DROP CERTIFICATE Cert_Demo2;

DROP TABLE tb;

GO

--示例二,使用對稱金鑰加密資料,

--對稱金鑰又使用證照來加密.

--建立測試資料表tb

CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));

GO

--建立證照,該證照用於加密對稱金鑰.

CREATE CERTIFICATE Cert_Demo

ENCRYPTION BY PASSWORD=N'liangCK.123'

WITH

SUBJECT=N'cert encryption by password',

START_DATE='2008-01-01',

EXPIRY_DATE='2008-12-31'

GO

--建立對稱金鑰

CREATE SYMMETRIC KEY Sym_Demo

WITH

ALGORITHM=DES --使用DES加密演算法

ENCRYPTION BY CERTIFICATE Cert_Demo --使用Cert_Demo證照加密

GO

--要使用Sym_Demo對稱金鑰.必需使用OPEN SYMMETRIC KEY來開啟它

OPEN SYMMETRIC KEY Sym_Demo

DECRYPTION BY CERTIFICATE Cert_Demo

WITH PASSWORD=N'liangCK.123'

--插入加密資料

INSERT tb(data)

SELECT ENCRYPTBYKEY(KEY_GUID(N'Sym_Demo'),N'這是加密的資料,能顯示出來嗎?')

--關閉金鑰

CLOSE SYMMETRIC KEY Sym_Demo

--插入完加密資料,現在使用SELECT來查詢一下資料

SELECT * FROM tb

GO

--現在來解密此資料

--同樣,還是要先開啟對稱金鑰

OPEN SYMMETRIC KEY Sym_Demo

DECRYPTION BY CERTIFICATE Cert_Demo

WITH PASSWORD=N'liangCK.123'

SELECT CONVERT(NVARCHAR(50),DECRYPTBYKEY(data)) --這裡可見,資料已經解密出來了.

FROM tb

CLOSE SYMMETRIC KEY Sym_Demo

GO

--刪除測試

DROP SYMMETRIC KEY Sym_Demo

DROP CERTIFICATE Cert_Demo

DROP TABLE tb

--示例三,還有一種方法加密資料更簡單

--就是使用EncryptByPassPhrase

--建立測試資料表tb

CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));

GO

INSERT tb(data)

SELECT EncryptByPassPhrase(N'這是密碼,用來加密的',N'這是要加密的內容');

--解密

SELECT CONVERT(NVARCHAR(50),DECRYPTBYPASSPHRASE(N'這是密碼,用來加密的',data))

FROM tb

GO

DROP TABLE tb

SQL 2005加密資料方法

相關文章