Home Business IT Security Using database triggers for alerting and auditing

Whether an enterprise application is custom-built or purchased off-the-shelf, you can always delve under the hood into the database layer to add your own specific functionality, like alerting and auditing when data is modified.

The 'magic' for want of a better word that makes this happen is triggers. This is a feature of high-end database systems like Oracle, SQL Server and MySQL that causes program code to execute via the ordinary creating, updating or deleting of data that typically occurs.

Triggers are implemented at the database layer and fired off by the database engine itself. This means you do not need to modify the code within your front-end application that the users see.

This is particularly important and beneficial when the program code is not in your control anyhow, like a third-party application.

It is also of value when you have multiple interfaces to your data - perhaps via a web app, a fat-client app and a mobile app - whether you can maintain the source code or not. In this case, the trigger can be created within the database and applies no matter how the user is working.

There are, of course, some catches.

I'll describe these on page two, and then give working T-SQL trigger code on page three to illustrate.


If the database is from a third-party application then you must be careful to maintain compatibility with version updates. A well-written trigger will be invisible to anyone using the software but a malformed trigger will interfere with its regular operation by causing operations to fail or throw errors.

Additionally, you must consider possible performance hits. A trigger will be activated each and every time something is changed in the specific database table you are targeting. If that table is frequently written to or updated then you are firing many, many triggers, each with its own small grab at the CPU.

If you wish an alert to be raised immediately when certain database changes occur then a trigger is for you; however, if you only want (say) a daily report indicating what has changed then there are other means of achieving this which can be implemented to execute out of working hours.

Over the page is a simple trigger implemented in Microsoft's SQL Server T-SQL language. The principles are identical for other enterprise databases though the syntax will vary.

In this example, we wish to be notified whenever a new record is inserted into the PurchaseOrders table, or whenever an existing record is modified.

We create a trigger using the T-SQL 'create trigger' command, specifying both the table to monitor (PurchaseOrders) and the actions that cause it to fire (insert, update).

Within the program code we have access to a special table called inserted - this can be queried to divulge precisely what the new or changed data is. Similarly, a trigger that fires whenever data is deleted has access to a special table called deleted.



Within the trigger code we extract fields from table inserted, construct a message and then send this via e-mail using SQL Server's msdb.dbo.sp_send_dbmail stored procedure.

Note that in the below code it is assumed that only one row is modified at a time, which is typically the case when operating an application. However, if an administrator performed a bulk UPDATE command then the trigger would break because each of the SELECTs from table inserted would return multiple values.

You will need to test your trigger against thorough test cases to ensure it is not going to break in production, but despite this the power it offers in instantaneous alerting, plus auditing of modifications, is unparalleled.

create trigger SendNotification
on PurchaseOrders
after insert, update
as

declare @PONumber nchar(10)
declare @User varchar(250)
declare @Division varchar(50)
declare @Location varchar(50)
declare @Notes varchar(250)

set @PONumber = (select PONumber from inserted)
set @User = (select [User] from inserted)
set @Division = (select Division from inserted)
set @Location = (select [Location] from inserted)
set @Notes = (select [Notes] from inserted)
if @Notes is NULL
begin
 set @Notes = ''
end

declare @Msg varchar(500)

set @Msg = 'PO number:    ' + @PONumber + char(13) +
   'User:         ' + @User + char(13) +
   'Division:     ' + @Division + char(13) +
   'Location:     ' + @Location + char(13) +
   'Notes:        ' + @Notes

 exec msdb.dbo.sp_send_dbmail
  @recipients = 'alerts@company.com',
  @body = @Msg,
  @subject = 'Purchase order change';
go

HOW TOP MANAGERS MOTIVATE, ENERGISE EMPLOYEES

Download an in-depth guide to managing a healthy, motivated and energetic workforce without breaking the bank.

DOWNLOAD NOW!

David M Williams

joomla site stats

David has been computing since 1984 where he instantly gravitated to the family Commodore 64. He completed a Bachelor of Computer Science degree from 1990 to 1992, commencing full-time employment as a systems analyst at the end of that year. Within two years, he returned to his alma mater, the University of Newcastle, as a UNIX systems manager. This was a crucial time for UNIX at the University with the advent of the World-Wide-Web and the decline of VMS. David moved on to a brief stint in consulting, before returning to the University as IT Manager in 1998. In 2001, he joined an international software company as Asia-Pacific troubleshooter, specialising in AIX, HP/UX, Solaris and database systems. Settling down in Newcastle, David then found niche roles delivering hard-core tech to the recruitment industry and presently is the Chief Information Officer for a national resources company where he particularly specialises in mergers and acquisitions and enterprise applications.

 

 

 

 

Connect

Join the iTWire Community and be part of the latest news, invites to exclusive events, whitepapers and educational materials and oppertunities.
Why do I want to receive this daily update?
  • The latest features from iTWire
  • Free whitepaper downloads
  • Industry opportunities