Auditing Access to Sensitive Tables with Triggers and SQL Audit
By Tom Nonmacher
Securing access to sensitive data is a key priority for any organization. One way to ensure data security is by auditing access to sensitive tables. SQL Server 2012, SQL Server 2014, MySQL 5.6, DB2 10.5, and Azure SQL provide robust tools for auditing access to sensitive data. In this post, we will explore how to implement auditing using Triggers and SQL Audit.
Triggers are database objects that automatically perform an action when certain events occur. They can be used to keep track of access to sensitive tables. For example, in SQL Server 2012 and 2014, you can create a trigger on a table to write an entry to an audit log whenever a user accesses the table. Here is a simple example:
CREATE TRIGGER AuditAccess ON SensitiveTable
FOR SELECT
AS
INSERT INTO AuditLog(UserName, AccessTime, TableName)
VALUES(CURRENT_USER, GETDATE(), 'SensitiveTable')
In MySQL 5.6, the syntax is slightly different but the concept is the same. Here is an example of a trigger in MySQL:
CREATE TRIGGER AuditAccess AFTER SELECT ON SensitiveTable
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(UserName, AccessTime, TableName)
VALUES(CURRENT_USER(), NOW(), 'SensitiveTable');
END;
DB2 10.5 also supports triggers for auditing. Here is an example of a DB2 trigger:
CREATE TRIGGER AuditAccess AFTER SELECT ON SensitiveTable
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO AuditLog(UserName, AccessTime, TableName)
VALUES(CURRENT USER, CURRENT TIMESTAMP, 'SensitiveTable');
END;
Azure SQL, like SQL Server, supports triggers for auditing. The syntax is the same as SQL Server:
CREATE TRIGGER AuditAccess ON SensitiveTable
FOR SELECT
AS
INSERT INTO AuditLog(UserName, AccessTime, TableName)
VALUES(CURRENT_USER, SYSDATETIME(), 'SensitiveTable')
While triggers can be useful for auditing, they have some limitations. For example, they only work on DML statements (SELECT, INSERT, UPDATE, DELETE), not on DDL statements (CREATE, ALTER, DROP). Also, they can be disabled or dropped by a user with sufficient privileges. To overcome these limitations, you can use SQL Audit.
SQL Audit is a feature of SQL Server and Azure SQL that allows you to audit both DML and DDL statements. It can also audit failed logins, changes to permissions, and other security-related events. SQL Audit logs can be written to the Windows Security log, the Windows Application log, or to a file. SQL Audit cannot be disabled or dropped by a standard user, making it more secure than triggers.
In conclusion, both triggers and SQL Audit can be used to audit access to sensitive tables. While triggers are easy to implement and work on many different database systems, they have some limitations. SQL Audit is more robust and secure, but it is only available on SQL Server and Azure SQL.