Сохраненная процедура и разрешения - достаточно ли ИСПОЛНИТЬ?

У меня есть база данных SQL Server 2008, где весь доступ к базовым таблицам выполняется через хранимые процедуры. Некоторые хранимые процедуры просто выбирают записи из таблиц, а другие - UPDATE, INSERT и DELETE.

Если хранимая процедура ОБНОВЛЯЕТ таблицу, выполняет ли пользователь, выполняющая хранимую процедуру, разрешения UPDATE для затронутых таблиц или факт, что у них есть права EXECUTE для хранимой процедуры?

В принципе, мне интересно, достаточно ли предоставить пользователям EXECUTE разрешения для хранимых процедур или мне нужно предоставить им разрешения SELECT, UPDATE, DELETE и INSERT для таблиц, чтобы хранимые процедуры работали. Спасибо.

[EDIT] В большинстве моих хранимых процедур действительно появляется, что EXECUTE достаточно. Однако я обнаружил, что в хранимых процедурах, где "Execute sp_Executesql" использовался, что EXECUTE было недостаточно. Для задействованных таблиц необходимы разрешения для действий, выполняемых в "sp_Executesql".

Ответ 1

Выполнить разрешения для хранимой процедуры достаточно.

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) будет иметь следующие права:

EXEC dbo.SPTemp 10
GO

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

Однако, если у вас есть динамический SQL внутри dbo.SPTemp, который пытается вставить в dbo.Temp, тогда это не удастся. В этом случае необходимо предоставить прямое разрешение на таблицу.

Ответ 2

Разрешения на таблицы не проверяются (включая DENY), если таблицы и proc имеют одного и того же владельца. Они могут быть в разных схемах, если схемы имеют одного и того же владельца.

См. Цепочка прав собственности на MSDN

Измените комментарий из удаленного ответа.

Контекст всегда является текущим входом, если только EXECUTE AS не используется: только ссылки на объекты. Разрешения DML не проверяются. Попробуйте OBJECT_ID (ссылочная таблица) в сохраненной процедуре, когда права не присваиваются ссылочной таблице. Он дает NULL. Если он будет выполнен владельцем хранимой процедуры, тогда он даст значение, потому что owener имеет права на ссылочную таблицу

Ответ 3

Выполнение разрешения на хранимую процедуру, которая делает вставку, обновление или удаление, является достаточной. Вам не нужно предоставлять эти разрешения на уровне таблицы. На самом деле я бы отговорил этот подход. Использование хранимой процедуры дает вам больше контроля над тем, как происходит изменение. Например, вы можете выполнить некоторую проверку до разрешения обновления. Использование хранимой процедуры также может помочь предотвратить крупные аварии - например, удаление всех строк в таблице, потому что кто-то забыл предложение WHERE!

Ответ 4

Возможно, вы можете использовать

"с исполнением как владелец"

при создании сохраненного procedure.such, как показано ниже:

create procedure XXX
with execute as owner
as
begin
...
end
go

тогда вам нужно предоставить пользователю разрешение на выполнение для хранимой процедуры XXX.

Ответ 5

БЛАГОДАРИМ ВАС! У меня была аналогичная проблема. Это привело меня к ответу.

Я пытался переустановить таблицу в хранимой процедуре, которая вызывала другие хранимые процедуры, которые были вложены в операторы IF.

Моя ошибка была

Основной сервер "domain\my_id" не может получить доступ к базе данных "2nd_DB" в контексте текущей безопасности.

Я предоставил права на хранимую процедуру вызова для выполнения truncate (EXECUTE AS SELF), что вызвало проблему, поскольку SELF не имел прав на вторую БД. Наше решение состояло в том, чтобы перенести truncate на другой SP, включая EXECUTE AS SELF. Теперь мы вызываем truncate SP, выполняем нашу обработку данных, делаем логическое определение и вызываем соответствующий третий SP.