SQL Server 2005存储过程签名-技术开发专区

SQL Server 2005存储过程签名

作者：博客园编辑：胡铭娅 2009-02-23 15:17 来源：IT168�

【IT168 技术文档】

SQL SERVER 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;

com/Images/OutliningIndicators/None.gif" width=11 align=top>-- 创建一个存储过程，该过程会创建一个主体(包含登录和用户)

-- 这需要服务器级别的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

关注我们