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; GO3. 建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 END4. 使用加密方法插入数据5. 使用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 = 12SELECT 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