От новичка до гуру: Курсы программирования на CyberDuff

Возможности аудита SQL Server 2016

У меня есть типичное веб-приложение, основанное на CRUD. Когда определенные таблицы или столбцы изменяются, нам нужно хранить информацию об этом в истории. Это может, например. быть таблицей AUDIT, содержащей новые/старые значения столбцов, временную метку, к какой таблице она относится, имя пользователя, изменившего ее, и т. д.

Обычно я реализовал это, например. с триггерами для получения этой информации и сохранения в таблице AUDIT.

Однако можно ли добиться того же автоматически с помощью функций аудита SQL Server 2016? Мне приходит в голову несколько вопросов: 1) может ли он хранить данные аудита в таблице базы данных, как в приведенном выше примере с триггерами? 2) разрешено ли приложению читать данные или для доступа к проверенным данным требуются специальные учетные данные администратора? 3) можно ли указать, какую информацию нужно логировать, или функционал логирует все автоматически?

sql-server audit sql-server-2016 audit-trail
05.10.2016

  • Является ли целью этих данных аудит или история? Для истории вы можете рассмотреть новую функцию темпоральных таблиц, если она подходит. 05.10.2016
  • Я считаю, что история и аудит — это одно и то же. Ключевые моменты заключаются в том, что 1) нам, вероятно, нужно определить структуру таблицы, 2) страницы приложения должны иметь возможность запрашивать контрольные/временные таблицы. 05.10.2016
  • Для таблиц истории, как предложил Дэмиен, лучше всего использовать временные таблицы kodyaz.com/sql-server-2016/ в SQL Server 2016. К сожалению, в этих таблицах нет имени пользователя, вызвавшего изменение 05.10.2016

Ответы:


1

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

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

Так же, как эмпоральные таблицы в SQL Server 2016. вам необходимо использовать или создать таблицу истории или таблицу журнала вручную

Пожалуйста, проверьте следующий код триггера SQL

CREATE TRIGGER dbo.LogTbl
    ON dbo.Tbl
AFTER INSERT, UPDATE, DELETE
AS

IF EXISTS (
  SELECT * FROM Inserted
) -- INSERT or UPDATE
BEGIN
    IF EXISTS (
      SELECT * FROM Deleted
    )
  -- UPDATE Statement was executed
  INSERT INTO HistoryTbl (
    ...
    UpdatedDate,
    UpdatedByUserId
  )
  SELECT
    ...
    i.UpdatedDate,
    i.UpdatedByUserId
  FROM Deleted d
  INNER JOIN Inserted i ON i.Id = d.Id

  ELSE
  -- INSERT Statement was executed
  INSERT INTO HistoryTbl (
    ...
    InsertedDate,
    InsertedByUserId
  )
  SELECT
    ...
    i.InsertedDate,
    i.InsertedByUserId
  FROM Inserted i

END
ELSE
  -- DELETE Statement was executed
  INSERT INTO HistoryTbl (
    ...
    DeletedDate,
    DeletedByUserId
  )
  SELECT
    GETDATE(),  ---!!!
    USER_ID()   ---!!!
  FROM Deleted

GO

Обратите внимание на команду DELETE, в команде удаления у нас нет удаленных пользовательских данных. Поэтому я использовал USER_ID(), вы также можете использовать USER_NAME().

Другой подход может заключаться в том, что вместо УДАЛЕНИЯ строки вы можете УДАЛИТЬ ее, ОБНОВИВ значение столбца ACTIVE на false. Я имею в виду ОБНОВЛЕНИЕ данных, если вы установите его активное поле в 0, это можно подумать, так как оно удалено.

Поэтому вам нужно изменить все свои коды, выбрав только активные данные...

05.10.2016
  • Да, триггеры — это один из подходов, и я использовал его раньше, но теперь мне интересно, можем ли мы использовать возможности SQL Server 2016 для этого «из коробки»? Триггеры могут вызвать всевозможные проблемы с производительностью и путаницу... 05.10.2016
    • Новые материалы
    Основы Spring: Bean-компоненты, контейнер и внедрение зависимостей
    Как лего может помочь нашему пониманию Когда мы начинаем использовать Spring, нам бросают много терминов, и может быть трудно понять, что они все означают. Итак, мы разберем основы и будем..
    Отслеживание состояния с течением времени с дифференцированием снимков
    Время от времени что-то происходит и революционизирует часть моего рабочего процесса разработки. Что-то более забавное вместо типичного утомительного и утомительного процесса разработки. В..
    Я предполагаю, что вы имеете в виду методы обработки категориальных данных.
    Я предполагаю, что вы имеете в виду методы обработки категориальных данных. Пожалуйста, проверьте мой пост Инструментарий специалиста по данным для кодирования категориальных переменных в..
    Игра в прятки с данными
    Игра в прятки с данными Я хотел бы, чтобы вы сделали мне одолжение и ответили на следующие вопросы. Гуглить можно в любое время, здесь никто не забивается. Сколько регионов в Гане? А как..
    «Раскрытие математических рассуждений с помощью Microsoft MathPrompter и моделей больших языков»
    TL;DR: MathPrompter от Microsoft показывает, как использовать математические рассуждения с большими языковыми моделями; 4-этапный процесс для улучшения доверия и рассуждений в математических..
    Раскройте свой потенциал в области разработки мобильных приложений: Абсолютная бесплатная серия
    Глава 6: Работа в сети и выборка данных Глава 1: Введение в React Native Глава 2: Основы React Native Глава 3: Создание пользовательского интерфейса с помощью React Native Глава 4:..
    Все о кейсах: Camel, Snake, Kebab & Pascal
    В программировании вы сталкивались с ними при именовании переменной, класса или функции. Поддержание согласованности типов и стилей случаев делает ваш код более читабельным и облегчает совместную..