Исключения в logon-триггерах

Logon-триггеры, которые появились в MS SQL 2005 предоставляют удобный механизм для логирования подключений. Подключения могут протоколироваться и средствами профайлера и другими методами слежения за процессами. Однако Logon-триггеры превратили процесс подключения в транзакцию, которую при желании можно откатить. Например, можно выполнять проверки для разрешения или запрета на подключение к серверу.

Приведем такой пример. Создадим базу данных для хранения настроек и логов:

use master
go

if db_id ( N'LogStore' ) is null
begin
        create database LogStore

        on primary

        (

                name = LogData,

                filename = N'C:\Users\В\Desktop\LogData.mdf',

                size = 100 Mb,

                filegrowth = 100 Mb,

                maxsize = unlimited

        )

        log on

        (
                name = LogDataLog,
                filename = N'C:\Users\В\Desktop\LogDataLog.ldf',
                size = 100 Mb,
                filegrowth = 100 Mb,

                maxsize = unlimited

        )

end

go

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

use LogStore
go

if object_id ( N'dbo.ExHosts', N'U' ) is null

begin

        create table dbo.ExHosts

        (

                iHostId int identity ( 1, 1 ) not null,

                vcHost nvarchar ( 200 ) not null,

                constraint PK_ExHosts_iHostId primary key clustered ( iHostId asc ) on [primary],

                constraint AK_ExHosts_vcHost unique nonclustered ( vcHost asc ) on [primary]

        )

end

go

Логон-триггер может проверять компьютер соединения и откатывать транзакцию, если окажется, что в таблице есть строка с таким компьютером. Перед созданием триггера надо определиться с тем под какой учетной записью он будет работать (ведь не стоит давать всем доступ на базу и на таблицу). Создадим такую учетную запись и предоставим ей права:

use master

go

if suser_id ( N'LogLogin' ) is null

begin

create login LogLogin

        with

                   password = N'qw3@',

                   check_policy = off,

                   default_database = LogStore,

                   default_language = russian

end

go

use LogStore

go

if database_principal_id ( N'LogUser' ) is null

begin

        create user LogUser

                from login LogLogin

                with default_schema = dbo
end

go

if database_principal_id ( N'LogRole' ) is null

begin

        create role LogRole

                  authorization dbo

end

go

grant select on object::dbo.ExHosts to LogRole

go

alter role LogRole add member LogUser

go

Теперь можно создать триггер на вход:

use master

go

if not exists
(
        select *

        from sys.server_triggers

        where name = N'CheckHost'
)
begin
        exec sp_executesql N'create trigger CheckHost on all server for logon as return'

end

go

alter trigger CheckHost on all server

with exec as 'LogLogin'

for logon

as

begin

        set nocount, xact_abort on

        if exists

        (

                select *

                from LogStore.dbo.ExHosts

                where vcHost = isnull ( host_name (), N'' )

        )

        begin

                ; throw 60000, N'Попытка входа с запрещенного хоста.', 1

                if @@trancount > 0

                begin

                        rollback tran

                end

        end
end
go

При попытке зайти на сервер с запрещенного компьютера произойдет ошибка. Например, если попытаться сделать такое подключение в Management Studio, то появится сообщение об ошибке:

Текста сообщения об ошибке, написанного в коде триггера, здесь не видно. Но такие ошибки попадают в журнал сервера, где их можно обнаружить:

Казалось бы все неплохо. Но при работе с logon-триггерами есть один подводный камень, состоящий в следующем. Код в таких триггерах выполняется в контексте неявной транзакции, опция xact_abort всегда включена. Любое исключение, возникающее из-за процессов, которые не предусмотрел разработчик, приводит к откату этой транзакции и, как следствие, невозможности подключения к серверу. В худшем случае, когда ошибка происходит при любой попытке входа, создается ситуация, когда никто не может подключиться к серверу. Никакая обработка ошибок, например, с помощью try/catch, не поможет, поскольку, как было отмечено выше, транзакция уже откатилась, и ничто не поможет. В этой ситуации можно входить на сервер используя некоторые специальные режимы. Например, режим приоритетного соединения администратора позволяет зайти на сервер, поскольку на такое соединение триггеры не действуют. Для этого требуется ввести в командной строке: sqlcmd -S <имя сервера> -E -A. После чего требуется ввести текст команды: disable trigger CheckHost on all server. Вместо командной строки можно использовать и Management Studio, если в окне подключения перед именем сервера написать: admin:. Возможно, также потребуется произвести эти действия с серверного компьютера (если отключена опция remote admin connection).

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

1. Триггер работает от имени логина LogLogin. Пока триггер работает в его контексте удалить логин не получится. Здесь все в порядке.

2. Делается попытка просмотра объекта в базе данных LogStore. Значит, чтобы все было хорошо, требуется, чтобы существовала база данных с таким именем, она должна быть доступной, и текущая учетная запись должна иметь права на подключение к ней.

3. Производится попытка запроса к таблице dbo.ExHosts. Для ее успеха требуется наличие таблицы с таким именем и права на запросы к ней для текущей учетной записи.

4. В запросе к таблице делается условие на столбец. Поэтому требуется удостовериться, что у заданной таблицы существует столбец с таким именем и что его тип данных такой какой мы ожидаем: nvarchar.

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

alter trigger CheckHost on all server
with exec as 'LogLogin'
for logon
as
begin
        set nocount, xact_abort on

        -- проверим возможность выполнения запросов к системныму каталогу с перечнем баз данных
    if isnull ( has_perms_by_name ( 'sys.databases', 'object', 'select', null, null ), 0 ) = 0
    begin
        return
    end

    -- существование базы данных с заданным именем в состоянии online
    if not exists
    (
        select *
        from sys.databases
        where name = 'LogStore' and state_desc = 'ONLINE'
    )
    begin
        return
    end

    -- возможность подключения к базе данных
    if isnull ( has_perms_by_name ( 'LogStore', 'database', 'connect', null, null ), 0 ) = 0
    begin
        return
    end

    -- наличие таблицы
    if object_id ( N'LogStore.dbo.ExHosts', N'U' ) is null
    begin
        return
    end

    -- наличие доступов на select к таблице (эта функция корректно учтет наличие несколько доступов и запретов)
    if isnull ( has_perms_by_name ( 'LogStore.dbo.ExHosts', 'object', 'select', null, null ), 0 ) = 0
    begin
        return
    end

    -- проверка существования столбца с определенным именем
    declare @vcSql nvarchar ( max ), @iColId int
    set @vcSql = N'use LogStore set @iColId = columnproperty ( object_id ( N''dbo.ExHosts'', N''U'' ), N''vcHost'', N''ColumnId'' )'
    exec sp_executesql @vcSql, N'@iColId int out', @iColId = @iColId out
    if @iColId is null
    begin
        return
    end

    -- возможность выполнения запросов в системному представлению с перечнем столбцов
    declare @bPerm int
    set @vcSql = N'use LogStore set @bPerm = has_perms_by_name ( ''LogStore.sys.columns'', ''object'', ''select'', null, null )'
    exec sp_executesql @vcSql, N'@bPerm int out', @bPerm = @bPerm out
    if @bPerm = 0
    begin
        return
    end


    -- наличие у столбца нужного типа данных
    if not exists
    (
        select *
        from LogStore.sys.columns
        where
            name = 'vcHost' and
            object_id = object_id ( N'LogStore.dbo.ExHosts', N'U' ) and
            type_name ( system_type_id ) = 'nvarchar' and
            type_name ( user_type_id ) = 'nvarchar' and
            max_length = 400
    )
    begin
        return
    end
    if exists
    (
        select *
        from LogStore.dbo.ExHosts
        where vcHost = isnull ( host_name (), N'' )
    )
    begin
        ; throw 60000, N'Попытка входа с запрещенного компьютера.', 1
        if @@trancount > 0
        begin
            rollback tran
        end
    end
end
go

В коде делается проверка возможности всех действий, которые выполняются при проверке законности входа. Причем, если для проверки возможности действия, происходит обращение к системному объекту, то предварительно делается проверка возможности обращения к системному объекту. Теперь, если, скажем, изменить имя столбца (exec LogStore..sp_rename 'dbo.ExHosts.vcHost', 'vcName', 'column') или сделать запрет для учетной записи на запросы к таблице (exec ( N'use LogStore deny select on object::dbo.ExHosts to LogUser' )) или вообще удалить учетную запись, триггер спокойно продолжит свою работу. В этой ситуации он, конечно, не сможет выполнять свои прямые обязанности по проверке законности подключений. Но в большинстве случаев это лучше чем совсем никого не пускать.