THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

A quick look at SQL Server Audit in Denali CTP3

I have been using the SQL Server 2008 audit feature on a regular basis and generally like it a lot. No doubt there is still room for improvement to make it practically more useful. Here are three changes I would love to see.

·         You can’t really just audit the SELECT statements. Yes, you can pick SELECT as an audit action type. But because of the way the audit feature is implemented, you most likely would also end up with a lot of UPDATE or DELETE in the audit log. Note that SQL Server audit logs its data at the points when permissions are checked and a SELECT permission check is almost always performed when UPDATE/DELETE is executed. In other words, practically you can’t specify that you only want to audit SELECT queries on a table but don’t care about any DML on the same table.

Why is this even an issue? One of primary reasons I use the audit feature is to track usage for deprecation purposes. On one reporting server, we suspect that a table is not really being used and would like to confirm that’s the case and drop the table. So I set up the table for audit on all SELECT queries by specifying SELECT as the audit action type on the table, expecting to find none or very few audit records. But I end up with millions of records in the audit log, and virtually all of them are updates. Note that this is a reporting server, and the table is being replicated from an OLTP server, on which the table is heavily updated. These updates are faithfully replicated by a transactional replication distribution agent. For our purpose, we know the table is being updated by the distribution agent and we don’t care about. We just want to know if anyone is querying the table for reporting. But we don’t seem to be able to do that efficiently with the SQL Server 2008 audit feature.

·         When you have a server with a lot of stored procedures calling each other, it can be difficult to find out where a statement in an audit record comes from. To me, the top most stored procedure is a key piece of audit information in addition to the exact statement being applied to the table directly. It should be captured, but it is not.

·         Much of the useful session level information is not captured in the audit record, making it difficult to correlate what (e.g. program name) did what (i.e. the info in the audit record) from where (e.g. hostname). There may be a perfect reason for not providing more/tighter correlation with the session level information. But this makes the audit feature less useful.

So the question is: do we have any good news from Denali CTP3? The answer is yes and no. Denali CTP3 has some nice improvement, but in my opinion it isn’t going far enough.

Good news is that in Denali CTP3, (1) we now can selectively retain audit records by using any or a combination of the columns in the audit records to setup a filtering predicate, and (2) the Additional Information column of the audit log now may include the T-SQL stack that has information on upper level calling stored procedures.

Not so good news is that these changes are not as effective as they could be or I’d love them to be.

I see two deficiencies in the audit filter capability. First, it is an attribute of the server audit object. It would give us more flexibility to add it as part of a database audit specification so that, for instance, we could create filters on what action type to include and what action type to filter out. The second issue with the current filter design is that it’s difficult to create an effective filter to retain only SELECT queries that are actually submitted instead ‘bogus’ SELECT as a result of permission checks on UPDATE or DELETE.

The inclusion of the T-SQL stack information is excellent. But my testing with CTP3 shows that it only provides information going up one level (I don’t know if this will change in the final release). For instance, if you audit SELECT on table foo, and you have the following two stored procedures:

CREATE PROC p_foo_1

As

   SELECT * FROM foo

go

CREATE PROC p_foo_2

AS

EXECUTE p_foo_1

go

 

When you execute p_foo1 and p_foo2, the statement captured in the audit records will be the same:

SELECT * FROM foo

The T-SQL stack for p_foo_1 is as follows, clearly identifying the calling proc:

<tsql_stack>

<frame nest_level = '1' database_name = 'test' schema_name = 'dbo'

       object_name = 'p_foo_1'/>

</tsql_stack>

 

And the T-SQL stack captured for p_foo_2 is:

<tsql_stack>

        <frame nest_level = '2' database_name = 'test' schema_name = 'dbo'

               object_name = 'p_foo_1'/>

</tsql_stack>

 

In both cases, the stored procedure is identified as p_foo_1, the one doing SELECT on the table directly. In the case of p_foo_2, you’ll need to pay attention to the value in the nest_level attribute. The nest_level value is 2, and you know that you must search one level up to find the calling stored procedure. This definitely helps. But if the nest_level value is 5, you end up having to do a lot of code search. So why not just identify the top calling stored procedure and save the user from having to search through the code?

With respect to the third issue I mentioned earlier, I have not found any change in CTP3 to better integrate/correlate the audit records with the SQL Server session level attributes.

 

Published Friday, October 07, 2011 3:27 PM by Linchi Shea
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

I was at PASS Summit 2011 in Seattle and had a chat with Jack Richins of Microsoft on the SQL Server

October 17, 2011 3:46 PM
 

Linchi Shea said:

We are told that SQL Server 2008 R2 Audit (and SQL Server 2008 Audit) has much less performance overhead

January 24, 2012 10:29 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement