47010 (Відстежування змін за допомогою стовпців і таблиць аудиту)

_{Кафедра} комп’ютерних технологій

Індивідуальне завдання

з дисципліни: " Структурована мова запитів SQL"

Тема: Відстежування змін за допомогою стовпців і таблиць аудиту

Коломия 2009

План

Аудит за допомогою стовпців

Налаштування стовпців аудиту

Аудит за допомогою таблиць

Використання тригера UPDATE для заповнення таблиці аудиту

Використання OUTPUT для заповнення таблиці аудиту

Відновлення даних за допомогою таблиць аудиту

Використання таблиць аудиту для відновлення змінених даних

Висновок

Аудит за допомогою стовпців

Перевага аудиту за допомогою стовпців полягає в тому, що контрольна інформація розміщується в тій же таблиці, що і дані. У табл.1 перераховані деякі стовпці аудиту, які зазвичай додаються в таблиці.

Табл.1. Різні типи стовпців аудиту

Відстежування події	Типи даних	Коментарі
INSERT, UPDATE або DELETE INSERT, UPDATE або DELETE DELETE	DATETIME VARCHAR BІТ/ТІN YINT	Використовується для відстежування дати і часу виконання відстежуваної дії Зазвичай використовується з функцією GETDATE () як значення за умовчанням, але значення може задаватися і зухвалим застосуванням Використовується для відстежування імені користувача або додатку, що виконує відстежувану дію. Використовується для того, щоб помітити дані як що видаляються. Це може з великою ефективністю застосовуватися в індексуванні і фільтрації

По цій таблиці можна зробити вивід, що зміни даних насправді не протоколюються. Найбільш ефективний спосіб використання стовпців аудиту - це відстежування факту внесення зміни, часу зміни і особи або додатку, що виконав цю зміну. Можна використовувати ці стовпці в будь-якій комбінації, щоб відстежувати зміни в записах в реальній таблиці.

Налаштування стовпців аудиту

Спочатку потрібно визначити події, які потрібно відстежувати. В даному прикладі ви показано як додавати стовпці аудиту для відстежування ініціатора змін, дати і часу створення запису, дати і часу останнього оновлення запису і того, чи був видалений запис з таблиці Person. Address бази даних Adventure Works.

Вибравши таблицю (Person. Address) і визначивши події, які відстежуватимуться, потрібно вирішити, які стовпці додати в таблицю.

Стовпець ModifiedDate вже існує в таблиці. Він відстежуватиме дату, що показує, коли запис був востаннє змінений або видалений.

Стовпець CreatedDate відстежуватиме, коли був створений запис. Тип даних цього стовпця DATETIME, з використанням функції GETDATE () для надання поточної дати як значення за замовчуванням.

Стовпець ModifiedBy - це стовпець VARCHAR, який міститиме ім'я користувача або деякі інші засоби для ідентифікації користувача або додатки, які внесли зміни.

Стовпець IsDeleted - стовпець з типом даних BIT, який використовуватиметься для запису про видалення рядка. Дата і користувач відстежуватимуться через стовпці ModifiedDate і ModifiedBy. Якщо запис був видалений, цей стовпець буде помічений, а в зміненому стовпці будуть відомості про того, хто і коли видалив запис.

Тепер можна виконати представлений нижче сценарій, щоб змінити таблицю Person. Address.

USE Adventure Works

GO

ALTER TABLE Person. Address

ADD CreatedDate DATETIME NULL DEFAULT GETDATE ()

,ModifiedBy VARCHAR (50) NULL

, IsDeleted BIT DEFAULT (0)

Далі, якщо змінювати таблицю з вже наявними даними, слід задати в стовпці CreatedDate значення, що показує, що стовпець був створений до того, як був початий аудит. Щоб задати значення CreatedDate, потрібно виконати наступний код:

UPDATE Person. Address SET Createddate = '1/1/1980';

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

Остання дія в цьому процесі - це додавання фільтру до всіх процедур і програм, що посилаються на дану таблицю, щоб запобігти поверненню видалених записів. Ось фільтр, який потрібно використовувати:

WHERE IsDeleted = 0

Аудит за допомогою таблиць

Тепер ми знаємо, як використовувати аудит для повідомлення про зроблені зміни. Проте єдина зміна, яка може бути легко відмінена - це подія DELETE. Досить просто скинути прапор IsDeleted, і дані будуть знову доступні. Існує також можливість відмінити подію CREATE, якщо про цю дію є достатня інформація. Проте якщо потрібно мати можливість повністю відстежувати стан даних перед зміною, можливо, кращим варіантом виявиться використання таблиць аудиту. Цю можливість слід використовувати з обережністю, тому що вона може викликати багато проблем з обслуговуванням і продуктивністю. Такі проблеми виникають тому, що доводиться копіювати дані в таблицю аудиту і змінювати їх в початковій таблиці. Для цього прикладу ми задамо аудит на базі таблиці в таблиці Sales. Special Offer. Мета - відстежування будь-яких змін в цій таблиці і забезпечення можливості відмінити зміни після того, як вони були зафіксовані.

Налаштування таблиці аудиту

Запускаємо SQL Server Management Studio і знаходимо в Object Explorer (Оглядач об'єктів) в базі даних Adventure Works таблицю Sales. SpecialOffer.

Генеруємо базовий сценарій аудиту, клацнувши правою КНОПКОЮ миші на таблицю Sales. SpecialOffer і вибравши з контекстного меню команди Script Table As, Create To, New Query Editor Window (Створити сценарій для таблиці, Використовуючи CREATE, В новому вікні редактора запитів). Після цього відкриється нове вікно запиту з готовим для редагування сценарієм CREATE TABLE.

Відредагуємо сценарій, виконавши перераховані нижче дії. Для цього прикладу остаточна редакція сценарію показана у дії 4. Спочатку видаляємо всі додаткові сценарії. Потрібно видалити всі рядки кодів, які не входять в інструкцію CREATE. Потім змінюємо ім'я таблиці з Sales. SpecialOffer на Sales. SpecialOffer_Audit.

Тепер видаляємо всі обмеження для таблиці і присвоюємо для всіх стовпців значення NULL. Завдяки цьому таблиця буде більше схожа на журнальну таблицю. В цьому випадку таблиця аудиту не повинна заважати звичайним операціям в таблиці із самого початку. Це також повинно спростити управління таблицею. Додаємо всі додаткові стовпці, які допомагатимуть у визначенні типу змін, дати змін і інших елементів аудиту, які потрібно відстежувати. У даному прикладі потрібно додати стовпці, перераховані в табл.2.

Табл. 2. Стовпці, які потрібно додати в таблицю аудиту

Ім’я стовпця	Тип даних
AuditModif iedDate	DATETIME
AuditType	NVARCHAR (20)

4. Виконуємо остаточний сценарій, представлений нижче, в базі даних Adventure Works. (Цей код можна знайти у файлах прикладів під ім'ям CreateАuditTable. sql)

USE AdventureWorks;

GO

CREATE TABLE Sales. SpecialOffer_Audit (

SpecialOfferID INT NULL,

Description NVARCHAR (255) NULL,

DiscountPct SMALLMONEY NULL,

[Type] NVARCHAR (50) NULL,

Category NVARCHAR (50) NULL,

StartDate DATETIME NULL,

EndDate DATETIME NULL,

MinQty INT NULL,

MaxQty INT NULL,

rowguid UNIQUEIDENTIFIER NULL,

ModifiedDate DATETIME NULL,

AuditModifiedDate DATETIME NULL,

AuditType NVARCHAR (20) null

);

GO

Основні способи переміщення даних в таблиці аудиту в SQL Server 2005 - це тригери бази даних і нова пропозиція T-SQL OUTPUT. Проте OUTPUT додає деякі цікаві можливості. Тепер ми на прикладі вивчимо кожен з цих двох варіантів.

Використання тригера UPDATE для заповнення таблиці аудиту

Створюємо в таблиці Sales. SpecialOffer тригер, який записуватиме попередній стан даних в створену нами таблицю Sales. SpecialOffer_Audit.

Код, приведений нижче, - це приклад синтаксичної конструкції, яку можна використовувати.

USE AdventureWorks

GO

CREATE TRIGGER SpecialOfferUpdateAudit ON Sales. SpecialOffer

FOR UPDATE

AS

INSERT INTO Sales. SpecialOffer_Audit

(SpecialOfferID

,Description

,DiscountPct

, [Type]

,Category

,StartDate

,EndDate

,MinQty

,MaxQty

,rowguid

,ModifiedDate

,AuditModifiedDate

,AuditType)

SELECT TOP 1 d. SpecialOfferlD

,d. Description

,d. DiscountPct

,d. [Type]

,d. Category

. d. StartDate

,d. EndDate

. d. MinQty

. d. MaxQty

,d. rowguid

,d. ModifiedDate

,GETDATE ()

,’UPDATE’

FROM deleted d;

GO

Перевага використання тригера полягає в тому, що він захоплюватиме будь-які оновлення, які відбудуться в таблиці, незалежно від їх джерела. Це варіант аудиту з повним обхватом. Якщо мова йде про дані, які змінюються без контролю з вашого боку, то це чудовий варіант. Але якщо ми ретельно контролюємо дані, які заносяться в таблицю, особливо якщо це виконується за допомогою процедур, що зберігаються, то в SQL Server 2005 є нова можливість аудиту змін - пропозиція OUTPUT.

Використання OUTPUT для заповнення таблиці аудиту

Щоб ефективно використовувати OUTPUT, кожну подію, яку потрібно відстежувати, зажадає розробки процедур, що зберігаються, і інструкцій SQL, які використовуватимуться для оновлення (UPDATE), вставки (INSERT) або видалення (DELETE) даних у відстежуваних таблицях. OUTPUT надає доступ до таблиць, що вставляються і видаляються, в цих процедурах і інструкціях SQL. Тепер не обов'язково використовувати тригери для доступу до даних. Представлений нижче код показує приклад використання OUTPUT для аудиту оновлення в таблиці SpecialOffer в таблиці SpecialOffer_Audit.

USE AdventureWorks

GO

UPDATE Sales. SpecialOffer

SET description = 'Big Mountain Tire Sale'

OUTPUT deleted. SpecialOfferID

,deleted. Description

,deleted. DiscountPct

,deleted. [Type]

,deleted. Category

,deleted. StartDate

,deleted. EndDate

,deleted. MinQty

,deleted. MaxQty

,deleted. rowguid

,deleted. ModifiedDate

,GETDATE ()

’UPDATE’ INTO Sales. SpecialOffer_Audit

WHERE SpecialOfferID = 10

Пропозиція OUTPUT поміщає змінені дані в рамках простого доступу в процесі зміни даних. В процесі операцій UPDATE і DELETE доступний префікс DELETED. У процесі операцій UPDATE і INSERT доступний префікс INSERTED. Потрібно звернути увагу на те, що обидва префікси не можуть бути доступними одночасно, на відміну від таблиць deleted і inserted, які використовуються в тригерах. Ця взаємодоступність вимагає, щоб різні операції оброблялися по-різному для збору потрібних даних і переміщення їх в таблиці аудиту.

Відновлення даних за допомогою таблиць аудиту

Тепер, коли у нас є два варіанти завантаження даних в таблицю аудиту, можна подумати, для чого використовувати ці дані. Оскільки всі зміни в таблиці зберігаються в таблиці аудиту, можна відновити будь-які зміни даних, перезаписавши поточні дані зміною, яку потрібно зберегти. Таблиця аудиту може зберігати декілька версій даних, тому найчастіше це доведеться робити уручну. Проте можна також створити обслуговуючу процедуру, що зберігається, для відміни найостаннішої зміни.

Використання таблиць аудиту для відновлення змінених даних

1. Визначаємо, який запис слід відновити. Для цього потрібно ідентифікувати змінний запис і дані, які його замінять.

2. Користуємось додатком UPDATE для перезапису поточних даних зміною, яку слід відновити в цій таблиці. У даному прикладі доведеться використовувати або властивість rowguid, або стовпець SpecialOf f erID у поєднанні з AuditModif iedDate як критерієм для інструкції UPDATE, як показано нижче.

USE AadventureWorks

GO

UPDATE Sales. SpecialOffer

SET Description = а. Description

,DiscountPct = а. DiscountPct

,Type = а. Type

,Category = а. Category

,StartDate = а. StartDate

,EndDate = а. EndDate

,MinQty = а. MinQty

,MaxQty = а. MaxQty

,rowguid = а. rowguid