2005中对存储过程进行签名,存储过程签名

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

我经常被问及这样的问题,能否让一个用户有加密数据的能力却不能解密数据呢? 答案是:可以。但在我展示这个方法之前,我们先稍微讨论一下对称密钥(symmetric key)。 用户能否加密或解密数据取决于用户能否够打开这个密钥。 如果用户可以打开密钥,那么他既能加密也能解密。 要想限制用户去使用密钥,需要删除用户打开密钥的能力。我们可以创建一个存储过程,将加密和打开密钥的过程封装进去,并让这个存储过程有权限执行这些操作(通过签名),然后赋予用户执行存储过程的权限。这样用户就可以通过存储过程访问密钥,但不能直接方法问密钥。注意,如果用户可以在并行操作中调用这个存储过程,可能会导致密钥被用户直接使用(用户没有打开密钥的权限,却能执行加密/解密操作。译者注)。虽然我不认为这会被利用,但仍存在这种可能性。

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

 

下面是示例代码

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

 

 

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

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

--
-- 这个demo演示让用户可以加密数据而不能解密数据
create database test
use test
-- 创建数据库主密钥
create master key encryption by password = 'Avcptnwgu@)!)'
-- 创建一个证书,该证书用于加密下面的对称密钥
create certificate cert_protect_skey_data with subject = 'Certificate for encrypting 
symmetric key'
-- 创建对称密钥,用于加密数据
create symmetric key skey_data with algorithm = triple_des encryption by certificate 
cert_protect_skey_data
--创建存储过程,使用对称密钥加密数据
create procedure sp_encrypt_with_skey_data
 @plaintext  varbinary(8000),
 @ciphertext  varbinary(8000) output
as
begin
 open symmetric key skey_data decryption by certificate cert_protect_skey_data
 set @ciphertext = encryptbykey(key_guid('skey_data'), @plaintext)
 close symmetric key skey_data
end
-- 验证该存储过程, 在一个batch内执行下代码
declare @plaintext varbinary(200)
set @plaintext = convert(varbinary(200), 'Plaintext')
declare @ciphertext varbinary(200)
exec sp_encrypt_with_skey_data @plaintext, @ciphertext output
print 'Ciphertext: '
print @ciphertext
print 'Plaintext: '
open symmetric key skey_data decryption by certificate cert_protect_skey_data
print convert(varchar(200), decryptbykey(@ciphertext))
close symmetric key skey_data
go
-- 创建一个不能访问密钥的主体
create login alice with password = 'TiA''ssptncgt#)))'
create user alice
-- 允许Alice执行该存储过程
-- 我们希望她能够使用密钥加密
-- 但不能解密
grant execute on sp_encrypt_with_skey_data to alice
-- 验证Alice可以加密
execute as login = 'Alice'
select suser_name()
-- Alice可以执行存储过程,但他不能访问密钥
-- 密钥并没有所有权链的机制
declare @plaintext varbinary(200)
set @plaintext = convert(varbinary(200), 'Plaintext')
declare @ciphertext varbinary(200)
exec sp_encrypt_with_skey_data @plaintext, @ciphertext output
print 'Ciphertext: '
print @ciphertext
-- Alice明显无法直接访问密钥
open symmetric key skey_data decryption by certificate cert_protect_skey_data
-- revert context
revert
-- 现在对存储过程进行签名,使Alice能够访问密钥
-- 创建用于签名的证书
create certificate cert_sign2use_skey_data with subject = 'Certificate for signing code that 
will use the symmetric key'
-- 创建一个映射到证书的用户
create user u_cert_sign2use_skey_data for certificate cert_sign2use_skey_data
-- 授权
grant view definition on symmetric key::skey_data to u_cert_sign2use_skey_data
grant control on certificate::cert_protect_skey_data to u_cert_sign2use_skey_data
-- 签名
add signature to sp_encrypt_with_skey_data by certificate cert_sign2use_skey_data
-- 现在Alice可以真正地使用这个存储过程了
execute as login = 'alice'
select suser_name()
-- 注意,现在Alice仍然不能直接访问密钥
open symmetric key skey_data decryption by certificate cert_protect_skey_data
-- 现在在一个batch执行一下代码,验证加密
-- 注意加密会成功,但解密会失败
declare @plaintext varbinary(200)
set @plaintext = convert(varbinary(200), 'Plaintext')
declare @ciphertext varbinary(200)
exec sp_encrypt_with_skey_data @plaintext, @ciphertext output
print 'Ciphertext: '
print @ciphertext
print 'Plaintext: '
print convert(varchar(200), decryptbykey(@ciphertext))
go
revert
-- cleanup
use master
drop database test
drop login alice
-- EOD

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

 

 

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

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

 

1) 在数据库中创建证书
2) 创建一个用户(user)并映射到这个证书
3) 将数据库级别的权限授予这个用户
4) 备份这个证书
5) 在master数据库中还原这个证书
6) 创建一个登录(login),并将登录映射到证书
7) 将服务器级别的权限授予给这个登录

 

原文地址:

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

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

 

下面是演示:
-- 目的
-- 展示如何用证书签名一个存储过程,
--并授予证书相应的权限 
create database demo;

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

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

 

-- 这需要服务器级别的ALTER ANY LOGIN 权限

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

-- 和数据库级别的 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

1)      在数据库中创建证书

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

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

commit tran;
go

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

-- 调用这个存储过程

4)      备份这个证书

-- 创建主体

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

sp_CreatePrincipal 'alice', 'Apufe@))%';  

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

--我们需要让alice可以调用这个存储过程,创建新的主体,

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

-- 但并不直接授予她权限(创建主体的权限,译者注)

 

grant execute on sp_CreatePrincipal to alice;

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

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

亚洲必赢网站登录 1-- 目的
亚洲必赢网站登录 2-- 展示如何用证书签名一个存储过程, 
亚洲必赢网站登录 3--并授予证书相应的权限
亚洲必赢网站登录 4
亚洲必赢网站登录 5 
亚洲必赢网站登录 6
亚洲必赢网站登录 7create database demo;
亚洲必赢网站登录 8
亚洲必赢网站登录 9use demo;
亚洲必赢网站登录 10
亚洲必赢网站登录 11 
亚洲必赢网站登录 12
亚洲必赢网站登录 13-- 创建一个存储过程,该过程会创建一个主体(包含登录和用户)
亚洲必赢网站登录 14-- 这需要服务器级别的ALTER ANY LOGIN 权限
亚洲必赢网站登录 15-- 和数据库级别的 ALTER ANY USER 权限
亚洲必赢网站登录 16create procedure sp_CreatePrincipal
亚洲必赢网站登录 17      @name varchar(256),
亚洲必赢网站登录 18      @password varchar(128)
亚洲必赢网站登录 19as
亚洲必赢网站登录 20   declare @sqlcmd varchar(2000);
亚洲必赢网站登录 21
亚洲必赢网站登录 22 
亚洲必赢网站登录 23
亚洲必赢网站登录 24   begin tran;
亚洲必赢网站登录 25
亚洲必赢网站登录 26 
亚洲必赢网站登录 27
亚洲必赢网站登录 28   -- create login
亚洲必赢网站登录 29   set @sqlcmd = 'create login '   quotename(@name)   ' with password = '   quotename(@password, '''');
亚洲必赢网站登录 30   exec (@sqlcmd);
亚洲必赢网站登录 31   if @@error <> 0
亚洲必赢网站登录 32
亚洲必赢网站登录 33   begin
亚洲必赢网站登录 34
亚洲必赢网站登录 35      rollback tran;
亚洲必赢网站登录 36      print 'Cannot create login'
亚洲必赢网站登录 37      return;
亚洲必赢网站登录 38   end
亚洲必赢网站登录 39
亚洲必赢网站登录 40 
亚洲必赢网站登录 41
亚洲必赢网站登录 42   -- create user
亚洲必赢网站登录 43   set @sqlcmd = 'create user '   quotename(@name);
亚洲必赢网站登录 44   exec (@sqlcmd);
亚洲必赢网站登录 45   if @@error <> 0
亚洲必赢网站登录 46   begin
亚洲必赢网站登录 47      rollback tran;
亚洲必赢网站登录 48      print 'Cannot create user'
亚洲必赢网站登录 49      return;
亚洲必赢网站登录 50   end
亚洲必赢网站登录 51
亚洲必赢网站登录 52 
亚洲必赢网站登录 53
亚洲必赢网站登录 54   commit tran;
亚洲必赢网站登录 55go
亚洲必赢网站登录 56
亚洲必赢网站登录 57 
亚洲必赢网站登录 58
亚洲必赢网站登录 59-- 调用这个存储过程
亚洲必赢网站登录 60-- 创建主体
亚洲必赢网站登录 61sp_CreatePrincipal 'alice', 'Apufe@))%';
亚洲必赢网站登录 62
亚洲必赢网站登录 63 
亚洲必赢网站登录 64
亚洲必赢网站登录 65--我们需要让alice可以调用这个存储过程,创建新的主体, 
亚洲必赢网站登录 66-- 但并不直接授予她权限(创建主体的权限,译者注)
亚洲必赢网站登录 67grant execute on sp_CreatePrincipal to alice;
亚洲必赢网站登录 68
亚洲必赢网站登录 69 
亚洲必赢网站登录 70
亚洲必赢网站登录 71-- 目前 alice还不能创建主体
亚洲必赢网站登录 72execute as login = 'alice';
亚洲必赢网站登录 73sp_CreatePrincipal 'bob', 'Apufe@))%';
亚洲必赢网站登录 74revert;
亚洲必赢网站登录 75
亚洲必赢网站登录 76 
亚洲必赢网站登录 77
亚洲必赢网站登录 78-- 使用证书对存储过程进行签名
亚洲必赢网站登录 79-- 首先我们要创建一个数据库主密钥(database master key)
亚洲必赢网站登录 80create master key encryption by password = 'Apufe@))%';
亚洲必赢网站登录 81create certificate certSignCreatePrincipal with subject = 'for signing procedure sp_CreatePrincipal';
亚洲必赢网站登录 82
亚洲必赢网站登录 83 
亚洲必赢网站登录 84
亚洲必赢网站登录 85-- 签名存储过程sp_CreatePrincipal
亚洲必赢网站登录 86add signature to sp_CreatePrincipal by certificate certSignCreatePrincipal;
亚洲必赢网站登录 87
亚洲必赢网站登录 88-- 现在签名完成了,可以将证书的私钥移除了
亚洲必赢网站登录 89alter certificate certSignCreatePrincipal remove private key;
亚洲必赢网站登录 90
亚洲必赢网站登录 91-- 对证书进行备份,随后在master数据库中将要使用该备份
亚洲必赢网站登录 92backup certificate certSignCreatePrincipal to file = 'certSignCreatePrincipal.cer';
亚洲必赢网站登录 93
亚洲必赢网站登录 94 
亚洲必赢网站登录 95
亚洲必赢网站登录 96-- 创建一个用户并将用户映射到证书
亚洲必赢网站登录 97create user u_certSignCreatePrincipal from certificate certSignCreatePrincipal;
亚洲必赢网站登录 98--通过授权映射映射的方式将ALTER ANY USER权限赋给证书  (因为用户和证书是映射的,所以权限也就赋给了证书,SQLSERVER本身没有直接将权限赋给证书的方法。译者注)
亚洲必赢网站登录 99grant alter any user to u_certSignCreatePrincipal;
亚洲必赢网站登录 100
亚洲必赢网站登录 101 
亚洲必赢网站登录 102
亚洲必赢网站登录 103-- 在master数据库中创建该证书
亚洲必赢网站登录 104use master;
亚洲必赢网站登录 105create certificate certSignCreatePrincipal from file = 'certSignCreatePrincipal.cer';
亚洲必赢网站登录 106
亚洲必赢网站登录 107-- 创建登录并映射到证书
亚洲必赢网站登录 108create login l_certSignCreatePrincipal from certificate certSignCreatePrincipal;
亚洲必赢网站登录 109-- 通过授权映射登录的方式将ALTER ANY LOGIN权限赋给证书
亚洲必赢网站登录 110grant alter any login to l_certSignCreatePrincipal;
亚洲必赢网站登录 111
亚洲必赢网站登录 112 
亚洲必赢网站登录 113
亚洲必赢网站登录 114-- 完成!
亚洲必赢网站登录 115use demo;
亚洲必赢网站登录 116
亚洲必赢网站登录 117 
亚洲必赢网站登录 118
亚洲必赢网站登录 119-- 验证一下,master数据库中的证书和demo数据库中的证书是一样的。
亚洲必赢网站登录 120select c.name from sys.certificates c, master.sys.certificates mc where c.thumbprint = mc.thumbprint;
亚洲必赢网站登录 121
亚洲必赢网站登录 122 
亚洲必赢网站登录 123
亚洲必赢网站登录 124-- 现在alice可以创建主体了
亚洲必赢网站登录 125execute as login = 'alice';
亚洲必赢网站登录 126sp_CreatePrincipal 'bob', 'Apufe@))%';
亚洲必赢网站登录 127revert;
亚洲必赢网站登录 128
亚洲必赢网站登录 129 
亚洲必赢网站登录 130
亚洲必赢网站登录 131-- cleanup
亚洲必赢网站登录 132drop user u_certSignCreatePrincipal;
亚洲必赢网站登录 133drop login l_certSignCreatePrincipal;
亚洲必赢网站登录 134drop procedure sp_CreatePrincipal;
亚洲必赢网站登录 135drop certificate certSignCreatePrincipal;
亚洲必赢网站登录 136drop user alice;
亚洲必赢网站登录 137drop login alice;
亚洲必赢网站登录 138drop user bob;
亚洲必赢网站登录 139drop login bob;
亚洲必赢网站登录 140
亚洲必赢网站登录 141 
亚洲必赢网站登录 142
亚洲必赢网站登录 143use master;
亚洲必赢网站登录 144
亚洲必赢网站登录 145drop certificate certSignCreatePrincipal;
亚洲必赢网站登录 146drop database demo;
亚洲必赢网站登录 147-- EOD
亚洲必赢网站登录 148
亚洲必赢网站登录 149

add signature to sp_CreatePrincipal by certificate certSignCreatePrincipal;
-- 现在签名完成了,可以将证书的私钥移除了

 

alter certificate certSignCreatePrincipal remove private key;
-- 对证书进行备份,随后在master数据库中将要使用该备份

原文: SQL Server 2005: procedure signing demo

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

本文由亚洲必赢娱乐游戏发布于亚洲必赢网站登录,转载请注明出处:2005中对存储过程进行签名,存储过程签名

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。