【stswordman】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資料庫中的證照一定要和使用者資料庫中的相同。

下面是演示：

<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt-- 目的
-- 展示如何用證照籤名一個儲存過程, 
--並授予證照相應的許可權

 

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-413573/，如需轉載，請註明出處，否則將追究法律責任。