Browse DevX
Sign up for e-mail newsletters from DevX


Use SQL Server Triggers to Track Unauthorized Database Changes  : Page 2

SQL Server can do all the work of tracking database modifications by responding to changes with predefined tasks.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Trigger an Auditing Solution
Finding the unauthorized user can be a simple task for SQL Server triggers. The first thing you need is a table to store all the audit data. The table should point to all actions by all users, including unauthorized users and unauthorized changes. Then you add triggers that react to each type of possible modification action-an INSERT, an UPDATE, or a DELETE statement. To create a table in which you can store this new audit data, open the Query Analyzer, specify the database in the dropdown database control, and then run the SQL statement in Listing 1.

Listing 1: Create Audit Table If EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.AuditTrail') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) DROP TABLE dbo.AuditTrail GO CREATE TABLE dbo.AuditTrail ( AuditTrailID Int IDENTITY (1, 1) NOT NULL, TableName VarChar (50) NOT NULL, ActionTaken Char (1) NOT NULL, ActionUser VarChar (50) NOT NULL, ActionDate DateTime NOT NULL ) ON [PRIMARY] GO

You'll use the Northwind sample database that comes with SQL Server in this example, but you can use any database you like. This procedure deletes a table named AuditTrail, if found, and creates a new table named AuditTrail with the attributes shown in Table 2. If you think you'll need to restart the process on a regular basis, use a stored procedure or a user-defined function. You must surround the PRIMARY keyword with brackets, and remember to include brackets around any of your object names if they include space characters.

Table 2: AuditTrail Table

Column Datatype NULL
AuditTrailID Identity Not allowed
TableName VarChar(50) Not allowed
ActionTaken Char(1) Not allowed
ActionUser VarChar(50) Not Allowed
ActionDate DateTime Not Allowed

Now you're ready to add the actual triggers. (In this example, you won't attempt to create or even describe a full-featured audit trail. You use the audit trail as an example of using triggers to update a file when data changes.) You'll create two triggers, one that responds to an INSERT or an UPDATE, and one that responds to a DELETE. Begin with the INSERT and UPDATE trigger as follows:

  1. Expand the Enterprise Manager to the Northwind database and double-click the Tables icon.
  2. Right-click the Products table, choose All Tasks, and select Manage Triggers.
  3. Choose <new> from the Name dropdown control.
  4. Enter the following trigger in the Text control (see Figure 2):

    CREATE TRIGGER [AuditInsertUpdate] ON dbo.Products FOR INSERT, UPDATE AS INSERT INTO AuditTrail (TableName, ActionTaken,
    ActionUser, ActionDate) VALUES ('Products', 'I', User_Name(), GetDate())

  5. Click Check Syntax and make corrections to the trigger statement if necessary.
  6. Click Apply when the trigger is correct, and then click Close.

Figure 2: Enter the INSERT and UPDATE Trigger

SQL Server will allow the user to insert and update new records. After which, the above triggers will modify the AuditTrail table by entering the following:

  • The name of the table modified into the TableName column
  • The letter I (for Insert) or U (for Update) into the ActionTaken column
  • The results of the User_Name() function at the time the change is executed into the ActionUser column
  • The results of the GetDate() functions at the time the change is executed into the ActionDate column

Now, test the trigger by changing the UnitPrice entry for one of the products in the Northwind Products table. To do so, take the following steps:

  1. Open the Query Analyzer, and specify Northwind in the database dropdown control.
  2. Run the following SQL statement to reduce the current value of the product identified as 1 from $18 to $1:

    UPDATE dbo.Products SET UnitPrice = 1 WHERE ProductID = 1

  3. Run the following SQL statement to see if the trigger worked. Figure 3 shows the results.

    SELECT * FROM dbo.AuditTrail

Figure 3: Updating the Products Table Fires the Table's Trigger

Thanks for your registration, follow us on our social networks to keep up-to-date