Разрешить вход в систему для запуска хранимой процедуры без возможности выбора из таблицы

Вы хотите использовать предложение execute as . Неясно, каким должно быть значение, но для установки разрешений часто требуется, чтобы владелец хранимой процедуры имел необходимые разрешения.

CREATE PROCEDURE Get_LogData (
       @ProductID int
)
WITH EXECUTE AS OWNER AS
BEGIN
    . . .
END;

Это расширение ответа, связанного с Ezlo ( SQL Server EXECUTE AS проблема ), о котором я собираюсь рассказать Включение кросс-доступа к базе данных в SQL Server . [ 1114]

Во-первых, давайте настроим быстрый тест, чтобы воспроизвести проблему, которая у вас есть прямо сейчас:

--Create a couple of sample databases
CREATE DATABASE SampleDB1;
CREATE DATABASE SampleDB2;
GO

USE SampleDB2;
GO
--Create a sample table
CREATE TABLE dbo.SampleTable (ID int, Somestring varchar(25));
GO
USE SampleDB1;
GO
--Create a sample SP and User/Login;
CREATE PROC dbo.SomeProc AS

    SELECT ID,
           SomeString
    FROM SampleDB2.dbo.SampleTable;
GO
CREATE LOGIN SampleCredential WITH PASSWORD = 'abc123', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF, DEFAULT_LANGUAGE = BRITISH;
CREATE USER SampleCredential FOR LOGIN SampleCredential;

GRANT EXEC ON dbo.SomeProc TO SampleCredential;
GO

--Test
EXECUTE AS LOGIN = 'SampleCredential';
GO
--This will fail
EXEC dbo.SomeProc;
GO
REVERT;
GO

Как вы можете видеть, если вы запустите этот скрипт, вы получите сообщение об ошибке: [ 1116]

Сообщение 916, Уровень 14, Состояние 1, Процедура SomeProc, Строка 4 [Стартовая Строка 28] Участник сервера "SampleCredential" не может получить доступ к базе данных "SampleDB2" в текущем контексте безопасности.

Итак, что такое кросс-доступ к базе данных? Процитируем из документации:

Создание цепочки владения несколькими базами данных происходит, когда процедура в одной базе данных зависит от объектов в другой базе данных. Цепочка владения несколькими базами данных работает так же, как цепочка владения в пределах одной базы данных, за исключением того, что непрерывная цепочка владения требует, чтобы все владельцы объектов были сопоставлены одной и той же учетной записи входа. Если исходный объект в исходной базе данных и целевые объекты в целевых базах данных принадлежат одной и той же учетной записи для входа в систему, SQL Server не проверяет разрешения для целевых объектов.

Заметим, однако, что есть основных недостатков безопасности, которые могут быть введены с этим методом. Таким образом, если это беспокоит вашу среду, это не является решением для вас. Опять же, из документации:

Цепочка владения по базам данных отключена по умолчанию. Microsoft рекомендует отключить цепочку владения несколькими базами данных, поскольку она подвергает вас следующим угрозам безопасности:

• Владельцы базы данных и члены ролей базы данных db_ddladmin или db_owners могут создавать объекты, которыми владеют другими пользователями. Эти объекты могут потенциально предназначаться для объектов в других базах данных. Это означает, что если вы включаете цепочку владения несколькими базами данных, вы должны полностью доверять этим пользователям данные во всех базах данных.

• Пользователи с разрешением CREATE DATABASE могут создавать новые базы данных и присоединять существующие базы данных. Если включена цепочка владения несколькими базами данных, эти пользователи могут получать доступ к объектам в других базах данных, к которым у них могут отсутствовать привилегии, из вновь созданных или присоединенных баз данных, которые они создают.

Хорошо, теперь, когда предостережение не в порядке, что с этим делать. Продолжая из приведенного выше сценария, нам нужно включить перекрестную цепочку владения БД на сервере, если это еще не сделано. Вы можете сделать это, выполнив следующее:

EXEC sp_configure 'show advanced', 1;
RECONFIGURE;  
GO
EXEC sp_configure 'cross db ownership chaining', 1;  
RECONFIGURE;  
GO
EXEC sp_configure 'show advanced', 0;
RECONFIGURE;  
GO

Теперь, когда это включено, вы можете включить DB_CHAINING в базах данных 2 :

ALTER DATABASE SampleDB1 SET DB_CHAINING ON;
ALTER DATABASE SampleDB2 SET DB_CHAINING ON;
GO

You ' Затем мне нужно будет создать пользователя для вашего логина в другой базе данных, но не нужно будет давать ему какие-либо разрешения, так что это будет «хорошо» (это не позволит им SELECT из таблицы за пределами SP):

USE SampleDB2;
GO
CREATE USER SampleCredential FOR LOGIN SampleCredential;
GO

Наконец, вы можете снова протестировать:

USE SampleDB1;
--Test
EXECUTE AS LOGIN = 'SampleCredential';
GO
--It works (0 rows returned)
EXEC dbo.SomeProc;
GO
REVERT;
GO

Если вы хотите дважды проверить, что пользователь не может выбрать из базы данных, вы можете сделать это :

USE SampelDB1;
GO
--This will fail
SELECT *
FROM SampleDB2.dbo.SampleTable;
GO

И, наконец, очистка (для хорошей меры):

--Cleanup
USE master;
DROP DATABASE SampleDB2;
DROP DATABASE SampleDB1;
DROP LOGIN SampleCredential;
EXEC sp_configure 'show advanced', 1;
RECONFIGURE;  
GO
EXEC sp_configure 'cross db ownership chaining', 0;  
RECONFIGURE;  
GO
EXEC sp_configure 'show advanced', 0;
RECONFIGURE;  
GO