SQL Auditing. Guilty as charged! Or not, as the case may be.

This is a finger pointing situation that i've witnessed in the past regarding native SQL Auditing, and the potential for edge case false positives. Something really not helpful when it comes to any security related topic.

This post is just to highlight a potential gotcha with the native SQL Auditing functionality, dependent on it's configuration. It's certainly not a best practice on setting up Auditing, or access controls, or even the intent someone may have in falling foul of any Audit. There are many awesome guides out there on how to do exactly that.

Let's go.

So, assume a confidential table and data, such as the below, living in a DB called AuditedDB.

USE AuditedDB
GO

CREATE TABLE SecureData
(ID Int IDENTITY (1,1),
  Name varchar(20),
  BankAccountNumber int,
  BankSortCode varchar(10),
  Balance  decimal (16,2)
)

INSERT INTO SecureData (Name, BankAccountNumber, BankSortCode, Balance)
 VALUES ('Bill Gates', 12345678, '99-22-11', 23423532.45)

INSERT INTO SecureData (Name, BankAccountNumber, BankSortCode, Balance)
 VALUES ('Elon Musk', 98324576, '88-32-74', 987556766777.45)

INSERT INTO SecureData (Name, BankAccountNumber, BankSortCode, Balance)
 VALUES ('Jeff Bezos', 56423435, '78-38-37', 567836778777.99)

INSERT INTO SecureData (Name, BankAccountNumber, BankSortCode, Balance)
 VALUES ('Rod', 09433621, '12-34-56', 0.01)

SQL Auditing

And you have a requirement to audit DELETEs against this table, so you decide to use native SQL Auditing. So you set up your Server Audit, and your Database Audit Specification in your user DB.

All good.

USE [master]
GO

CREATE SERVER AUDIT [AuditDelete]
TO FILE 
(FILEPATH = N'D:\AuditData'
	,MAXSIZE = 1000 MB
	,MAX_FILES = 5
	,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
ALTER SERVER AUDIT [AuditDelete] WITH (STATE = ON)
GO

USE [AuditedDB]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditDeleteSpecification]
FOR SERVER AUDIT [AuditDelete]
ADD (DELETE ON OBJECT::[dbo].[SecureData] BY [public])
WITH (STATE = ON)
GO

Naughty Wicked Malicious User.

Assume someone with a grudge, who had an Amazon package damaged by the driver just hurling it at their door and driving off (heaven forbid that would ever happen...oh no, never, not at all....). Rightly or wrongly, this user has write access to this table... and decides to run...

DELETE FROM SecureData WHERE NAME='Jeff Bezos'

Execute. Bezos upset.

Looking at our data, the record has gone.

The Evidence

This is where our Auditing comes in.... let us read the Audit Log.

DECLARE @Auditlog NVARCHAR(4000)
DECLARE @Audit NVARCHAR(1000) = 'AuditDelete';

SELECT @Auditlog  =CONCAT(log_file_path, name,'*') FROM sys.server_file_audits
WHERE name = @Audit AND is_state_enabled =1 
ORDER BY audit_id DESC

                                        
SELECT  aa.name ,aa.class_desc ,al.* 
	FROM    sys.fn_get_audit_file(@Auditlog, DEFAULT, DEFAULT) al
        LEFT JOIN sys.dm_audit_actions aa ON aa.action_id =al.action_id 
		AND aa.action_in_log =1
              ORDER BY event_time DESC;
GO

This shows our Audit data exactly as expected. Boom, the user has been caught, dragged straight to the judge, guilty as charged, stood against the wall at dawn...etc.

So what is the problem?....In step someone doing their job.

Great, then say a DBA wants to check the query plans for something. So they gather an Estimated Execution Plan (SSMS - Ctrl L, SET SHOWPLAN_XML ON etc) for the below (or anything else that performs a delete against the table for that matter)

DELETE FROM SecureData WHERE NAME='Elon Musk'

Data is still there as expected as SQL has obviously not executed the DELETE statement.

Falsehood!

But look at our Audit data... according to the log, the data HAS been deleted, we now have a row for it.

AND to further twist the knife, it also reports as succeeded as well.

The security judge is getting restless, they're looking to smack someone with their gavel again... Poor old DBA is clearly innocent here, as they haven't deleted anything, but the evidence is suggesting that they have.

There's an affected_rows column, does that help?

Alas, only for Azure SQL DB only.... for both real and Estimated plan deletes on box sql you get....

No Alibi

In fact, there appears to be nothing in the audit file itself when you are just capturing Deletes.

So what can we do?

SHOWPLAN is covered under the DATABASE_OPERATION_GROUP Database Audit specification group... if we recreated our Database audit specification to add that?

So our Database Audit Specification now looks like:

USE [AuditedDB]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditDeleteSpecification]
FOR SERVER AUDIT [AuditDelete]
ADD (DATABASE_OPERATION_GROUP),
ADD (DELETE ON OBJECT::[dbo].[SecureData] BY [public])
WITH (STATE = ON)
GO

Great! Its captured under the same transaction_id, so we can at least see that the estimated plan was captured

What about an actual plan then?

Very little it seems...we're still potentially going in front of the Judge.

The Actual plan is logged via Audit exactly the same (no differentiating columns in the audit output file to highlight a actual vs and estimated plan?), so this isn't completely helpful either, as this would be an actual delete we want to know about.

Clearly with the Estimated Plan, the data would still be there, so although capturing the SHOW PLAN gives us something, but its not buttoned down exactly how we may like, and still leaves an element of ambiguity. If your Audits go directly to management/Security/any other higher power, someone could be dragged infront of the judge for an explaination on this entry and why you are just doing your job.

ie... a DBA just getting an estimated plan for a statement/proc that contains a delete against that table? It means that it will show up as if you have run the delete itself...so potential explainations required. Take care!

Answers on a Postcard.

OK, by this point, i'd jump over to auditing these additional elements using another method, probably XEvents in order to get a complete picture. But it got me thinking... is it possible to capture the differences between Estimated and Actual SHOWPLANs using native SQLAudit?

If you know a cool method, or workaround, feel free to comment , i'd love to learn it.

Thanks for reading

Judge Rod Rinder*

Image by pikisuperstar on Freepik

*For legal and moral reasons, most definitely not a judge.