Forensic Tamper Detection in SQL Server

An article by Amit Basu

Executive Summary

This document presents a method of tamper detection of sensitive data. SQL Server provides many mechanisms to authenticate and authorize users to perform certain activities in the database. These mechanisms ensure that other than those with proper authentication, no one else can view or modify data. However, this still doesn't protect the system when these authorized users tamper with data - modify or delete them in any way. The suggested method illustrates a way how such tampering by an authorized user can be detected. While this method doesn't provide tamper-prevention measures, but as there is no such thing as ultimate security, detection of such tampers will help maintaining the integrity of information in a great way.

Problem Statement

Consider the scenario where you have an Audit Trail database. All the activities from your application get logged onto that database along with the name of the application user who has performed that operation. This is a standard approach for any application supporting government regulated Compliance. Typically, this Audit Trail database is never updated and always have rows serially updated. Different mechanisms exist to insert record to the Audit Trail database like direct inserts from Applications (perhaps through a data access layer), through database triggers etc. Once the data is inserted, the data is supposed to remain intact should there come any forensic requirement in future. This database, if separate from the application database, or at least the set of tables which comprise of Audit Trail information, are usually protected by authentication and authorization schemes provided by SQL Server.

Every application will have at least one SQL Server login which can have write-access to this data - right? This is the user who will be impersonated at the required time to write to the Audit information. The question is now, what if I know the credentials of that user like password and login, and then use this credential to get into the system, delete or modify a particular row from the Audit information because I don’t want anyone to see that I have actually done something wrong in the main application, intentionally or by mistake? You may ask that how in the first place I will know those credentials. Well, I am the administrator of that application and I myself have messed up something which I want to cover up now. See - I told you that there is no such thing as ultimate security!

Therefore, we need some tool with the help of which we can at least find out, from time to time, that whether the data that exists, is it really clean and un-tampered? Or, the data that we see - is that all? Or was there some other data that have disappeared - has someone deleted one or more rows?

Solution Features

One of the solutions (please note there could be other solutions also) that I am presenting here can boast of the following features: it can detect modifications in any row of a table

it doesn't provide encryption to the original data itself, thereby ensuring that there is no baggage of burden that usually comes with encryption

this solution can be applied selectively to one or more tables - doesn't not required to be applied for the entire database. For example, the tamper-detection logic may be applied to the Audit Trail database alone and not the application database

It can detect tampering in a range of date - such as, was there in tampering between such and such date? Or on a particular date? Or in a given week? This is particularly useful for Audit Trail databases which tend to grow very fast pretty quickly.

it doesn't use any special non-SQL server tool, languages - everything is available out of the box (i.e. SQL Server itself)

because it's a simple set of SQL statements and procedures, it doesn't require any special deployment strategy

Like any good thing in life - the solution also comes with some disadvantages! They are:

While it can detect all row modifications, it can detect first occurrence of delete operation in a table and can detect if the last row has been deleted. If there are row deletes anywhere in between, it cannot detect - the reason for this behaviour comes from the algorithm that has been employed. With little more tricky algorithm, this may also be overcome - I'll leave that to the use as a nice brain-storming session

Nothing more - I hate to think of short-comings of my own ideas!

The Solution

We will go by our example of Audit Trail database. This is a database having two independent tables - AuditLog and AuditUser. These two tables, in reality, can reside in the same application database also. The objective will be to protect AuditLog table so that any tampering in that can be detected.

For this purpose, we will have two special columns called HReserved and VReserved (they should have been RowHash and ColHash ideally, but ain't we love to underestimate the thieves by trying to fool them?). The algorithm involving these two columns are:

Whenever there is an insert operation in the AuditLog table, we need to calculate two hash values - a horizontal or row hash, and a vertical or a column hash.

The row or horizontal hash is stored in the HReserved column and contains a hash value of all the columns in the row except HReserved and VReserved column values. Any change or a modification in any given row will result in a mismatch of the hash value and therefore can be detected.

The column or vertical hash is stored in the VReserved column and contains a hash value based on the HReserved values of the last two rows as well as the current row. This interwoven hashing mechanism will ensure that if one particular row is deleted from the AuditLog table, the detection algorithm can find a mismatch by the existence of other two rows immediately preceding the deleted row and can thus cry foul!

Pictorially, the algorithm looks like this: Pictorial presentation of the algorithm

That's all there to it! Yup - that simple but effective!

Now that we have a basic idea of how we are going to do this, let's now do it step by step. Here we go:

Create the tables

Run the following script to create the two tables:

CREATE TABLE AuditLog
(
 AuditId BIGINT IDENTITY (1, 1) NOT NULL,
 AuditSummary NVARCHAR (3000) NOT NULL,
 AuditUserId NVARCHAR(200) NOT NULL,
 Details XML NULL,
 RecordTimeStamp DATETIME NOT NULL,
 HReserved INT NULL,
 VReserved BIGINT NULL
 CONSTRAINT PK_AuditLog PRIMARY KEY CLUSTERED
 (AuditId DESC)
)

CREATE TABLE AuditUser (
 AuditUserId INT IDENTITY (1, 1) NOT NULL,
 AuditUser NVARCHAR (256) NOT NULL,
 RecordTimeStamp DATETIME NOT NULL
 CONSTRAINT PK_AuditUser PRIMARY KEY CLUSTERED
 ( AuditUserId DESC )
)

The first table contains all Audit logging information like a textual summary, audit log details in XML format so that later on queries can be made on that.

The second table, AuditUser exists to provide normalization in the AuditLog table. While the columns AuditSummary and Details log the actual operation within the application, AuditUserId is used to keep track of who made the operation. The columns HReserved and VReserved have already been explained. Please note that the primary key of the table, AuditId has a descending index on it to ensure a faster way for the VReserved calculation.

Adding data to the table

Because we need to calculate horizontal and vertical row hashes, insert operations in the AuditLog table is preferably done through a stored procedure. Before it actually insert the new row, the stored procedure carries out three tasks:

Detects if the user is already in the AuditUser table or not - if she doesn't exist, add an entry for her. It retrieves the AuditUserId for insertion in the AuditLog table.

Calculates horizontal hash (HReserved) for the current row.

Calculates vertical hash (VReserved) for the current row.

CREATE PROCEDURE upAddAudit
	(	@AuditUser		NVARCHAR(256),
		@AuditSummary	NVARCHAR(3000),
		@Details			XML = NULL
	)
AS
BEGIN
		SET NOCOUNT ON
		BEGIN TRANSACTION

		IF (@AuditUser IS NULL) OR @AuditUser = ''
			SET @AuditUser = 'Anonymous'

			--does this user already exist?
			DECLARE @UserId	INT
			SELECT 	@UserId = AuditUserId
			FROM 	AuditUser
			WHERE	AuditUser = @AuditUser

			-- if he is a new user, insert her record
			IF @UserId IS NULL
			BEGIN
				INSERT INTO AuditUser (AuditUser, RecordTimeStamp)
				VALUES ( @AuditUser, GETUTCDATE() )
				SET @UserId = SCOPE_IDENTITY()
			END

		IF @@ERROR <> 0 GOTO ErrorExit


		--the user is found, so first insert the Audit record
		INSERT INTO AuditLog (
			AuditUserId,
			AuditSummary,
			Details,
			RecordTimeStamp
			)
		VALUES (
			@UserId,
			@AuditSummary,
			@Details,
			GETUTCDATE()
			)

		IF @@ERROR <> 0 GOTO ErrorExit

		-- now is the time to make this record tamper-proof
		DECLARE @CurrentId 	INT
		DECLARE @HReserved	INT
		DECLARE @VReserved	BIGINT

		SET	@CurrentId = SCOPE_IDENTITY()

		-- calculate the row hash
		SELECT	@HReserved = BINARY_CHECKSUM(
		AuditId,
				RecordTimeStamp,
				AuditUserId,
				AuditSummary,
				Details
				)
		FROM 	AuditLog
		WHERE	AuditId = @CurrentId

		-- Calculate column hash
		SELECT	TOP 2
			@VReserved = CHECKSUM_AGG( ISNULL(HReserved,0))
		FROM	AuditLog
		WHERE	AuditId < @CurrentId

		SET 	@VReserved = ISNULL(@VReserved, 0)

		--update the row with the calculated values
		UPDATE	AuditLog
		SET	VReserved = @VReserved + @HReserved,
			HReserved = @HReserved
		WHERE	AuditId = @CurrentId

		IF @@ERROR <> 0	GOTO ErrorExit

		--commit data if all the transactions above are successful
		COMMIT TRANSACTION

		--return the current AuditId in case it is required
		RETURN @CurrentId

	    --handle errors
		ErrorExit:
		ROLLBACK TRANSACTION
		RETURN -1
END

Let&single;s add some data now, which we can test later.

EXEC upAddAudit
    'Brishti',
    'Added new customer',
    '13'

EXEC upAddAudit
    'Amit',
	'Added new customer',
	'14'

EXEC upAddAudit
    'Brishti',
    'Added new customer',
    '15'

EXEC upAddAudit
    'Brishti',
    'Added new product',
    '59'

EXEC upAddAudit
    'Amit',
    'Modified product details',
    '81'

After adding data through the stored procedure, this two tables will have the following data:

Detect Tampers

Now that we have some data in place, let's try tampering them and then we will see how the detection works.

Let's modify one row and delete one row from the AuditLog table.

UPDATE	AuditLog
SET AuditUserId = 3 WHERE AuditId = 4

DELETE	AuditLog WHERE AuditId = 2

We need a stored procedure now for the purpose of detection:

CREATE PROCEDURE upCheckAudit
		(@StartDate	DATETIME = NULL,
	 	@EndDate	DATETIME = NULL
		)
AS
BEGIN
		SET NOCOUNT ON

		DECLARE @Result	TABLE (AuditId INT, TamperType VARCHAR(50))
		DECLARE @AuditId 	INT
		DECLARE @HReserved	INT
		DECLARE @VReserved 	BIGINT
		DECLARE @Temp		BIGINT

		-- if start date is passed as NULL, set to one week back
		IF @StartDate IS NULL
			SET @StartDate = dbo.ufStartDateTime
				( DATEADD(ww, -1, GETUTCDATE()))

		--and also make sure start date has time stamp from 00:00:00
		SET @StartDate = dbo.ufStartDateTime ( @StartDate)

		--if end date is passed as null, set it to current date
		IF @EndDate IS NULL   -- set to current date
			SET @EndDate = GETUTCDATE()

		--and make sure end date has time stamp upto 23:59:59
		SET @EndDate = dbo.ufEndDateTime ( @EndDate)

		--find all modified rows in the specified date range and
		--put them into temporary table
		INSERT INTO @Result (
					AuditId,
					TamperType
					)
		(SELECT		AuditId,
					'Modified'
		FROM	AuditLog
		WHERE	HReserved <> BINARY_CHECKSUM(
			AuditId,
			RecordTimeStamp,
			AuditUserId,
		AuditSummary,
			Details
			)
			AND
			DATEADD( s, DATEDIFF(s, GETUTCDATE(), GETDATE()  ),					RecordTimeStamp )
				BETWEEN @StartDate AND @EndDate
			)

		--find all deleted rows in the specified date range and
		--put them into temporary table

		DECLARE	cRow CURSOR LOCAL FORWARD_ONLY KEYSET READ_ONLY
		FOR	SELECT	AuditId, HReserved, VReserved
		FROM 	AuditLog
		WHERE 	DATEADD( s, DATEDIFF(s, GETUTCDATE(), GETDATE()  ), 				RecordTimeStamp )
		BETWEEN @StartDate AND @EndDate
		ORDER BY AuditId ASC

		OPEN cRow

		FETCH NEXT FROM cRow INTO @AuditId, @HReserved, @VReserved
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT	TOP 2
				@Temp = CHECKSUM_AGG( ISNULL(HReserved,0))
			FROM	AuditLog
			WHERE	AuditId < @AuditId

			SET @Temp = ISNULL(@Temp, 0)
			SET @Temp = @Temp + @HReserved

			IF @Temp <> @VReserved
			BEGIN
				INSERT INTO @Result (
						AuditId,
						TamperType
						)
				(SELECT	@AuditId - 1,
						'Deleted' AS 'TamperType' )
				BREAK
			END

			FETCH NEXT FROM cRow INTO @AuditId, @HReserved, @VReserved
		END
		CLOSE cRow
		DEALLOCATE cRow

		--check for the last row deletion
		IF NOT EXISTS
			(SELECT	*
			FROM 		AuditLog
			WHERE AuditId = ident_current('AuditLog'))
			INSERT INTO @Result (AuditId, TamperType)
			VALUES (ident_current('AuditLog'), 'Deleted')

		--we have all tamper-detections done - spit out the result!!!
		SELECT * FROM @Result
END

Wait! Don't yet - execute it now. You may have noticed that there are two user-defined functions used in the procedure above. Apart from formatting the dates, these two functions correctly modify the time-component of the dates to 00:00:00 and 23:59:59 depending on if the passed parameter is a starting day or ending day. Here they are

CREATE FUNCTION ufStartDateTime
		(@StartDate DATETIME)
RETURNS DATETIME
AS
BEGIN
		RETURN	CAST(YEAR( @StartDate ) AS VARCHAR(4))  + '-' +
			CAST(MONTH( @StartDate ) AS VARCHAR(2)) + '-' +
			CAST(DAY( @StartDate ) AS VARCHAR(2))   + ' 00:00:00'

END
GO

CREATE FUNCTION ufEndDateTime
		(@EndDate DATETIME)
RETURNS DATETIME
AS
BEGIN
		RETURN	CAST(YEAR( @EndDate ) AS VARCHAR(4))  + '-' +
			CAST(MONTH( @EndDate ) AS VARCHAR(2)) + '-' +
			CAST(DAY( @EndDate ) AS VARCHAR(2))   + ' 23:59:59.99'
END
GO

The upCheckAudit procedure accepts default parameters - starting day and ending day of the time period we want to track. In case they are not passed, they default to the last seven days from the current date.

Let's see now how it responds when we tell it to discover the tampers (we have already tampered the data, in case you have forgotton!)

EXEC upCheckAudit

And voila:

Summary

In this article, we have seen how with some help of the T-SQL features, we can implement a forensic tamper detection in SQL Server. While prevention of tampering can be implemented to some extent using the various security features of SQL Server, the method described will help any application to leverage its security model thus benefiting the end-user.

About the Author: Amit Basu has an extensive experience on Microsoft based technologies. Apart from his current work as Project Manager with Gamcom Solutions Ltd. UK, he is busy with writing a book on Chess Programming. He loves photography, long drives, and music. He also programs poetry, in his own words!