Кафедра комп’ютернихтехнологій
Індивідуальне завдання
з дисципліни: " Структурована мова запитів 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 ONSales. 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
,ModifiedDate = а. ModifiedDate
FROM Sales. SpecialOffer_Audit а
WHERE Specialoffer. SpecialOfferlD = 10
AND а. SpecialOfferlD = 10
AND а. AuditModifiedDate = '2006-04-02 22: 40:27.513'
Якщо у нас є дані, які потребують регулярноговідновлення, можна інкапсулювати приведений вище код в процедуру обслуговування,що зберігається. Проте якщо ми вирішимо реалізувати її, у нас можуть виникнутипроблеми з введенням відновлених даних. Описані варіанти добре підходять дляобмеженої кількості рядків в одній таблиці. Якщо ми працюємо з масовимвисокопродуктивним завантаженням в декількох таблицях, слід використовуватимоментальні знімки, про які йшла мова в першому розділі.
Висновок
Ми навчилися створювати моментальні знімкиданих. Моментальні знімки можуть ефективно використовуватися для вирішеннярізних завдань в процесі розробки, тестування і виробництва. Можнавикористовувати архівні дані для підведення підсумків і аналізу даних на певнихвідрізках часу. Ми також навчилися виконувати аудит змін даних і відновлюватиокремі записи даних аудиту. Зберігаючи архівні дані і відстежуючи зміни в базіданих, можна забезпечити цілісність даних, використовуючи можливість відміниокремих змін, не відновлюючи всю базу даних.