SQL Server 2005加密与解密

1. 选建一个表来做实验

USE [TestDB]

GO

/****** Object:  Table [dbo].[Customer]    Script Date: 05/05/2008 13:14:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Customer](

 [CustomerID] [int] NULL,

 [Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

 [City] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,

 [CreditCardType] [varbinary](max) NULL,

 [CreditCardNumber] [varbinary](max) NULL,

 [Notes] [varbinary](max) NULL,

 [Descr] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF 2. 建master key和证书use TestDB

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abcd1234';

GO

 

CREATE CERTIFICATE User2Certificate

 AUTHORIZATION ODMSDB_User_Name

    WITH SUBJECT = 'ODMS DB testing',

 START_DATE = '10/31/2007',

 EXPIRY_DATE = '10/31/2017';

GO 

 

 --若要将已创建的证书授予其它用户，用下边的语句

ALTER AUTHORIZATION ON Certificate::User2Certificate TO user1;

GO

3. 建function
--加密的:

create FUNCTION [dbo].[fn_Encrypt]

(

 @plaintxt Varchar(max),

 @token varchar(100)

)

RETURNS varbinary(max)--因为EncryptByCert方法返回的是varbinary

AS

BEGIN

 declare @returnVal varbinary(max)

 set @returnVal = EncryptByCert(Cert_ID(@token), @plaintxt)

 return @returnVal

END

 

--解密的:

create  FUNCTION [dbo].[fn_Decrypt]

(

 @plaintxt varbinary(max),--保存值的字段一般定度成varbinary类型

 @token varchar(100)

)

RETURNS Varchar(max)

AS

BEGIN

 declare @returnVal Varchar(max)

 set @returnVal = CONVERT(VARCHAR, DecryptByCert(Cert_ID(@token), @plaintxt) )

 return @returnVal

END

4. 使用加密方法插入数据
INSERT INTO Customer

VALUES (12, 'John Doe', 'Fairbanks',

--EncryptByAsymKey(AsymKey_ID(@CertName), @Column2Value)

--EncryptByCert(Cert_ID('User2Certificate'), 'HuangYao'),

dbo.fn_Encrypt('HuangYao2258', 'User2Certificate'),

EncryptByCert(Cert_ID('User2Certificate'), '1234-5678-9009-8765'),

EncryptByCert(Cert_ID('User2Certificate'), '黄黃楊杨shopper. Spends $5 at most.'),

dbo.fn_EnDecrypt('HuangYao2258-----huangyao testing,25rsheahehdahtrehj42jdahet', 'User2Certificate', 0))

 

select * FROM Customer WHERE CustomerID = 12

5. 使用

SELECT CustomerID, Name, City,

--CONVERT(nvarchar(100), DecryptByAsymKey(AsymKey_Id(@KeyName), Column2Name, @DecryptToken)) AS ColumnAlias

--CONVERT(VARCHAR, DecryptByCert(Cert_ID('User2Certificate'), CreditCardType)) AS CardType,

dbo.fn_Decrypt(CreditCardType, 'User2Certificate') AS CardType,

CONVERT(VARCHAR, DecryptByCert(Cert_ID('User2Certificate'), CreditCardNumber) ) AS CardNumber,

CONVERT(VARCHAR, DecryptByCert(Cert_ID('User2Certificate'), Notes) ) AS Notes,

dbo.fn_EnDecrypt(Descr, 'User2Certificate', 1) as Descr

FROM Customer WHERE CustomerID = 12