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

posted on 2008-07-29 10:20  stswordman  阅读(2618)  评论(11编辑  收藏  举报