Two SQL audit triggers you can’t afford to ignore

29 Nov 2023 26253 views 0 minutes to read Contributors

data audit

SQL Server auditing is no longer a ‘nice to have’ for any business. Public awareness of the risks of data breaches is now extremely high. Your ability to demonstrate that you can maintain data security is an essential element of customer trust.

In fact, according to PwC, 87% of consumers say they will take their business elsewhere if they don’t trust a company to handle their data responsibly. What’s more, under GDPR, it is also a legal requirement. That means all organizations now need data security audits and data forensics.

The purpose of a security audit is to log all the information, such as user access to records, changes to data or to the configuration of servers, and log-in attempts. You can then use this log to identify and report any suspicious or malicious activity, data breaches or human errors.

How does data auditing work?

There are many SQL Server auditing techniques. Some can be time consuming and affect database performance. However, some SQL Server audit triggers can be easily set up to track a variety of essential information.

Here are two SQL Server audit triggers we highly recommend in addition to any default SQL server audits:

1.       DDL (Data Definition Language) auditing. This covers commands such as creating a new table, altering or deleting an existing table.

2.       DML (Data Manipulation Language) auditing. This covers common commands such as entering new data, changing existing data or deleting data.

If any DDL or DML events occur on your SQL Servers, the trigger will capture this action. This captures all the information needed, such as the type of change, the time and user. We believe DDL and DML auditing is a must-have for any organization that needs to keep sensitive customer data secure.

Here’s a real life example

One of our customers has a database table that stores sensitive data, such as credit card numbers and addresses, for millions of customers.  They came to us with an urgent question about some changes that had been made to the data:

Do you know who did it?

Fortunately, this customer had DDL and DML auditing in place on the customer server and this had captured all the events. The trigger had recorded everything; what kind of changes were made, which user made them, on what computer and the date and time of the changes. This output was stored on a separate, locked database that only DBAs with the right permission levels could access.

Thankfully, in this scenario, the changes were nothing malicious; it was simply a human error. We were able to pinpoint the mistake, and easily switch back to the original snapshot of data because of the data that was collected. However, if they had not put these audit triggers in place, it would have been extremely difficult, perhaps impossible, to capture or track these events in the first place.

Servers can be under attack at any time without DBAs being aware. But, these relatively straightforward data audits can help to ensure data safety. And, they provide additional evidence that you are complying with the best effort principle for keeping data secure under GDPR.

What kind of SQL audit is right for my business?

There are many different SQL Server data audits and some companies have certain mandatory audits. Which solution or combination of solutions is appropriate for your company? The exact answer depends on many factors, but DDL and DML audit triggers are easy to implement solutions that are relevant to many organizations.

Would you like to discuss your auditing requirements in more detail? You can contact us here.

Report a Bug

In this article