展示如何用证书签名一个存储过程

 

-- 目的
-- 展示如何用证书签名一个存储过程,
--并授予证书相应的权限

 

create
database demo;
go

-- 创建一个存储过程，该过程会创建一个主体（包含登录和用户）
-- 这需要服务器级别的ALTER ANY LOGIN 权限
-- 和数据库级别的 ALTER ANY USER 权限
create
procedure sp_CreatePrincipal
      @name
varchar(256),
      @password
varchar(128)
as
   declare
@sqlcmd
varchar(2000);

 

   begin
tran;

 

   -- create login

set
@sqlcmd
=
'create login '
+
quotename(@name) +
' with password = '
+
quotename(@password, '''');
   exec (@sqlcmd);
   if
@@error
<>
0

   begin

      rollback
tran;
      print
'Cannot create login'
      return;
   end

 

   -- create user

set
@sqlcmd
=
'create user '
+
quotename(@name);
   exec (@sqlcmd);
   if
@@error
<>
0
   begin
      rollback
tran;
      print
'Cannot create user'
      return;
   end

 

   commit
tran;
go

 

 -- 调用这个存储过程
-- 创建主体
sp_CreatePrincipal 'alice', 'Apufe@))%';

--我们需要让alice可以调用这个存储过程，创建新的主体，
-- 但并不直接授予她权限（创建主体的权限，译者注）
grant
execute
on sp_CreatePrincipal to alice;

 

-- 目前 alice还不能创建主体
execute as login = 'alice';

sp_CreatePrincipal 'bob', 'Apufe@))%';
revert;
 

select * from sys.master_key_passwords

 

-- 使用证书对存储过程进行签名
-- 首先我们要创建一个数据库主密钥（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
='C:\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
=
'C:\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';
exec sp_CreatePrincipal 'bob', 'Apufe@))%';
revert;

select * from sysusers

 

-- 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