Pros and cons of using SQL Server audit triggers for DBAs
Using triggers to capture audit information in SQL Server can be instrumental in keeping track of database use and changes. But they aren't a perfect fit for all cases.
Database administrators can employ various techniques to audit the use of SQL Server databases for security and regulatory compliance purposes. One of the most common methods is creating SQL Server audit triggers to automatically capture information about transactions and changes to database tables.
As part of the database auditing processes, triggers in SQL Server are often used to ensure and improve data integrity, according to Tim Smith, a data architect and consultant at technical services provider FinTek Development. For example, when an action is performed on sensitive data, a trigger can verify whether that action complies with established business rules for the data, Smith said.
Triggers are themselves set to perform specific actions when pre-established criteria are met, freeing database administrators (DBAs) from the need to manually write and run audit queries. But triggers aren't the right choice for every scenario. In a webinar on auditing best practices and options hosted by database tools vendor Idera, Smith discussed the pros and cons of using audit triggers in SQL Server.
Advantages of audit triggers
SQL Server audit triggers give DBAs the ability to track database changes on a granular level. This makes them ideal for use in smaller SQL Server environments that need to be monitored in a detail-oriented manner, Smith said.
Triggers can also be used to audit one specific database object, as opposed to a whole host of objects. In addition, they can fire off warning emails to DBAs if they detect issues with data manipulation language and data definition language operations or unauthorized login attempts. And Smith said triggers are more cost-effective than other auditing alternatives, which can be expensive to develop and maintain.
When audit triggers are a good option and when they aren't
Once admins program audit triggers, they run automatically, leaving SQL Server DBAs with more time to tend to other duties. While that can free DBAs to handle multiple other tasks, they do need to be mindful of the trigger parameters in the databases, Smith said. Otherwise, the audit triggers can turn into unnecessary stumbling blocks for both admins and end users.
SQL Server audit trigger disadvantages
Because [triggers] are automatic, that can be a valuable thing, but it can be to our detriment if we're not careful.
Tim SmithData architect and consultant, FinTek Development
Triggers add processing overhead to database systems and can make them run slower. In particular, triggers tend to struggle in environments with heavy online transaction processing or environments with large amounts of data, according to Smith and other SQL Server experts. Using triggers to capture audit information in such systems can cause performance and scalability problems.
Also, when using audit triggers, the business rules associated with them must be meticulously planned out, Smith said.
"A trigger is ... a reactive approach," he explained.
Because it's designed to react to specific stimuli, a trigger can misinterpret business rules that are poorly planned or badly worded. But properly constructing these rules can be time-consuming and sometimes difficult.
Another issue with using triggers is that they often have permissions that users don't, which can cause problems when a user needs to bypass the audit triggers in a database.
Smith also detailed how malicious hackers can use SQL Server audit triggers to their advantage. If hackers know a system is equipped with triggers, they may repeatedly attack it to create a diversion or to see how quickly the DBA responds and what security measures he takes.
While triggers can be useful auditing tools in databases, they aren't a good match for every system -- and even when they are, DBAs need to closely manage them and their usage. "Because [triggers] are automatic, that can be a valuable thing, but it can be to our detriment if we're not careful," Smith said.
