存储过程和权限 - EXECUTE 足够了吗? [英] Stored Procedure and Permissions - Is EXECUTE enough?

问题描述

我有一个 SQL Server 2008 数据库，其中对基础表的所有访问都是通过存储过程完成的.一些存储过程只是从表中 SELECT 记录，而其他存储过程则是 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.

如果存储过程更新表，执行存储过程的用户是否也需要受影响表的更新权限，或者他们对存储过程具有执行权限的事实就足够了?

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 中，则会失败.在这种情况下，需要授予对该表的直接权限.

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.

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