SQL Server 2005 built-in Tool building review system

As a database administrator for billing applications (DBA), the company's chief information Officer (CIO) often invites me to meet with Sarbanes-oxley reviewers to discuss the security and consolidation of corporate data. Simply put, he wants us to answer a number of questions through the right documents: who has access to our data, how to authorize access, and how we monitor access to prevent someone from invading, logging into data, or doing something they shouldn't.

My CIO and I have learned some business solutions that demonstrate compliance with the Sarbanes-oxley Act. However, we decided to apply the SQL Server 2005 built-in tools to build our "self-produced" review system.

Server 2005 New Features

Before using SQL Server 2005, we applied Data Manipulation language (DML) triggers to understand when data changes would occur in the database. We can point to the review log saying that there is a list of all the insert (insert), update (update) and delete (Detele) statements we have published since the last review, including the time, date, person, or program that executed the statement.

A DML trigger is a dedicated stored program that the database engine starts, and it has a significant role in establishing a review index for data changes. However, a DML trigger will only start if the data has changed. Before using SQL Server 2005, there is no better way to track the structure or security changes in the database server.

SQL Server 2005 supports data definition language (DDL) triggers. These triggers are not established and started when a server or database-level event occurs. DDL triggers allow us to track important changes in the database-intentional, unintentional, or malicious.

Track all Registrations

The following is a typical example of a DDL trigger that improves database monitoring capabilities. In previous versions of SQL Server, it was difficult to see when new registrations were established, when a new database was established, or when new licenses were assigned to different users.

In SQL Server 2005, it is relatively easy to track such security changes. To illustrate this point, no matter who the user is, let's design a solution that tracks all the behavior on the server. We will use the DDL trigger event ddl_login_events to establish the audit index. This event trigger will look for any registered events on the server, including all established registrations (create login), modify registration (ALTER LOGIN), and delete registration (delete login) events.

In SQL Server 2005, create a review index database by running the CREATE DATABASE Ddltriggertest statement, and then define the following fields:

IDCol SMALLINT IDENTITY(1,1) PRIMARY KEY，
XMLEvent XML
DatabaseName VARCHAR (50)
SystemUser VARCHAR(50)
EntryDate DATETIME DEFAULT (GETDATE())

Note The table uses the XML data type, which is new in SQL Server 2005. As the name suggests, its role is to save XML data.

Once the table is ready to track events, you can establish the necessary triggers to monitor these events. As a first step, our solution looks for registration events that occur on the server, including any setup registration (create login), modify registration (ALTER LOGIN), and delete registration (delete login) events. Run the following script to establish the trigger:

CREATE TRIGGER tr_Security
ON ALL SERVER
For DDL_LOGIN_EVENTS
AS
BEGIN
?INSERT INTO DDLTriggerTest..
EventtableData(XMLEvent, DatabaseName, SystemUser)
?VALUES (EVENDATA(), DB_NAME(), SYSTEM_USER)
END