auDitinG | "You are the CEO of your destiny"

Is it me or does it seem like we just had a T-SQL Tuesday blog party? These days are just flying by! This week Mickey Stuewe (b|t) is hosting and she has brought up a fabulous question about auditing with the topic being Follow The Yellow Brick Road. I have been on both sides of the fence in shops where there has been very limited auditing versus mandated regulatory auditing of their systems – so what’s my preference? Glad you asked; pull up a chair as we travel down the yellow brick road from munchkin land while trying to avoid the wicked witch and her ape like minions.

What to Audit?

As we start out on this journey, there are multiple questions on what needs to be audited one should ask themselves. Let’s face it, there are many wicked witches out there that just flat out want to get to our data and the longer I’m in the business I’ve seen more and more attacks from within, then from the outside. Here are a few ideas on some things to audit:

Tables with sensitive data such as SSN’s, personal information, transaction data
Review of your QA, UAT, and Prod environments (some shops like to audit all 3 some like to only audit Prod) – don’t be the one who audit’s none!
Check-ins of code into your repository
Inserts
Updates
Growth trends
Security Access
File Shares

These are just a few ideas, but don’t limit yourself to just auditing who inserts or updates data into your systems. As you fly through the house like Dorothy did in the SQL tornado remember to think outside the box some and audit other areas such as growth, baseline trends. The SQL environment is always evolving, use the necessary tools to keep one step ahead of the storm.

How To Audit

All right, so I’ve identified what I want to Audit. It seems like it is a daunting task and I have no clue where to begin; that’s where the Tin Man comes into play. If you are a Data Professional then you have to care about the data enough to even be reading this post – – means you have a heart.

There are many different avenues you can take to audit your systems, a few of those are:

Triggers within SQL
3rd Party Auditing Tools
CDC
Home grown utilities

Whatever avenue you decide to take, just take the stance of doing something. Doing nothing is not something I would advise; knowing who is changing your data, schema, files, etc. is an important aspect of any data professionals life – who knows if you have a mighty Wizard of Oz such as your auditing department or compliance department they might just be thankful for your efforts.

Summary

When you are going through your own SQL journey on the yellow brick road remember that auditing can be viewed upon as an asset. Look into what you are doing currently, can you improve upon it? Are you doing anything at all? Is your data being protected? Even better do you know if your data is being protected?

Take the time to put some safeguards in place in the end you will be glad you did.

T-SQL Tuesday

Wait a minute, wait just a minute. What is this T-SQL Tuesday you speak of. The mighty Wizard of Oz, Adam Machanic (b|t), started this party in 2009. Basically each month on the first Tuesday an invitation is sent out that describes a topic for that month; the second Tuesday bloggers put together a post regarding the topic and then send it back to the person who is hosting. If you have a blog and are interested contact the mighty Wizard of Oz and he’ll get you on the schedule.

Real life scenario ~ I’ve seen companies that I’ve come into not have their production environment on lock down and every one who is anyone has access to the environment.

I like the idea of having a table with the following columns:

AuditID – Unique Identifier
AuditDate – Date the event occurred
LoginName – Who did the event
ObjectName – What object was affected
Command – The command that was executed
EventType – What kind of event occurred
ServerName – The server the Event occurred on
DatabaseName – Which Database the Event occurred on

Once the table is created you can create a database trigger that will dump data into your table so you can track who is doing what in the database at any given time. I’ve used this practice at previous job sites and was happy to see this being utilized at my current employer.

The trigger will look something similar to this:

CREATE TRIGGER [tr_Trigger Name]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

BEGIN

SET NOCOUNT ON

DECLARE @EventData XML
SET @EventData = EVENTDATA()

BEGIN TRY

INSERT INTO [DATABASE NAME].dbo.[TABLE NAME]
(
AuditDate,
LoginName,
ObjectName,
EventType,
ServerName,
DatabaseName,
Command
)
SELECT
GETDATE(),
t.c.value(‘(LoginName)[1]’,’VARCHAR(255)’),
t.c.value(‘(ObjectName)[1]’,’VARCHAR(255)’),
t.c.value(‘(EventType)[1]’,’VARCHAR(100)’),
t.c.value(‘(ServerName)[1]’,’VARCHAR(255)’),
t.c.value(‘(DatabaseName)[1]’,’VARCHAR(255)’),
t.c.value(‘(TSQLCommand/CommandText)[1]’,’NVARCHAR(MAX)’)
FROM
@EventData.nodes(‘/EVENT_INSTANCE’) t(c)

–Note you can add a where clause and exclude “Stats” if you so desire”

END TRY
BEGIN CATCH
EXECUTE [DATABASE NAME].dbo.[SP TO CAPTURE ERRORS]
END CATCH

END

I’ve found this technique useful in running reports after deployments on what was deployed; going back for future reference on what was done, capturing issues among team members of not knowing what they were doing in order to correct it, and just for overall satisfaction of knowing what is occurring on the database itself.

If you’re not sure what is occurring on your databases then maybe this is something that you can try to help build a base to capture events that are occurring from drops, creates, to grants, etc.