MS SQL Server. Запись данных из триггера в другую базу данных

    Представим ситуацию, в которой на сервере MS SQL Server есть две базы данных. С первой базой через свою информационную систему работают пользователи. Во вторую базу для обмена с другой системой выгружаются "итоговые данные, подписанные руководством". Самый простой вариант – это создать триггер, который после подписи будет копировать данные из таблицы первой базы данных во вторую.

    Пользователем sa создадим две тестовые базы данных:

• db1 – база с таблицей исходных данных (tdb1) и таблицей аудита (tdb1_audit).
• db2 – база с таблицей, в которую триггер копирует данные (tdb2).

CREATE DATABASE db1
GO
USE db1
GO
CREATE TABLE tdb1
(
   id          INT IDENTITY NOT NULL PRIMARY KEY,
   name        VARCHAR(100) NOT NULL,
   is_active   BIT          NOT NULL
)
GO
CREATE TABLE tdb1_audit
(
   id          INT          NOT NULL PRIMARY KEY,
   login       SYSNAME      NOT NULL
)
GO
CREATE DATABASE db2
GO
USE db2
GO
CREATE TABLE tdb2
(
   id          INT          NOT NULL PRIMARY KEY,
   name        VARCHAR(100) NOT NULL,
   is_active   BIT          NOT NULL
)
GO

В первой базе создадим триггер, который после добавления новой строки в таблицу tdb1, скопирует ее содержимое в таблицу tdb2 и запишет в таблицу tdb1_audit имя пользователя, добавившего эту строку.

USE db1
GO
CREATE TRIGGER tdb1_ai ON tdb1 AFTER INSERT
AS
  SET NOCOUNT ON
BEGIN
  INSERT INTO db2.dbo.tdb2 (id, name, is_active)
    SELECT id, name, is_active FROM INSERTED
  INSERT INTO tdb1_audit (id, login) VALUES (@@identity, SUSER_NAME())
END
GO

Добавим новую строку в таблицу tdb1

INSERT INTO tdb1 (name, is_active) VALUES ('row #1 from sa', 1)

и, чтобы убедиться, что все сработало правильно посмотрим содержимое таблиц tdb1 и tdb2:

    Все действия мы выполняли от имени администратора СУБД. Но пользователи информационной системы работают каждый под своим логином. Проверим, что запишет наш триггер под простым пользователем. Для этого создадим в базе данных db1 пользователя user1 и дадим ему права на работу с таблицей tdb1:

CREATE LOGIN user1 WITH PASSWORD = 'user1', 
  DEFAULT_DATABASE = db1, 
  CHECK_POLICY = OFF, 
  DEFAULT_LANGUAGE = Russian
GO
USE db1
GO
CREATE USER user1
GO
GRANT SELECT, INSERT, UPDATE, DELETE ON tdb1 TO user1
GO

Теперь подключимся к базе данных db1 пользователем user1 и добавим в таблицу tdb1 новую строку:

Ошибка 916 "Серверу-участнику X не удалось обратиться к базе данных Y в текущем контексте безопасности" ("The server principal X is not able to access the database Y under the current security context") означает, что триггер не смог сделать запись в таблицу tdb2, т.к. у пользователя user1 нет доступа к базе данных db2. Что делать?

    MS SQL Server позволяет в блоках T-SQL кода указывать, какую учетную запись ядро СУБД должно использовать при проверке прав на объекты на которые ссылается исполняемый код. Для этого служит специальный модификатор "EXECUTE AS", который в качестве параметра может принимать значения: CALLER, SELF, OWNER и имя/логин пользователя. Изменить контекст безопасности выполнения можно даже для целых модулей (функций, процедур, триггеров и очередей). Сделаем это для нашего триггера – изменим контекст безопасности выполнения добавив в объявление триггера параметр "WITH EXECUTE AS OWNER":

USE db1
GO
ALTER TRIGGER tdb1_ai ON tdb1 WITH EXECUTE AS OWNER AFTER INSERT
AS
  SET NOCOUNT ON
BEGIN
  INSERT INTO db2.dbo.tdb2 (id, name, is_active)
    SELECT id, name, is_active FROM INSERTED
  INSERT INTO tdb1_audit (id, login) VALUES (@@identity, SUSER_NAME())
END
GO

Это заставит его выполняться не от имени подключившегося пользователя (user1), а от имени своего владельца (в данном случае, от имени администратора).

    Так как триггер выходит за пределы своей базы данных, то ее необходимо сделать для ядра СУБД "доверенной" установив параметр TRUSTWORTHY в состояние "ON":

ALTER DATABASE db1 SET TRUSTWORTHY ON

По умолчанию этот параметр для новых баз данных имеет значение "OFF" (для того, чтобы устранить угрозы от баз данных, содержащих вредоносные сборки с параметром разрешения EXTERNAL_ACCESS или UNSAFE, или вредоносные модули, выполняемые от имени привилегированных пользователей). Теперь снова подключимся к базе данных db1 пользователем user1, добавим строку в таблицу tdb1 и проверим результат запросами к таблицам tdb1, tdb1_audit и tdb2.

Строка добавилась. Но в tdb1_audit записано, что эту строку добавил пользователь sa, а не user1. Проблема в том, что функция SUSER_NAME() возвращает имя пользователя, от имени которого выполняется код. Есть два варианта ее решения. Первый – заменить функцию SUSER_NAME() на функцию ORIGINAL_LOGIN(), которая возвращает имя пользователя, использовавшееся для подключения к серверу MS SQL Server. Второй – перед записью в tdb1_audit переключить выполнение оставшегося кода на контекст пользователя, вызвавшего модуль. Второй вариант с использованием команды "EXECUTE AS CALLER" мне кажется более правильным.

USE db1
GO
ALTER TRIGGER tdb1_ai ON tdb1 WITH EXECUTE AS OWNER AFTER INSERT
AS
  SET NOCOUNT ON
BEGIN
  INSERT INTO db2.dbo.tdb2 (id, name, is_active)
    SELECT id, name, is_active FROM INSERTED

  EXECUTE AS CALLER

  INSERT INTO tdb1_audit (id, login) VALUES (@@identity, SUSER_NAME())
END
GO

Подключимся к базе данных db1 пользователем user1 и добавим в таблицу tdb1 вторую строку. Теперь автор записи сохранился корректно:

    EXECUTE AS у MS SQL Server повышает гибкость и безопасность управления разрешениями между пользовательскими модулями и объектами, на которые они ссылаются. Всем пользователям можно не выдавать разрешения на объекты, а предоставлять только разрешения на выполнение работающего с ними модуля. При этом модуль не ограничен рамками базы данных, в которой он выполняется.