存储过程和权限-执行是否足够? [英] Stored Procedure and Permissions - Is EXECUTE enough?

问题描述

我有一个SQL Server 2008数据库，所有对基础表的访问都是通过存储过程完成的.一些存储过程只是从表中选择记录，而其他存储过程则是UPDATE，INSERT和DELETE.

I have a SQL Server 2008 database where all access to the underlying tables is done through stored procedures. Some stored procedures simply SELECT records from the tables while others UPDATE, INSERT, and DELETE.

如果存储过程更新表，执行存储过程的用户是否还需要对受影响的表具有UPDATE权限，或者它们对存储过程具有EXECUTE权限是否足够?

If a stored procedure UPDATES a table does the user executing the stored procedure also need UPDATE permissions to the affected tables or is the fact that they have EXECUTE permissions to the stored procedure enough?

基本上，我想知道是否向用户授予对存储过程的EXECUTE权限是否足够，还是我需要向他们授予对表的SELECT，UPDATE，DELETE和INSERT权限才能使存储过程起作用.谢谢.

Basically I am wondering if giving the user EXECUTE permissions to the stored procedures is enough or do I need to give them SELECT, UPDATE, DELETE, and INSERT permissions to the tables in order for the stored procedures to work. Thank you.

在我的大多数存储过程中，确实看起来EXECUTE就足够了.但是，我确实发现，在使用"Execute sp_Executesql"的存储过程中，EXECUTE是不够的.所涉及的表需要具有在"sp_Executesql"中执行的操作的权限.

In most of my stored procedures it does indeed appear that EXECUTE is enough. However, I did find that in stored procedures where "Execute sp_Executesql" was used that EXECUTE was not enough. The tables involved needed to have permissions for the actions being performed within "sp_Executesql".

推荐答案

对存储过程的执行权限就足够了.

Execute permissions on the stored procedure is sufficient.

CREATE TABLE dbo.Temp(n int)

GO
DENY INSERT ON dbo.Temp TO <your role>
GO
CREATE PROCEDURE dbo.SPTemp(@Int int)
AS

INSERT dbo.Temp
SELECT  @Int 

GO

GRANT EXEC ON dbo.SPTemp TO <your role>

GO

然后，(非db_owner)用户将具有以下权限:

Then the (non-db_owner) user will have the following rights:

EXEC dbo.SPTemp 10
GO

INSERT dbo.Temp --INSERT permission was denied on the object 'Temp'
SELECT  10

但是，如果dbo.SPTemp中有动态SQL试图插入dbo.Temp，则该SQL将失败.在这种情况下，将需要授予对该表的直接许可.

However, if there is dynamic SQL inside dbo.SPTemp that attempts to insert into dbo.Temp then that will fail. In this case direct permission on the table will need to be granted.

这篇关于存储过程和权限-执行是否足够?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！