Переключение контекста выполнения внутри триггера входа

У меня есть база данных с именем MyDB в экземпляре Microsoft SQL Server 2008 Express с использованием аутентификации в смешанном режиме. Приложение, использующее базу данных MyDB, в настоящее время подключается с использованием Windows Authentication, используя учетные данные текущего пользователя Windows. Этот логин является членом "общедоступной" роли сервера, и для него назначен пользователь в базе данных MyDB. Этот пользователь базы данных является членом ролей базы данных db_datareader и db_datawriter.

Я хотел бы, чтобы при подключении приложения у него были права на чтение и запись в MyDB. Но когда другое приложение подключается, используя тот же логин, ему разрешается только чтение.

Я думал, что я создам триггер входа в систему, который будет проверять часть имени приложения в строке подключения и на основе этого решить, следует ли переключать контекст выполнения. (Кстати, я знаю, что никоим образом не безопасно полагаться на имя приложения в строке подключения и что его очень легко обойти. Цель здесь не в том, чтобы защитить базу данных, а чтобы помочь пользователям избежать изменений данные при подключении с помощью другого приложения, например Microsoft Excel)

Я создал новый логин с именем "myapp_reader", сопоставленный с пользователем в базе данных MyDB, которая является членом db_datareader.

Затем я попытался создать триггер входа в систему со следующим TSQL:

CREATE TRIGGER CheckUser
ON ALL SERVER
AFTER LOGON AS
BEGIN
IF APP_NAME() <> 'My Application Name'
    BEGIN
        EXECUTE AS LOGIN = 'myapp_reader' WITH NO REVERT
    END
END

Но, к сожалению, это не работает. Когда я пытаюсь подключиться, я получаю следующую ошибку:

Ошибка входа в систему для входа в систему "MyComputer\MyWindowsUsername" из-за выполнения триггера.
Изменен контекст базы данных на "мастер".
Изменены языковые настройки на us_english. (Microsoft SQL Server, ошибка: 17892)

И когда я смотрю в журнал ошибок, он говорит:

Ошибка: 15590, уровень серьезности: 16, состояние: 1.
Можно использовать параметры "Нет возврата" или "Cookie" только с оператором "Выполнить как" на уровне adhoc.
Ошибка: 17892, серьезность: 20, состояние: 1.
Ошибка входа в систему для входа в систему "MyComputer\MyWindowsUsername" из-за выполнения триггера. [КЛИЕНТ: xxx.xxx.xxx.xxx]

Означает ли эта ошибка, что я не могу постоянно изменить контекст выполнения в триггере входа?

Источник

4 ответа

Решение

Я не думаю, что возможно изменить контекст выполнения для всей сессии. Вы можете создать триггер DML для INSERT, UPDATE и DELETE для каждой таблицы / представления в вашей базе данных, которая выполняет откат для определенного app_name(). Вы можете написать процедуру для автоматизации создания всех этих триггеров.

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

Источник

Предполагая, что у вас есть контроль над приложением, и вы можете изменить его, тогда роли приложения будут делать именно то, что вы хотите. См. Sp_setapprole в Books Online, чтобы начать.

Источник

Вы действительно должны сначала решить, как организовать учетные данные и управлять ими.

Если вы используете sp_setapprole, который обойдет проверку подлинности Windows и разрешит доступ через это приложение для любого пользователя. Если это то, что вы действительно хотите сделать, то, если приложение является сервером, создайте учетную запись пользователя для этого приложения и запустите ее под учетными данными этого пользователя.

Если это клиентское приложение, то создайте веб-сервис, который будет считывать и отправлять только определенные данные, необходимые приложению, и запускать этот веб-сервис под новой учетной записью. Затем в IIS7 вы можете разместить ACL на самом веб-сервисе, чтобы он все еще был защищен.

Также, если этому приложению не доверяют быть чистым и знать, что оно делает, попросите, чтобы оно было проверено кодом, прежде чем оно сможет коснуться SQL-сервера. Если это ваше собственное приложение, то начните доверять себе:-)

Источник

Вы не можете делать это так, как хотите.

  • Пользовательское соединение имеет одинаковые учетные данные, за исключением некоторых конкретных областей, использующих EXECUTE AS.
  • Вы поняли, что не можете полагаться на APP_NAME() или HOST_NAME(), чтобы определять, когда кто-то подключается по-другому, что означает, что триггер отката для каждой таблицы не может полагаться на него
  • Ваше приложение использует прямой доступ к записи таблицы

Некоторые варианты, которые я могу придумать...

  • Ваше приложение использует хранимые процедуры, пользователи имеют доступ только для чтения к таблицам
  • Используйте SET CONTEXT_INFO в своем приложении, чтобы установить "секретный" ключ для триггеров отката
  • Измените ваше приложение, чтобы использовать учетную запись службы / является службой Windows / и т. Д. И использует прокси-имя пользователя (как это будет делать веб-страница)
  • ... или некоторые перестановки
Источник
Другие вопросы по тегам