SQL Server 2005: 存储过程签名

 

 

SQLSERVER 2005提供的对存储过程签名(signature)功能是我最喜欢的。

 

如果我们要编写一个存储过程,执行该存储过程里的代码需要权限P,并且我们想要用户Alice可以执行这个存储过程，但是我们不想将权限P直接赋予给用户Alice， 我们可以用证书(certificate)对这个存储过程进行签名来完成这一需求：

 

a)      如果P是一个数据库级别的权限，那我们可以在相应的数据库中创建一个证书，使用证书创建一个用户(user)，然后将权限p授权给这个用户

b)      如果P是一个服务器级别的权限，那我们能要在master数据库中创建一个证书，使用证书创建一个登录(login)，然后将权限P授权给这个登录

 

  签名之后，存储过程就会在执行期间获得权限P，而我们仅仅授予了Alice执行这个存储过程的权限。

 

     如果我们既需要服务器级别的权限，又需要数据库级别的权限，那么我们既要创建用户，又要创建登录。下面列出步骤：

 

1)      在数据库中创建证书

2)      创建一个用户(user)并映射到这个证书

3)      将数据库级别的权限授予这个用户

4)      备份这个证书

5)      在master数据库中还原这个证书

6)      创建一个登录(login)，并将登录映射到证书

7)      将服务器级别的权限授予给这个登录

 

  我们也可以先在master数据库中创建证书，然后再将其还原到用户alice工作的数据库。也就是证书的创建顺序并不重要，重要的是master数据库中的证书一定要和用户数据库中的相同。

 

下面是演示：

 

 

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

 

create   database  demo;

use  demo;

 

--  创建一个存储过程，该过程会创建一个主体（包含登录和用户）
--  这需要服务器级别的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;

 

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

 

原文： SQL Server 2005: procedure signing demo