programming4us
programming4us
DATABASE

SQL Server 2012 : Auditing in SQL Server (part 3) - Database Audit Specification Object, User-Defined Audit Event

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/19/2014 9:25:16 PM

Database Audit Specification Object

If you want to audit events that occur within a database, you will need to define a database audit specification. To create one, you can select New Database Audit Specification from the Database Audit Specification node of the Security node of a specific database in SSMS.

Note Creating a database audit specification is only available on Enterprise edition and above.

Figure 5 shows the Create Database Audit Specification dialog box that opens.

images

Figure 5. Create Database Audit Specification dialog box

Just like the server audit specification points to a server audit, so too can the database audit specification. In this example, select Compliance Audit to use the existing server audit that you created earlier.

The Actions grid is where you define which audit events or event groups you want to record. In addition to having groups of events, the database audit specifications have a handful of single events. Some of these are SELECT, INSERT, UPDATE, DELETE, and EXECUTE. If you want to audit anyone who issues a SELECT statement on the Customers table, select SELECT from the Audit Action Type list. Then, specify that the object class is an object. The object name is the object that is the target of the audit. In this case, you want the Customers table. If you want to audit all users, you can enter public for the principal name, or else you can specify a specific user or group to audit.

You can also create a database audit specification using the following T-SQL statement:

USE [Accounting]
GO
CREATE DATABASE AUDIT SPECIFICATION [Customer information]
    FOR SERVER AUDIT [Compliance Audit]
        ADD(SELECT ON Customers by public)
GO

If you want to follow along and do not have this database created, execute the following script:

USE master
GO
CREATE DATABASE Accounting
GO
USE Accounting
GO
CREATE TABLE Customers
(id INT NOT NULL,
firstname VARCHAR(20) NOT NULL,
lastname VARCHAR(40) NOT NULL)
GO

Remember to enable the database audit specification once you are ready to start collecting events. You can do this by selecting Enable Database Audit Specification from the context menu of the specification or by issuing the following T-SQL statement:

ALTER DATABASE AUDIT SPECIFICATION [Customers Table]
    WITH (STATE=ON)
GO

Note To turn off auditing but retain the audit definitions, simply use STATE=OFF in the preceding code.

Now, to test this audit event, try issuing a SELECT * FROM CUSTOMERS statement. If you view the Compliance Audit audit log, you will notice an additional entry for the SELECT statement. Notice that you can see both the calling context and the actual T-SQL statement that triggered the audit.

Previous editions of SQL Server did not display T-SQL stack information. For example, if you audited SELECT on a given table, an auditing event would be raised when you directly accessed the data via a SELECT statement and, as expected, via a stored procedure. With SQL Server 2012, T-SQL stack information is written to the additional information column of the audit log if applicable. You can test this by creating a stored procedure that runs a SELECT statement on the Customers table. Sample code is as follows:

CREATE PROCEDURE ViewCustomers
AS
BEGIN
SELECT * FROM Accounting.dbo.Customers
END

Now, if we execute the stored procedure and then looked at our auditing log, we see the following information in the additional information column for the SELECT audit event:

 <tsql_stack><frame nest_level='1' database_name='Accounting' schema_name='dbo'
object_name='ViewCustomers'/></tsql_stack>

This additional information is helpful in determining the origin of the event that caused the audit to be raised.

COMPLIANCE AND THE PROBLEM WITH SYSADMIN

Although regulatory agencies’ specifications for compliance don’t specifically call out a SQL Server system administrator, they imply the need for protection against all users, and this includes system administrators. The problem with sysadmins is that they are, in some ways, running under the service account of SQL Server itself. You cannot protect data against the SQL Server service account, because SQL Server needs to have access to the protected data to serve to the users who have explicit access.

Although it is impossible to prevent a sysadmin from doing bad things to SQL Server or from reading data that they probably shouldn’t, you can create an audit that will protect against sysadmin repudiation attacks. To set up this configuration, simply create a server audit that points to a folder on a file share where the SQL Server service account has Append Data permission only. Although the sysadmin could still stop the audit and do something inappropriate, the mere fact that the audit was stopped at all should be a red flag to an auditor. In most cases, this kind of configuration is acceptable to auditors.

User-Defined Audit Event

There are occasions when you want to write a custom event into the audit log. Consider the scenario where an application connects to SQL Server through a common single user account. The application may support multiple users but when these users access the data in SQL Server, the database server doesn’t know the specific application user that is requesting data access to the database, because access is through a common user account. In this case, the application can write a user-defined audit event to ensure that, when auditors read the audit logs, they see the user who is connecting to the application who requested the data.

Writing a custom audit event is very straight forward. You can use the sp_audit_write stored procedure to write to an audit log. Before you can use this function, you need to create a server audit specification that contains the USER_DEFINED_AUDIT_GROUP. If you do not do this, the sp_audit_write stored procedure will not do anything but return success. To demonstrate this event, create a UserDefinedAudits server audit as follows:

CREATE SERVER AUDIT [UserDefinedAudits]
TO FILE
(       FILEPATH = N'C:\audit'
        ,MAXSIZE = 0 MB
        ,MAX_ROLLOVER_FILES = 2147483647
        ,RESERVE_DISK_SPACE = OFF
)
WITH
(       QUEUE_DELAY = 1000
        ,ON_FAILURE = CONTINUE
)
GO
ALTER SERVER AUDIT [UserDefinedAudits]
WITH (STATE=ON)
GO

Next, create the server audit specification CustomAudits and add the USER_DEFINED_AUDIT_GROUP.

USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [CustomAudits]
FOR SERVER AUDIT [UserDefinedAudits]
ADD (USER_DEFINED_AUDIT_GROUP)
WITH (STATE=ON)
GO

Now, with the audit defined, the server audit specification defined, and both enabled, we can utilize the sp_audit_write stored procedure as follows:

EXEC sp_audit_write @user_defined_event_id =  1000 ,
              @succeeded =  1
            , @user_defined_information = N'User Bob logged into application.' ;

The user defined event ID is any integer value you would like. It has no meaning other than what you make of it. The @succeeded bit can be used to determine if the event you are raising is a failure. The last parameter is a 4,000-character field where you can display any message you choose. After raising your event, you can view it by simply viewing the audit log in SSMS.

Other  
  •  SQL Server 2012 : Reordering Nodes within the Hierarchy - The GetReparentedValue Method,Transplanting Subtrees
  •  SQL Server 2012 : Querying Hierarchical Tables - The IsDescendantOf Method
  •  Protecting SQL Server Data : Obfuscation Methods (part 4) - Truncation,Encoding, Aggregation
  •  Protecting SQL Server Data : Obfuscation Methods (part 3) - Numeric Variance,Nulling
  •  Protecting SQL Server Data : Obfuscation Methods (part 2) - Repeating Character Masking
  •  Protecting SQL Server Data : Obfuscation Methods (part 1) - Character Scrambling
  •  SQL Server 2012 : Managing Resources - Limiting Resource Use, Leveraging Data Compression
  •  SQL Server 2012 : Tuning Queries (part 3) - Using the Database Engine Tuning Advisor
  •  SQL Server 2012 : Tuning Queries (part 2) - Gathering Query Information with Extended Events
  •  SQL Server 2012 : Tuning Queries (part 1) - Understanding Execution Plans
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us
    programming4us
     
     
    programming4us