execute as login = ’alice’;
sp_createPrincipal ’bob’, ’Apufe@))%’;
revert;
sp_createPrincipal ’bob’, ’Apufe@))%’;
revert;
-- 使用证书对存储过程进行签名
-- 首先我们要创建一个数据库主密钥(database master key)
create master key encryption by password = ’Apufe@))%’; create certificate certSigncreatePrincipal with subject = ’for signing procedure sp_createPrincipal’;
-- 签名存储过程sp_createPrincipal
add signature to sp_createPrincipal by certificate certSigncreatePrincipal;
-- 现在签名完成了,可以将证书的私钥移除了
alter certificate certSigncreatePrincipal remove private key;
-- 对证书进行备份,随后在master数据库中将要使用该备份
backup certificate certSigncreatePrincipal to file = ’certSigncreatePrincipal.cer’;
-- 创建一个用户并将用户映射到证书
create user u_certSigncreatePrincipal from certificate certSigncreatePrincipal;
--通过授权映射映射的方式将alter ANY USER权限赋给证书 (因为用户和证书是映射的,所以权限也就赋给了证书,SQLSERVER本身没有直接将权限赋给证书的方法。译者注)
grant alter any user to u_certSigncreatePrincipal;
-- 在master数据库中创建该证书
use master; create certificate certSigncreatePrincipal from file = ’certSigncreatePrincipal.cer’;
-- 创建登录并映射到证书
create login l_certSigncreatePrincipal from certificate certSigncreatePrincipal;
-- 通过授权映射登录的方式将alter ANY LOGIN权限赋给证书
grant alter any login to l_certSigncreatePrincipal;
-- 完成!
use demo;
-- 验证一下,master数据库中的证书和demo数据库中的证书是一样的。
select c.name from sys.certificates c, master.sys.certificates mc where c.thumbprint = mc.thumbprint;
-- 现在alice可以创建主体了
execute as login = ’alice’;
sp_createPrincipal ’bob’, ’Apufe@))%’;
revert;
-- cleanup
drop user u_certSigncreatePrincipal;
drop login l_certSigncreatePrincipal;
drop procedure sp_createPrincipal;
drop certificate certSigncreatePrincipal;
drop user alice;
drop login alice;
drop user bob;
drop login bob;
use master;
drop certificate certSigncreatePrincipal;
drop database demo;
-- EOD
sp_createPrincipal ’bob’, ’Apufe@))%’;
revert;
-- cleanup
drop user u_certSigncreatePrincipal;
drop login l_certSigncreatePrincipal;
drop procedure sp_createPrincipal;
drop certificate certSigncreatePrincipal;
drop user alice;
drop login alice;
drop user bob;
drop login bob;
use master;
drop certificate certSigncreatePrincipal;
drop database demo;
-- EOD