Applies to
Summary
This article shows how to construct and use advanced SQL audit filtering.
Description
ApexSQL Audit is a SQL Server auditing and compliance tool that audits multiple SQL Server instances and is capable to collect over 200 SQL Server events and stores the SQL audited data into a tamper-evident central repository database. The type of event can be selected for monitoring in a general manner, using simple SQL audit filters, or can be set to a high precision all the way down to a specific text line in a query, using advanced filtering. Captured information is available through a range of built-in, predefined reports or via custom report ability also designed to meet specific granular user requirements.
Simple filters
The standard audit filter in ApexSQL Audit is based on the so-called Simple audit filter, which allows fast and easy selection of events that will be audited for each audited SQL Server instance separately. Even though it is called “simple”, it still allows a high level of granularity.
Here is an example of a server-level filter:
And here is an example of a database-level filter:
Besides allowing selection of each single auditing event, it also allows including/excluding specific logins, applications and/or object from the process of auditing. As such, this filter can fulfill all standard SQL auditing demands.
Advanced filters
The advanced filter allows the highest level of granularity in creating auditing filter rules and thus allowing precise auditing and collecting exactly the required data. This allows the application to minimize the negative effects that auditing process imposes on SQL Server where the repository database is hosted, but also to maximize resources available to other parts of the system by reducing event collection and network/storage overhead.
The Advanced filter relies on an intuitive set of operators that can be chosen when creating a filter condition for SQL auditing. The data fields selected in the filter determine the operators that will be offered for filtering. The following shows which operators are available for the different types of data fields:
- is, is not, contains, and does not contain – Application name, Client host, Database name, Login name, Object name, Schema name, Text date
- Is and is not – Database operation, Object type, Server, Server operation
The image below lists all data fields that are available in the advanced filter:
There is no limit to the number of filtering criteria used to achieve the desired precision of auditing results. The filtering criteria used will form the auditing rule that will determine within which SQL Server instances/databases SQL Server events will be audited and when collected stored in the central repository database.
Additionally, the SQL audit advanced filters can use criteria grouping. Grouped criteria will operate as a single unit related to the parent condition to additionally determine parent query criteria or to make this more understandable, grouping is similar to using parentheses around a mathematical calculation or logical expression to alter the sequence of evaluation.
The above filtering rule can be presented as a logical expression:
Server is RECHIE-PC\RECHIE AND Database name is AdventureWorks2014 AND Database operation is DDL, Disable trigger, Enable trigger, Rename object, Revert, Update statistics
AND (Application name is not Microsoft SQL Server Management Studio AND Client host is not MAKINA
AND (Text data does not contain “START POPULATION“
OR Text data does not contain “SET_TRACKING”))
The condition indentation should be treated as the open parenthesis in front of that condition while conditions that are grouped are conditions enclosed between the parentheses.
As is visible from the above example, the AND or OR operators in front of the group apply to the whole group implying that those grouped conditions will be evaluated as a single unit within the parent query.
The following examples will demonstrate how the auditing filter can allow meeting precise SQL auditing requirements, using conditional filtering:
-
A defined set of requirements on what events have to be collected is established. For specified users there are specific requirements to audit security events related to login/user changes:
In the particular example, the filter is set to audit the databases specified in the Database name is condition when DDL or DML events specified in the Database operation is condition are fulfilled but also only one user, Rechie will be additionally audited when they perform any server or database operation specified in the Server operation is/Database operation is filter conditions. The logical expression would be:
Database name is AdventureWorks2014 AND Database operation is DDL, DML
OR (Login name is ReChie-PC\Rechie
AND (Server operation is Alter login, Create login, Drop login
OR Database operation is Alter user, Create user, Drop user)) -
In the next case, only specific users are to be excluded from auditing but only for some specific auditing events and to be excluded completely from auditing when working with a specific database:
Here, the auditing filter is set to fully audit databases specified in the Where Database name is condition for any server-level security operations or DDL, DML, Security database-level operations except for login name PROD\MRancic, as they will be excluded from auditing (defined by the rule created via the sub-group).
For those who prefer to visualize this auditing condition via the logical expression:
Database name is AdventureWorks2014 AND Login name is not PROD\MRancic AND (Database operation is DDL, Security,DML OR Server operation is Security)
OR (Database name is not PhoneBook AND Login name is not PROD\MRancic) -
Besides the ability to utilize the filter condition related to the SQL Server or database level conditions, there is also a Text data filtering condition. It allows you to set an auditing filter that will check the T-SQL query text of the executed events to find the matching string and to reject or to include that event according to the condition set. The Text data filter allows using Is, is not, contain, do not contain condition.
If we want to audit SELECT statements for example, but we don’t want to audit when counting different values in columns using the SELECT DISTINCT clause. Here is the example:
SELECT DISTINCT JobTitle FROM HumanResources.Employee
If we don’t want to collect SELECT DISTINCT when it is executed on the specific column (JobTitle column in this particular case), we can exclude that from auditing via Text data filter condition:
The defined auditing filter will audit all SELECT operations when executed against AdwentureWoorks2014 database HumanResources.Employee table but will exclude from auditing every event where T-SQL contain words “DISTINCT” or “JobTitle”. In a similar way, it is possible to set a rule that can fulfill very complex/precise auditing requirements. The Text data filter should be used when the highest level of auditing precision is required.
Besides being able to ensure more precise auditing, it can be additionally used for applying the same filtering conditions to specific groups of SQL Server instances. To achieve this, users can simply export the auditing configuration of a specific server and import it directly to other audited SQL Server instances:
Practically, there is no limitation in the way the advanced filter can be used and it can be determined by users to define what granularity/precision level they want to achieve and how they want to achieve that.
Finally, but not less important is that ApexSQL Audit alerting system utilize the same advanced filter technology which allows equally high precision of alerting that can fully match and complement the auditing precision and thus ensuring alerts to be raised exactly and only for the precisely specified events and under the strict condition that users can define.
ApexSQL Audit utilizes a highly developed advanced filtering system that breaks all boundaries on conventional filtering empowering users to fully customize SQL audits and alerts precisely to their specifications.
