证书加密并控制证书权限测试步骤

 

-- 使用证书对存储过程进行签名
-- 首先我们要创建一个数据库主密钥（database master key）
create master key encryption by password = 'xxxxxxxx';

 

Create Certificate CT_DBA
Encryption by password =N'xxxxxxxx'
with
subject = N'自主加密',
Start_date = N'2010-01-01',
Expiry_date = N'2999-01-01'

-- 对证书进行备份，随后在master数据库中将要使用该备份
backup certificate CT_DBA to
file
='C:\CT_DBA.cer';

-- 在master数据库中创建该证书
use master;
create certificate CT_DBA from
file
=
'C:\CT_DBA.cer';

-- 验证一下，master数据库中的证书和demo数据库中的证书是一样的。
select c.name from sys.certificates c, master.sys.certificates mc where c.thumbprint = mc.thumbprint;

 

Create table Test
(
 ID int identity(1,1) Primary key,
 Name nvarchar(100)
)

Create Proc Upc_InsTest
(
 @name nvarchar(20)
)
as
BEGIN
 insert into Test(Name)
  select ENCRYPTBYCERT(Cert_ID(N'CT_DBA'),@name)
END

Grant all on Upc_GetTest to Test

Create Proc Upc_GetTest
(
 @name nvarchar(20)
)
AS
BEGIN
 SELECT ID,name
 from Test where Convert(nvarchar(max),DECRYPTBYCERT(Cert_ID('CT_DBA'),name,N'xxxxxxxx')) = @name
END

gRANT EXECUTE ON Upc_InsTest TO TEST

select Convert(nvarchar(2000),DECRYPTBYCERT(Cert_ID('CT_DBA'),ENCRYPTBYCERT(Cert_ID(N'CT_DBA'),name),'xxxxxxxx'))

From dbo.test

-- 签名存储过程sp_CreatePrincipal
add signature to Upc_GetTest by certificate CT_DBA WITH PASSWORD = 'xxxxxxxx';

 

-- 现在签名完成了，可以将证书的私钥移除了
alter certificate certSignCreatePrincipal remove private key;

-- 创建一个用户并将用户映射到证书
create user u_CT_DBA from certificate CT_DBA;

 

--通过授权映射映射的方式将ALTER ANY USER权限赋给证书  （因为用户和证书是映射的，所以权限也就赋给了证书，SQLSERVER本身没有直接将权限赋给证书的方法）

GRANT CONTROL ON CERTIFICATE::[CT_DBA] TO u_CT_DBA
GO