授予存储过程权限 [英] Give permissions to a stored procedure

问题描述

我有一个可以合并到表中的存储过程.我们称之为someSchema.UpsertSomeStuff(注意它不在dbo 模式中).让我们调用表 dbo.SomeStuff(注意它在 dbo 模式中).

I have a stored procedure that does a merge into a table. Let's call it someSchema.UpsertSomeStuff (note that it is not in the dbo schema). Let's call the table dbo.SomeStuff (note that it is in the dbo schema).

当我使用数据读取器/数据写入器用户调用存储过程时，它工作正常.

When I call the stored procedure with a data reader/data writer user, it works fine.

但是，如果我将调用此存储过程的权限授予非常弱的用户(没有数据写入者或读取者权限)，则它无法告诉我我无法选择、插入或更新到 dbo.SomeStuff.

But if I give permissions to call this stored procedure to a very weak user (no data writer or reader rights), then it fails telling me that I can't select, insert or update to dbo.SomeStuff.

我不想让这个弱用户访问那个表.我只希望存储过程可以访问.

I don't want to give this weak user access to that table. I just want the stored procedure to have access.

我尝试使用 WITH EXECUTE AS OWNER 并且它似乎有效，但是我的同事在尝试修改存储过程时开始收到此错误:

I tried using WITH EXECUTE AS OWNER and it seemed to work, but then my co-worker started getting this error when he tried to modify the stored procedures:

无法以用户MyUserNameHere"的身份执行，因为它不存在或您没有权限.

Cannot execute as the user 'MyUserNameHere', because it does not exist or you do not have permission.

我对所有者和架构之间的区别有点困惑.但我认为应该有一种方法可以完成此操作，而不必将其绑定到我的个人用户帐户.

I am a bit confused about the difference between the owner and the schema. But I think there should be a way to get this done with out having to tie it to my personal user account.

推荐答案

您需要的是签署程序.

让我从 M.Ali 在他的评论中提供的链接(SQL Server 用户对存储过程和底层表的权限):

Let me borrow the setup from the link M.Ali provided in his comment (SQL Server User Permissions on Stored Procedure and Underlying Tables):

use Test
go
if exists (select * from sys.syslogins where name = 'UserA')
    drop login UserA 
create login UserA with password = 'Welcome'
if exists (select * from sys.syslogins where name = 'UserB')
    drop login UserB 
create login UserB with password = 'Welcome'
if exists (select * from sys.syslogins where name = 'UserC')
    drop login UserC 
create login UserC with password = 'Welcome'


if exists (select * from sys.tables where name = 'Customers' and schema_name(schema_id) = 'SchemaA')
    drop table SchemaA.Customers
if exists (select * from sys.schemas where name = 'SchemaA')
    drop schema SchemaA
if exists (select * from sys.sysusers where name = 'UserA')
    drop user UserA

if exists (select * from sys.tables where name = 'Orders' and schema_name(schema_id) = 'SchemaB')
    drop table SchemaB.Orders
if exists (select * from sys.procedures where name = 'GetCustomerOrderInfo' and schema_name(schema_id) = 'SchemaB')
    drop procedure SchemaB.GetCustomerOrderInfo 
if exists (select * from sys.schemas where name = 'SchemaB')
    drop schema SchemaB
if exists (select * from sys.sysusers where name = 'UserB')
    drop user UserB

if exists (select * from sys.sysusers where name = 'UserC')
    drop user UserC

create user UserA for login UserA
alter role db_owner add member UserA
go
create schema SchemaA authorization UserA
go
create user UserB for login UserB
alter role db_owner add member UserB
go
create schema SchemaB authorization UserB
go
create user UserC for login UserC

create table SchemaA.Customers (id int identity)

create table SchemaB.Orders (id int identity, CustomerId int)
go
create procedure SchemaB.GetCustomerOrderInfo 
as
select  *
from    SchemaB.Orders o
join    SchemaA.Customers c
on      c.id = o.CustomerId
go

这是设置，感谢 Andomar.

This was the setup, thx to Andomar.

我们可以授予UserC对该过程的执行权限:

We can give the UserC execute permission on the procedure:

grant execute on SchemaB.GetCustomerOrderInfo to UserC
execute as login = 'UserC'
exec SchemaB.GetCustomerOrderInfo 
-- The SELECT permission was denied on the object 'Customers', database 'Test', schema 'SchemaA'.
revert

这还不够好.我们可以做的是在数据库中创建一个证书，在这个证书上创建一个数据库用户，赋予该用户适当的权限(本示例中的 db_owner 角色)，然后使用证书签署程序:

This wasn't good enough. What we can do is create a certificate in the database, a database user on this certificate, give that user appropriate permissions (db_owner role in this sample), and then sign the procedure with the certificate:

create certificate cert_raiser
    encryption by password = 'pGFD4bb925DGvbd2439587y'
    with subject = 'raiser', 
    expiry_date = '01/01/2114';
go

create user cert_user from certificate cert_raiser
go

alter role db_owner add member cert_user
go

add signature to SchemaB.GetCustomerOrderInfo 
   by certificate cert_raiser
    with password = 'pGFD4bb925DGvbd2439587y';
go

现在应该可以正常工作了.

It should work OK now.

需要说明的是:证书上创建的用户不能作为普通用户使用，没有登录，不存在安全问题；当我们添加签名时，我们授予该用户的所有权限都将添加到执行该过程的上下文中；如果我们改变程序，我们必须再次签署.

Points to make: the user created on the certificate cannot be used as a normal user, there is no login with it and it's not a security problem; all the permissions we give that user will be added to context in which the procedure is executed when we add a signature; If we alter the procedure, we have to sign it again.

这篇关于授予存储过程权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！