Sql2005 非對稱加密的實作

--1) 建立非對稱密鑰

CREATE ASYMMETRIC KEY AsyTestKey WITH ALGORITHM = RSA_1024

ENCRYPTION BY PASSWORD = 'walp#$sdine894!sdasdf>seddv'

GO

SELECT * FROM sys.asymmetric_keys

--2) 建立示例表

USE TestDb

IF EXIST EmpSalary DROP TABLE EmpSalary;

CREATE TABLE SysEmployee(

    WorkNo int,

    WorkName nvarchar(50),

    Passwd varbinary(500)

)

GO

--3) 向表中插入資料，并對Salary列的資料進行加密

INSERT INTO SysEmployee VALUES (1, '王賢', EncryptByAsymKey(AsymKey_ID('AsyTestKey'), '123#sined'))

INSERT INTO SysEmployee VALUES (2, '李甯', EncryptByAsymKey(AsymKey_ID('AsyTestKey'), 'disf>!we'))

INSERT INTO SysEmployee VALUES (3, '陳飛', EncryptByAsymKey(AsymKey_ID('AsyTestKey'), '877erECe'))

GO

--4) 檢視表中存放的資料

SELECT * FROM SysEmployee           

--5) 解密被加密了的資料列

SELECT WorkNo, WorkName,CONVERT(varchar(20),

    DecryptByAsymKey( AsymKey_Id('AsyTestKey'),

    Passwd, N'walp#$sdine894!sdasdf>seddv' )) as DecryptPasswd,Passwd as EncryptPasswd

FROM SysEmployee