6.4. Auditing
Auditing solutions are built to enable retrospective analysis of user activity. SQL Server 2008 introduces a number of enhancements in this regard, which will be the focus of this section. We'll begin with coverage of the new SQL Server Audit feature before looking at DDL and logon triggers. We'll finish with a brief look at another new feature in SQL Server 2008, Change Data Capture.
6.4.1. SQL Server Audit
In SQL Server 2005 and earlier, auditing options consisted of simple server-level logon success/failure logging, custom audits using server-side traces or SQL Profiler, or the C2 trace option. What was missing was a more granular auditing option whereby a custom audit trace could be easily created that captured specific events such as executing DBCC commands.
In addition to all of the auditing options in 2005, SQL Server 2008 introduces a comprehensive new auditing model that addresses the need to easily create granular audit specifications. The new Audit feature in SQL Server 2008 consists of three main components:
An audit, which specifies the location of the audit output (file, application, or security event log), an option to shut down SQL Server if the audit can't be written, and a queue delay setting that specifies the number of milliseconds that can pass before audit actions are processed
Server audit specifications, which contain definitions of server-level audit events such as server logins
Database audit specifications, which contain definitions of database level events such as schema modifications
Let's walk through an example of creating an audit solution using SQL Server Management Studio. The first step is to create an audit. First right-click on Audits under Security and choose New Audit. As shown in figure 6.8, the Create Audit screen lets you specify various properties, including the file path if the audit destination is file based. As with most other parts of Management Studio, use the Script button to save the creation script for later inspection.
Both server and database audit specifications are created in the context of a matching audit. The audit events collected are sent to the Audit object, and written to either file or the event log, as defined in the Audit object.
Known as audit action groups, both server- and database-level audit specifications have a wide variety of audit actions that can be captured. Let's continue by defining a server audit specification. SQL Server BOL contains a full list of all possible server-level audit actions that can be chosen. For this example, let's create a specification that will capture the execution of any DBCC command. We can do this in Management Studio by right-clicking Server Audit Specifications under Security and choosing New Server Audit Specification. In the Create Server Audit Specification screen, shown in figure 6.9, select the audit we created earlier, and then choose DBCC_GROUP from the Audit Action Type drop-down box. If necessary, we can choose multiple audit actions.
Figure 6.8. Define audits using Management Studio or with T-SQL code.
Figure 6.9. Once an audit has been defined, you can define audit specifications that use the audit. The specification can be at a server level (as shown here) or at a database level.
The nice thing about auditing in SQL Server 2008 is that the database-level audit specifications are defined within the database itself. What this means is that if the database is moved from one server to another, the database-level audit specification will move to the new server. When attached to the new server, the audit specification will be orphaned until you use the ALTER DATABASE AUDIT SPECIFICATION command to reassign it to the new server's Audit object.
Let's expand our audit by including a database-level audit specification. We'll do this for the AdventureWorks database by expanding it and right-clicking the Database Audit Specifications option under Security and choosing New Database Audit Specification. The Create Database Audit Specification screen is similar to the one for the server-level specification, as you can see in figure 6.10. Again, we'll select the audit created earlier and then select from the options in the Audit Action Type drop-down box. In this example, we'll select the DATABASE_ROLE_MEMBER_CHANGE_GROUP option, which will audit events involving logins being added to or removed from a database role.
Once our audit action groups are defined, we can start the audit by right-clicking on it and selecting Enable Audit. The next step is to select the Server and Database Audit specifications, again by right-clicking on them and choosing Enable. Viewing audit data is as simple as right-clicking the audit specification and choosing View Audit Logs. Alternatively, if the audit events are directed to the Windows event logs, you can read them directly by using the event log viewing tools.
Figure 6.10. An audit specification at the database level
In our example, after running a DBCC command and adding a login to a database role, viewing the audit logs will reveal a screen like the one in figure 6.11. Although our example was a simple one, it reveals the ease with which granular auditing specifications can be created.
Figure 6.11. Viewing the audit logs is as simple as right-clicking the audit specification and choosing View Log.
You can use the new Audit feature to create granular audits at both a server and database level as needed to match your organization's custom auditing requirements. In comparison to other auditing options such as the C2 audit mode (described in SQL Server BOL), it offers a much more flexible option, while enabling powerful features such as portability of database audit specifications when transferred between servers.
Let's look at another feature that can be employed as part of a custom auditing solution: DDL triggers.
6.4.2. DDL triggers
DDL (Data Definition Language) triggers were introduced in SQL Server 2005 as a means of either auditing or preventing data definition statements. Not to be confused with DML (Data Manipulation Language) triggers, DDL triggers are defined on events such as CREATE TABLE. From an auditing perspective, they enable customized data to be collected for particular events.
Let's walk through a simple example to highlight the power and flexibility of DDL triggers. Suppose we want to capture the details related to the creation of new tables, including the T-SQL statement used to create the table, the user that executed the statement, and the date and time of the creation. Consider the T-SQL code in listing 6.3. We'll first create a table used to store the required details, before creating the DDL trigger that uses the EVENTDATA function to return the required details.
Example 6.3. DDL trigger to capture table creation details
-- create the table to store the audit details
CREATE TABLE dbo.CREATE_TABLE_LOG (
eventTime datetime
, eventOwner nvarchar(100)
, eventTSQL nvarchar(3000)
)
GO
-- create the DDL trigger
CREATE TRIGGER DDLTrigger_CreateTable ON DATABASE FOR create_table
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO CREATE_TABLE_LOG
VALUES (
GETDATE()
, CURRENT_USER
, @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(1000)')
)
GO |
Listing 6.3 obtains the T-SQL command from the EVENTDATA function, which returns information about server or database events. As such, it's ideal for use in the body of a DDL trigger.
With the table and trigger in place, a table creation command will fire the trigger and capture the associated event data. The results appear in figure 6.12.
Figure 6.12. Querying the results of a create table command that fired a DDL trigger
Not only can DDL triggers audit actions, they can also actively prevent certain changes. Consider the example shown in listing 6.4, which rolls back any attempt to drop or alter a table definition.
Example 6.4. DDL trigger to prevent table modifications
CREATE TRIGGER DDLTrigger_PreventTableChanges
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'Cannot drop or modify tables in this database'
ROLLBACK |
As listing 6.4 shows, the ROLLBACK statement rolls back any attempt to drop or alter any table in the database in which the trigger is created, along with the error message "Cannot drop or modify tables in this database."
Similar to DDL triggers, logon triggers, discussed next, enable auditing and control of the logon process.
6.4.3. Logon triggers
In a manner similar to creating DDL triggers, you create a logon trigger to either roll back (deny) a logon or capture information surrounding the logon using the EVENTDATA function.
Consider the example shown in listing 6.5, which prevents ReportUser from logging on between 11 p.m. and 11:30 p.m.
Example 6.5. Logon trigger to prevent logon for a period of time
CREATE TRIGGER validateLogonTrigger
ON ALL SERVER WITH EXECUTE AS 'logonTrigger'
FOR LOGON
AS
BEGIN
DECLARE @time time(0) = getdate()
IF ORIGINAL_LOGIN() = 'ReportUser'
AND @time BETWEEN '23:00:00' and '23:30:00'
ROLLBACK
END |
With the trigger in place, a logon attempt by ReportUser between 11 p.m. and 11:30 p.m. will be met with an error message similar to the one displayed in figure 6.13.
Figure 6.13. A logon failure message that results from the firing of a logon trigger
In our example, we used the ORIGINAL_LOGON function. There are a number of other functions that can be used, such as APP_NAME; however, as you'll see in chapter 16, careful consideration needs to be given to the function used, due to the possibility of function values being spoofed to circumvent the intention of logon triggers.
In closing our coverage of auditing features, let's consider another new feature in SQL Server 2008: Change Data Capture.
6.4.4. Change Data Capture
The Change Data Capture (CDC) feature, introduced in SQL Server 2008, is used to capture data modification activity in SQL Server tables and make the details of the activity available in a format that can be used for various purposes.
One of the main uses for CDC is for data warehousing solutions. The classic data warehouse load process involves identifying data that has been modified since the last load operation. Once identified, the data is the subject of an extract, transform, load (ETL) process.
The challenge for ETL processes is identifying which data has changed since the last load, and typically involves timestamp or GUID values along with a corresponding query to select all data with a timestamp/GUID value greater than the one used in the last load operation. CDC is perfect for this scenario, as all changes can be easily consumed, thus avoiding the need for expensive identification queries.
From an auditing perspective, CDC can be used to identify modifications to one or more tables. In versions of SQL Server prior to 2008, such auditing was typically performed using triggers or some other mechanism. CDC simplifies this process greatly while avoiding the expensive overhead of a trigger-based approach.
As a brief introduction to how CDC can be used from an auditing perspective, let's consider an example in which we want to track modifications to the Production. Product table in the AdventureWorks2008 database. To do so with CDC, we'll run the code shown in listing 6.6.
Example 6.6. Setting up Change Data Capture
USE [AdventureWorks2008] GO -- enable change data capture EXEC sys.sp_cdc_enable_db GO -- enable the Production.Product table for CDC EXEC sys.sp_cdc_enable_table @source_schema = N'Production' , @source_name = N'Product' , @role_name = N'CDCRole' GO |
At this point the table is defined for CDC. A number of tables and functions are created in the AdventureWorks2008 database to support CDC, along with two SQL Server Agent jobs for capturing and cleaning up captured data. To simulate and view captured changes, let's run the script shown in listing 6.7.
Example 6.7. Modifying data and viewing CDC changes
|
|
Let's walk through the code in listing 6.7 to understand the steps:
First, after declaring variables, we initialize @begin_time to 2 minutes ago. CDC queries work by providing a transaction log sequence number (LSN) range in which to return changes. To derive the LSN numbers, we use date ranges and the sys.fn_cdc_map_time_to_lsn function discussed shortly.
Next up, we run a modify statement on the Production. Product table. This is the change CDC will capture for us.
We then capture the @end_time using the GETDATE() function.
CDC captures changes using a SQL Agent job that reads the transaction log. The 10-second pause statement is inserted in order to give the Agent a chance to capture the change.
The next two statements capture the starting and ending LSN numbers using the start and end date time values captured earlier.
Finally, we select from the cdc.fn_cdc_get_all_changes_Production_Product function passing in the from/to LSN values as parameters. This function was automatically created for us when we enabled CDC on the Production.Product table in listing 6.6.
The output of the final select command in listing 6.7 is shown in figure 6.14.
After the required database and tables are defined, Change Data Capture enables a real-time, lightweight method of auditing changes to tables. SQL Server BOL contains a complete description of all of the functions we've used in this simple example, along with a range of other features of Change Data Capture.
The ability to easily define granular auditing solutions without the need for third-party tools or custom server-side traces is a powerful weapon in creating and managing secure SQL Server environments. In the next section, we'll take a look at another security feature that has been significantly enhanced in SQL Server 2008: encryption.