Saturday, March 14, 2015

Tracking Schema/ data definition change (audit tail DDL changes)

Tracking DDL(Data Definition Langauage) changes 

This may be bit advance for some. Go through DDL Trigger first.
DML Trigger

In application development , the database changes are general. Whenever some workflow in application  require to change, generally sql developer perform the ddl changes and later on or same time forgot out what commands he/she had run in order to perform task. Sometime developer require to perform a task by adding some columns in table or something like that ,but administrator dn't aware about all change.So using DDL trigger we can track that all.

DDL trigger in sql server , helps a developer/administrator to track the DDL changes and command run.
Suppose I am a sql developer, I had execute

  ALTER TABLE table1 Add ColumnNew nvarchar(225) 

 & that cause a new column in table1. If user wants to tack the sql command ,action, user etc etc the DDL Trigger is best solution in case for all DDL changes.

DDL Trigger: - The trigger used to track and work on ddl changed. DDL >> Data Definition Language

Why require DDL Trigger:
  • To track ddl change.
  • For audit trail of data definition change.
  • For restricting user to perform data definition changes.

Suppose a user tried to delete a table. In that case trigger can be used to restrict user to delete trigger or the command can be tracked.
Where to record ddl changes:
  • Can track /stored in same database.
  • Can use different database to track.
  • Can use the different server/linked server in track ddl changes

DDL trigger applicable level:

Database Level: The triggers which are only applicable to database on which that has been applied.

Server Level: The trigger applicable to all the database stored on server . imposed restriction etc on database server instance. 

Location where Database level triggers can be seen:
Databases >> [Specific Database] >> Programmability >> Database triggers
Image shows the database level ddl trigger for database MYBlog

Location where for Server lever trigger can be seen:
Database Engine Instance name (SQL05)>> Sever Objects >> Triggers

Database level ddl trigger:

Suppose we does not want that application /user can create/alter/drop table . the ddl trigger can restrict user

Trigger NoNewTablePlease restrict to perform create table /alter table / drop table operations

Same way we can perform number of operations/restriction as such revoking permission / grant permission on database objects.

Deactivating/Activating the ddl trigger :

Disable trigger NoNewTablePlease on database
It will disable trigger named NoNewTablePlease,

Disable trigger All on database   
Disable all ddl trigger on a database

Enable ddl trigger
Enable trigger NoNewTablePlease on database

Enable trigger All on database
Enable all trigger for a database

To fetching the data from ddl command & fill in table to track audit trail for ddl command
EVENTDATA() function provider detail of ddl command that can be used in dl trigger and it’s collect parameters in xml format , a programmer can easily get that parameter & can record as per org/application demands.

It contain information regarding
  • ·         DatabaseObject
  • ·         DatabaseName
  • ·         EventType
  • ·         TSQLCommand
  • ·         LoginName etc etc

DatabaseObject :- name of object on which some action/event is performed
DatabaseName : database on which sql query is running /effecting db
EventType: the type of action /event performed such as Create_Table ,Alter_Table,Create_Database etc
TSQLCommand: the command performed/run.
LoginName: the name of user under which query is being executed.

Perform audit trail of the all ddl commands /statements run:-

Server Level Trigger:-

Restrict user to perform server level action as create database , drop database , create alter login etc etc

You can explore more on your development machine , Go through DDL Trigger as well.


No comments:

Post a Comment