Change Auditor for SQL Server 7.1.1

SQL Data Level Auditing page

The SQL Data Level Auditing page is displayed when SQL Data Level is selected from the Auditing task list in the navigation pane of the Administration Tasks tab. From this page you can launch the SQL Data Level Auditing wizard to specify the SQL instances and the operations to audit. You can also edit existing templates and remove templates that are no longer being used.


	NOTE: Authorization to use the administration tasks on the Administrations Tasks tab is defined using the Application User Interface page under the Configuration task list. If you are denied access to the tasks on this page, see the Change Auditor User Guide for more information on how to gain access.

The SQL Data Level Auditing page contains an expandable view of all the SQL Data Level Auditing templates that have been defined. To add a new template, click the Add tool bar button. Once added, the following information is provided for each template:

Template

Displays the name assigned to the template when it was created.

Status

Indicates whether the auditing template is enabled or disabled.

Database

Displays the target database.

Operations

Displays the events selected for auditing on the Events tab of the wizard. Hover your mouse over this cell to view all of the events included in the template.

Filters

Displays the column filters applied to a template.

Sensitive Columns

Displays the columns that have been selected in the Sensitive column data option in the template wizard. Due to the nature of this data, it will display as “***” in Event Details pane and no actual values will be stored in the database.


	NOTE: The cells directly under the main heading rows are used for filtering data. That is, as you enter characters into these cells, the client will redisplay the templates that meet the search criteria (i.e., comparison operator and characters entered). For more details about using the data filtering function provided throughout the Change Auditor client, see the Change Auditor User Guide.

SQL Data Level Auditing templates

To enable SQL Data Level auditing in Change Auditor, you must create a SQL Data Level auditing template which specifies the SQL server, Instance, and the database to audit. Change Auditor agents must be installed on SQL servers/SQL cluster nodes before configuring their templates.

NOTE: For SQL clusters a template must be configured for each database/node combination. For example, a single database in a two node cluster would require two templates. One for Node1 and another for Node2.

NOTE: When multiple enabled SQL Data Level templates are auditing the same database, each template is analyzed separately and an event only needs to match one template to enter the event in the Change Auditor database, even if a second template specifically excludes it with, for example, a NOTLIKE filter. Sensitive column configuration values from the enabled templates are then merged and applied to incoming events.

For example:

•

Template1 is enabled and configured with sensitive columns PERSON.NAME, PERSON.PAYGRADE.

•

Template2 is enabled and configured with sensitive column PERSON.ADDRESS.

•

Template3 is disabled and configured with sensitive column PERSON.DEPARTMENT.

For a new event, data in the PERSON.NAME, PERSON.PAYGRADE and PERSON.ADDRESS fields will display as "***".

To create a new SQL Data Level auditing template:

Open the Administration Tasks tab.

Click Auditing.

Select SQL Data level (under the Applications heading in the Auditing task list) to open the SQL Data Level Auditing page.

Click Add to open the SQL Data Level auditing wizard which will step you through the process of creating a template.

Enter a name for the template and select the SQL instance to be audited.

•

Select the SQL server to be audited.

•

Select the Default option to audit the default instance.

•

Select the Named option to audit a named instance.

•

Select the target database to be audited.

The logged in account is used to attempt to populate the available databases and their data. If the logged in account does not have the proper access rights, SQL Server authentication credentials are required.

•

Select the agent server to perform the auditing. If a SQL Server cluster is specified in the Server field, you can pick the agent/node by clicking the browse button.

•

Enter the credentials required for the agent to access the SQL sever. Click Test credentials to ensure the specified database can be opened on the target server.


	NOTE: If the target SQL Server's domain does not trust the Change Auditor client's domain, the test for Windows Authentication may fail even though the agent credentials are valid.

On the second page of the wizard, select the operations (event classes) that are to be audited. At least one event must be selected.

Select an entry from the list box at the top of the page, and select Add to add individual events.

On the third page of the wizard, optionally define column filters to capture only a subset of transactions.

•

Select/highlight an entry from the data grid.

•

In the Filter where fields, enter the operator and value to be used in the filter. In the first field (left) use the drop-down menu to select the operator (In, Not in, Like or Not Like; =, !=). The operators listed are based on the entry selected in the Filters list above. In the second field (right) enter the value or string to be used in the filter.

NOTE: The following wildcard characters can be used in LIKE expressions for non-exact string matches:

•

Use a percent sign (%) to match zero or more characters.

•

Use an underscore (_) to match a single character.

NOTE: Commas (,) can be used to separate individual values when the ‘in’ and ‘not in’ operators are is selected.


	NOTE: When setting filters, if there is need to remove multiple objects of the same “type” (for example, TableName) using the != (not equal to) operator, only use the “AND” joiner for the different lines. If “OR” is used in the case, the resulting filter statement will always evaluate to TRUE, and forward the event.

•

Click Add to add it to the Filter list at the bottom of the page.


	NOTE: To add multiple filters, select the column filter row after which the new filter is to be added, and then specify whether all criteria must be met or only some of the criteria. If Join filters with AND is selected, all filters specified must be satisfied before an event can be audited. If Join filters with OR is selected, only one of the specified filters needs to be satisfied.

On the next page of the wizard, you can specify the columns within a table that are deemed to potentially include sensitive information. Select Refresh Columns to update the data. Once these columns are identified, their data will not be recorded in the database and will display in the Event Details pane as “***” to maintain privacy.

Clicking Finish creates the template, close the wizard, and return to the SQL DL Auditing page, where the newly created template will now be listed.

Select the Change Auditor agent from the Configuration page and click Refresh Configuration.


	NOTE: If you do not refresh the agent’s configuration, the agent will automatically check for a new agent configuration based on the polling interval setting (located on the System Settings tab of the Configuration Setup dialog). The default is every 15 minutes.

To modify a template:

On the SQL Data Level Auditing page, select the template to be modified and click Edit.

This will display the Data Level Auditing wizard, where you can modify the SQL server, instance, database, assigned agent, events and/or filters included in the template. For example:

•

On the first page of wizard, you can change the name of the template, change the SQL server, instance, or database.

•

On the second page of the wizard, you can add or remove SQL events from the selected auditing template.

•

On the third page of the wizard, you can add, modify or remove column filters from the selected auditing template.

•

On the fourth page of the wizard, you can add, modify, or remove the sensitive columns.

Click Finish to save the changes.

To disable an auditing template:

The disable feature allows you to temporarily stop auditing the specified SQL instance without having to remove the auditing template or individual SQL instance from a template.

On the SQL Data Level Auditing page, use one of the following methods to disable an auditing template:

•

Place your cursor in the Status cell for the template to be disabled, click the arrow control and select Disabled.

•

Right-click the template to be disabled and select Disable.

The entry in the Status column for the template will change to ‘Disabled’.

To re-enable the auditing template, use the Enable option in either the Status cell or right-click menu.

To delete an auditing template:

On the SQL Data Level Auditing page, select the template to be deleted and click Delete | Delete Template.

A dialog will be displayed confirming that you want to delete the selected template. Click Yes.

SQL Data Level Auditing wizard

The SQL Data Level Auditing wizard opens when you click Add or Edit on the SQL Data Level Auditing page. This wizard steps you through the process of creating a template, identifying the SQL server, instances, and database to included in the template. You can also use this wizard to modify a previously defined template.

The following table provides a description of the fields and controls in the SQL DL auditing wizard.


	NOTE: A red flashing icon indicates that you have not yet entered the required information. Hovering your cursor over this icon displays a tool tip explaining what needs to be entered.

Table 3. SQL Auditing wizard

Create or modify a SQL Data Level Auditing Template page: On the first page of the wizard, enter a name for the template and select the SQL instance to audit.

Template Name

Enter a descriptive name for the template being created.

Server

Select the SQL server to audit. If the server is a cluster, select the cluster name, not an individual node.

SQL Instance

Select one of the following options:

•

Default - This option is selected by default and will use the default SQL instance (MSSQLSERVER) found on an agent that is using the SQL Server Auditing template.

•

Named - Select this option to use a named instance instead of the default SQL instance. When this option is selected, the name field will be activated allowing you to enter a SQL named instance.

Database

Select the target database to audit. The logged in account is used to attempt to populate the available databases and their data.If the logged in account does not have the proper access rights, SQL Server authentication credentials are required.

Agent Server

Select the agent server to perform the auditing.

NOTE: If a SQL Server cluster is specified in the Server field, you can pick the agent/node by clicking the browse button.

Agent Credentials

Enter the credentials required for the agent to access the SQL sever. Click Test Credentials to ensure the specified database can be opened on the target server.

NOTE: If the target SQL Server's domain does not trust the Change Auditor client's domain, the test for Windows Authentication may fail even though the agent credentials are valid.

Select the operations to audit page: From this page, select the SQL Data Level operations (event classes) to audit on the selected SQL instance. You must select at least one operation.

Event Classes

The data grid across the top of the page displays all of the SQL event classes available for auditing. Select/highlight an event class and use the appropriate add option to add either the individual event class or all events in the selected facility.

This grid displays the following information for each event class:

•

Event Class - the events available for auditing

•

Severity - the current severity level assigned to each event

•

Status - indicates whether the event is currently enabled or disabled

Add event

Select the operations (event classes) that are to be audited. At least one event must be selected.

Remove

Use to remove the selected entry from the Audit list box.

Select auditing filters page: Using the filtering page you can optionally define criteria to limit the data retrieved. These filters allow you to capture only the required information in high traffic databases.

Filters

The data grid across the top of the page displays the SQL columns available for filtering. Select/highlight an entry and then use the Filter where fields to define the operator and values to be used in the filter.

Filter where

In the first field (left) use the drop-down menu to select the operator (In, Not in, Like or Not Like; =, !=). The operators listed are based on the entry selected in the Filters list above. In the second field (right) enter the value or string to be used in the filter

In the second field (right) enter the value or string to be used in the filter.

NOTE: The following wildcard characters can be used in LIKE expressions for non-exact string matches:

•

Use a percent sign (%) to match zero or more characters.

•

Use an underscore (_) to match a single character.

NOTE: Commas (,) can be used to separate individual values when the ‘in’ and ‘not in’ operators are is selected.

NOTE: To add multiple filters, select the column filter row after which the new filter is to be added, and then specify whether all criteria must be met or only some of the criteria.

NOTE: When setting filters, if there is need to remove multiple objects of the same “type” (for example, TableName) using the != (not equal to) operator, only use the “AND” joiner for the different lines. If “OR” is used in the case, the resulting filter statement will always evaluate to TRUE, and forward the event.

If ‘and’ is selected, all filters specified must be satisfied before an event can be audited. If ‘or’ is selected, only one of the specified filter needs to be satisfied.

Add

Use to move the filter entered above to the Column Filter list at the bottom of the page.

Remove

Use to remove the selected entry from the Column Filter list.

Modify

Use to change the operator or value of the filter selected in the Column Filter list.

Specify columns with sensitive data page: From here you can specify the columns within a table that are deemed to potentially include sensitive information.

Add\Remove

The data grid across the top of the page displays the SQL table/columns/ and data type. Select/highlight an entry and then use the Add and Remove buttons to define the values to be used in the filter. Select Refresh Columns to update the data.

Once these columns are identified, they will not record values in the database and will display as “***” in the Event Details pane to maintain privacy.

NOTE: When multiple SQL Data Level templates are auditing the same database, all sensitive column configurations defined in any of the enabled templates is applied.

For example:

•

Template1 is enabled and configured with sensitive columns PERSON.NAME, PERSON.PAYGRADE.

•

Template2 is enabled and configured with sensitive column PERSON.ADDRESS.

•

Template3 is disabled and configured with sensitive column PERSON.DEPARTMENT.

For a new event, data in the PERSON.NAME, PERSON.PAYGRADE and PERSON.ADDRESS fields will display as "***".