Audit All Select Statements

audit_selectLegislation and regulation sometimes dictates that certain activities must be tracked within a database. On occasion, it will be required that queries be audited and tracked to reach compliance with the legislation or regulation. To achieve this compliance, this article will demonstrate how to use Extended Events to audit statements being executed within the database.

Over the course of a few recent articles, I have included little hints here and there alluding to this article. In fact, now would be a good time to review one of these articles in preparation for what I will be sharing today. Take a moment to refresh your memory on this article about finding the right event – here.

Backstory

I have to be a little honest here. Prior to somebody asking how they could possibly achieve a statement audit via extended events, I had not considered it as a tool for the job. I would have relied on Audit (which is Extended Event related), or some home grown set of triggers. In this particular request, Audit was not fulfilling the want and custom triggers was not an option. Another option might have included the purchase of third party software but there are times when budget does not allow for nice expensive shiny software.

So, with a little prodding, I hopped into the metadata and poked around a bit to see what I could come up with to achieve this low-budget audit solution.

Low-Budget Audit

Using the handy scripts I have shown previously (and that I just linked to), I was able to explore the Extended Events metadata and find just what may work properly. The requirements in this case were that it needed to be done in XEvents and that it must capture SELECT statements. To find the events that seemed plausible for this task, I plugged the term “SELECT” into my queries as follows:

From this query, there was really only one event that made any sense for my task – “degree_of_parallelism”. There was another event returned in the result set, but it made no sense to me as a possible candidate event for auditing select statements (it was ucs_transmitter_reclassify). From the results, I then took the keyword associated to degree_of_parallelism to see what else might be pertinent. I plugged that keyword “execution” into the following query:

The results of the previous query will be quite a bit more substantial. Some make sense to include in the audit and some do not. I opted to not included any of the events to keep things as clean and simple as possible.

More Requirements

I now have the base events covered that I want to use for my audit. That said, my base requirements are just not extensive enough. When I audit something, I really want to know who did it, when it was done, where it originated and what was it that they did. The degree_of_parallelism event will capture all of the select statements but it does not meet these additional requirements.

In order to meet the additional requirements, I will attach a handful of actions to the degree_of_parallelism event. The addition of the actions will provide all of the data I want and need. Combine the event and actions together, I came up with the following session definition.

This is a very simple session to pull together. I have added a few things in on my predicate to help limit the scope of the session to the AdventureWorks2014 database and to make sure I am not trapping events related to the code completion tools shown. Notice here also that there is a potential chance to optimize this session. Can you find it? Hint: It may be in the predicate. Second hint: re-read this article about predicates.

Now the trick to what makes this work to audit only the select statements is right there in the predicate. I have instructed the session to ignore any statement_type that is not a value of 1. As it turns out, statement_type of 1 is a select statement. To see how these values map, here is a quick query and the correlated results.

dop_statementtype

Based on these values, should you want to audit a different query type, just change the predicate to use the map_key value that corresponds to the desired statement type.

Testing

Testing this session is rather simple. Here is a quick and dirty script to help test it.

Conclusion

Building low cost solutions is a common requirement for the data professional. When given the opportunity, try things out to see what you can build to provide the solution. In this case, I have demonstrated how Extended Events, however unlikely a candidate, can provide a working solution to help audit any select statements occurring within your database.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.

4 Comments - Leave a comment
  1. phil h. says:

    Jason, this is a great idea, but one thing we must provide in regulated systems is separation of duties. The problem with SQL Audit and XE is that they’re not separated from the SQL process, and are by design under the control of the DBA/sysadmin. Only a system such as Guardium fulfills our auditors’ requirements.

    • Jason Brimhall says:

      One of things that fulfills this separation (depending on your auditor and documentation) is the moving of the audit files to a centralized location. I have had auditors sign off on that and fulfill the separation of duties. One thing to be careful about with separation of duties is that a single audit utility across all system types and systems now suddenly becomes no separation of duties due to the single point of access for audit.

      Telling a client that the only choice is to purchase a third party tool is not a viable option in my opinion. They have to be presented with options and decide between those options. Buying a tool is not feasible for everybody. Documenting and following process, however, will help them survive most audits.

  2. Peter says:

    Curiosity – would SQL Audit be a reasonable choice if I wanted to audit all SQL Statements run by accounts that don’t match a given pattern? I’d like to audit SQL run by non-service accounts – DBA, SysAdmins, others who might somehow have gotten rights. I don’t want to audit processes run as part of the normal day-to-day apps.

    • Jason Brimhall says:

      SQL Audit would be an appropriate mechanism for auditing statements run by specific people. The complication with sql audit comes in the event you only want to audit a specific query type. To do that, we have to get tricky or start using a third party app.

Leave a comment

Your email address will not be published. Required fields are marked *










Calendar
August 2016
M T W T F S S
« Jul   Sep »
1234567
891011121314
15161718192021
22232425262728
293031  
Content
SQLHelp

SQLHelp


Welcome , today is Saturday, July 22, 2017