Free SQL Help for Devs, DBAs, and the Curious

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.

Check out the latest articles from all our sites:

• How to Take Advantage of Flash Sales at Grocery Stores [https://www.ethrift.net]
• A brief history of the Galveston Hurricane of 1900 [https://www.galvestonbeachy.com]
• How to Plant and Maintain Chokeberry Bushes [https://www.gardenhomes.org]
• New Query Store Enhancements in SQL Server 2022 [https://www.sqlsupport.org]
• Heat: Why My Laptop Is Cooking My Lap [https://www.SupportMyPC.com]
• The Best Months to Visit South Korea for Cherry Blossoms and Fall Colors [https://www.treasureholidays.com]