-- 使用证书对存储过程进行签名
-- 首先我们要创建一个数据库主密钥(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