Database auditing: Tamper-Evident design

ApexSQL Audit has been designed by recognizing the principle that it is impossible to prevent tampering by trusted parties with software-only solutions. Even the worst-case scenario of an attacker obtaining trusted privileges is thus simply reduced to treating all tampering the same, no matter where it comes from. So we’ve applied this principle in all areas that affect auditing, from capturing of audited data all the way to its storage. This has been critical in ensuring not only easy compliance, but actually making tampering obvious

The attack scenarios can be split into two different sets, one attacking the source of origin of audited data, the distributed ApexSQL Audit instances themselves, and the other attacking audited data at rest in the Central Repository Database

Instance Tampering Scenarios

Attacking the source of audited data can be done by directly tampering with an ApexSQL Audit instance or with parts of audited SQL Server on which the collection of audited data relies. The main possible attacks, short of intrusion into an instance process itself, are:

1. Stopping, disabling, or deleting the ApexSQL Audit service
2. Deleting, renaming, or corrupting ApexSQL Audit binaries and/or other binaries on which the service depends
3. Blocking network access for the service thus preventing sending of audited data and reception of configuration data
4. Revoking access rights to SQL Server for a user account running the service
5. Stopping or deleting SQL Server traces created and configured by the service
6. Deleting or renaming trace files created by SQL Server traces owned by the service
7. Deleting or renaming files or directories containing processed audited data
8. Deleting or renaming local configuration of the service

It is obvious from this list that a trusted party could easily employ any of these attacks (or an almost infinite number of their variants). We can defend against some of these attacks (for example, if configuration is deleted or otherwise unavailable, the instance will automatically use the default configuration instead of just idling) but not all by a long shot. So what cannot be cured must be endured and ApexSQL Audit works hard on detecting service interruptions, whatever their nature may be, by making its internal information part of the audited data itself, equal in treatment as the actually captured SQL Server activity. This allows us to recognize gaps in functioning of ApexSQL Audit instances and thus identify times at which auditing has been unavailable for whatever reason

The internal data that we capture includes:

1. Service start times
2. Creation and closure of audited data files
3. Starting and stopping of internal service modules
4. Service heartbeats, warnings, and errors
5. Starting and finishing of trace file collections
6. Starting and finishing of loading audited data files
7. Quarantining trace files or audited data files
8. Configuration updates, failed or successful
9. Starts and stops of integrity checks

Data Tampering Scenarios

Another approach to attacks is to tamper with the audited data once it has been stored in the Central Repository Database. The main possible attacks of this type are:

1. Tampering a single valid row or a set of valid rows of audited data:
• Inserting a rogue row or a set of rogue rows consistent between them
• Changing one or more fields in a valid row or a set of valid rows
• Deleting a valid row or a set of valid rows
2. Tampering of configuration data:
• Manually adding new valid rows to change configuration and then deleting the rows once the bogus configuration is retrieved by a distributed instance that is being attacked
• Manually changing configuration and then reverting the changes once the bogus configuration is read
3. Database-wide tampering:
• Restoring the database to a previous state to eliminate unwanted data
• Tampering with database versioning to make an appearance of an orderly non-tampered state
4. Leveraging ApexSQL Audit in tampering by disabling auditing or changing its configuration and then manually destroying the evidence of changes after the attack

We consider the storage of audited data as the weakest point in detecting changes as it allows the widest attack window: if audited data itself can be tampered, then an attacker can cover the tracks at leisure and far later in time. We have therefore concentrated our efforts on making all such tampering evident and making hiding tracks of any such tampering expensive in effort, time, and computational resources. We’ve made a further design decision to enforce temper-evident storage of older versions of auditing configurations to prevent leveraging ApexSQL Audit itself from attacks

Multiple tampering-evident measures are built right into the design of the Central Repository Database:

1. Customized hashing: instead of creating a custom hashing algorithm, something notoriously hard to do correctly, ApexSQL Audit relies on SHA-2 hash with 256 bits in length (so called SHA-256) but without disclosing the actual order used to calculate the hashes. Even if the attacker reverse engineers the order of hashing, it will just make his or her job a bit easier
2. Row level hashing: hash of all known field values is calculated before being inserted, so any change to a row, without doing re-hashing, becomes easily detectable
3. Row level versioning: every row comes with a unique database-wide row version number (timestamp). Changing a row afterwards will automatically update the timestamp thus exposing out-of-order changes which would force an attacker to update not only a single row, but all rows that follow it, both in its table and all other tables
4. Rows are only inserted into the database, never updated or deleted: this allows us to do chained row hashing where the hash of each row in the chain depends on the hash and row version of the previous row in the chain. This allows for sophisticated data integrity analysis of the entire database
5. Row group hashing: hashing of groups of rows and storing them separately in a row referenced by all rows in the group. This is useful when Foreign Key (FK) relationship is present, so referenced row holds hash of hashes and versions of all rows that reference it
6. Time-timestamp mapping: ApexSQL Audit central instance constantly tracks and maps local server time and current central repository database timestamp. This allows detection of rows that have correct row-level hash but were actually tampered afterwards. The application of a special hashing algorithm makes attacks computationally extremely expensive (and this computation cannot be parallelized)

Each of these measures on its own might be easy to defeat but taken together, they increase manifold a complexity of any attack on the audited or configuration data. We will describe all these measures in another article

It is clear from the above that the design trades off performance of simple data modification operations for data tampering detectable by ApexSQL Audit. That said, we still achieve excellent performance as the most expensive operations (like configuration changes) are infrequent, whereas the insertion of audited data, which has orders of magnitude greater frequency, is done through bulk insertion features exposed by SQL Server and is thus very fast

Data Integrity Checks

The combination of anti-tampering measures gives us a wide variety of different data integrity checks which may be run per group of rows, per table, or per entire database. These checks include:

1. Analyzing hashes of some rows, all rows of a table, or rows of all tables
2. Analyzing row chain hashes for a single chain, all chains in a table, or all chains in all tables
3. Analyzing row group hashes for a single row group, all groups in a table, or all groups in all tables
4. Analyzing row versions for logical inconsistencies between rows of the same table, rows of related tables, and time-timestamp mapping
5. Analyzing gaps in service functioning both on the central instance and on any distributed instances
6. Analyzing internal data for possible attacks of a different nature like deletion of trace file data and similar

ApexSQL Audit in its current version doesn’t employ all of these integrity checks but again it is more important for us to actually be collecting the data that we know is useful for integrity checks. Adding integrity checks, some of which we will be describing in detail in the future articles, is then far easier than adding them in a data void

Tamper-Safe Design?

So does ApexSQL Audit have a completely tamper-safe design? No, it doesn’t and indeed it cannot have a tamper-safe design with a software-only solution. At the beginning of this article it was mentioned that we recognize this principle and that we have applied it in the entire design of ApexSQL Audit. Instead of playing a losing game in that way, we try to play a different game, raising the stakes by making attacks much more difficult to be carried out undetected. It still doesn’t guarantee anything, but it is our opinion that it’s the best that can be done