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
