Создать аудит данных в SQL Server

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

Допустим, у меня есть следующая таблица:

[TableA]
------
ID
ColumnA
ColumnB
ColumnC

Для аудита я создал такую ​​таблицу, как:

[TableA.Audit]
------
ID
TableAID
UserID
Date (default value = getdate())
ColumnA
ColumnB
ColumnC

Затем я написал сценарий вроде:

DECLARE @currentColumnA int
       ,@currentColumnB int
       ,@currentColumnC int

SELECT TOP 1 @currentColumnA=ColumnA
            ,@currentColumnB=ColumnB
            ,@currentColumnC=ColumnC
FROM [TableA]
WHERE ID=@TableAID

UPDATE [TableA]
SET ColumnA=@ColumnA
    ,ColumnB=@ColumnB
    ,ColumnC=@ColumnC
WHERE ID=@TableAID

INSERT INTO [TableA.Audit] (TableAID, UserID, ColumnA, ColumnB, ColumnC)
VALUES (@TableAID, @UserID, NULLIF(@ColumnA, @currentColumnA), NULLIF(@ColumnB, @currentColumnB), NULLIF(@ColumnC, @currentColumnC))

Проблема в том, что если я добавлю поле ColumnD в TableA, мне придется отредактировать свою таблицу TableA.Audit, а также приведенный выше скрипт.

Поэтому есть ли лучший способ сделать это?


sql sql-server audit
person Curt    schedule 22.08.2011    source источник
comment
В зависимости от вашей ситуации этот ответ может быть чем-то помочь. (Это упрощенная версия того, о чем говорит @HLGEM.)   -  person Andriy M    schedule 23.08.2011

Ответы (3)


arrow_upward
3
arrow_downward

Вам лучше написать триггеры для таблицы для AFTER INSERT, AFTER DELETE и AFTER UPDATE. Таким образом, в любое время НИЧЕГО (приложение, Management Studio и т. д.), которое вставляет, обновляет или удаляет данные в таблице, будет регистрироваться. Вам нужно будет добавить поле для действия аудита, а в свой триггер вставить литерал для действия (например, «I» или «INSERT»). Я структурирую свои таблицы аудита следующим образом:

audit_id: INT IDENTITY 
audit_date: DATETIME GETDATE() 
audit_action: VARCHAR(16) ... or you can use CHAR(1) 
audit_user: VARCHAR(128) SUSER_SNAME()
(the fields from the table being audited)

Поскольку наши приложения используют Active Directory, я могу по умолчанию использовать для audit_user значение SUSER_SNAME().

person HardCode    schedule 22.08.2011
comment
Я использую ASP.NET Forms Authentication / Membership. Есть ли способ получить UserID в триггере? В настоящее время я передаю параметр @UserID из приложения - person Curt; 23.08.2011
comment
Эй, у вас есть какие-либо предложения о том, как я могу сделать это с помощью проверки подлинности/членства с помощью форм? - person Curt; 14.09.2011
comment
Я не уверен насчет членства — я никогда им не пользовался — но для решений, отличных от Active Directory, вам придется передать идентификатор пользователя хранимой процедуре в качестве обычного старого параметра. - person HardCode; 29.03.2012
comment
Чтобы использовать проверку подлинности с помощью форм, вы можете поместить идентификатор пользователя вашего приложения в строку подключения. Например, мое приложение называется SGA. В строку подключения включено ;Имя приложения=SGA User MyUserName. Затем вы можете использовать функцию T-SQL APP_NAME() и выделить из нее имя пользователя. - person Jeff Stock; 13.02.2014

arrow_upward
1
arrow_downward

Мы используем триггеры (единственный способ убедиться, что вы написали их для обработки нескольких вставок/обновлений/удалений записей), и наша структура немного отличается. Сначала у нас есть таблица, в которой хранится информация о действии, человеке/приложении, которое его выполнило, дата и количество затронутых записей. Затем у нас есть таблица, в которой хранятся детали. Эта таблица имеет столбец идентификатора, имя_столбца, старое значение, новое значение. (мы используем nvarchar (max) для столбцов в таблице аудита). Таким образом, если в таблице появляются новые столбцы, нам не нужно беспокоиться об изменении таблиц аудита. У нас есть один набор таблиц аудита для каждой таблицы, которую мы проверяем.

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

person HLGEM    schedule 22.08.2011

arrow_upward
0
arrow_downward

Проблема в том, что если я добавлю поле ColumnD в TableA, мне придется отредактировать таблицу TableA.Audit, а также приведенный выше сценарий.

Поэтому есть ли лучший способ сделать это?

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

Я был свидетелем попыток сделать это «лучше», когда вам не нужно обновлять триггер или таблицу аудита. Это всегда приводит к замене незначительной проблемы (эй, столбец добавлен, и мне нужно кое-что сделать) на гораздо более серьезные проблемы. Обычно проблемы с производительностью, правильностью и надежностью.

person Conrad Frix    schedule 22.08.2011