What, When and who? Auditing 101

Question

What, When and who? Auditing 101

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Thanks Zero one. 🙂

Auditing in SQL Server is still in infancy state. It will take couple of more releases to get all the required information for auditing I guess.

-Roy

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 2

tymberwyld (11/28/2011)
When SQL 2005 came out, I cringed at the way SQL Notification services worked and basically threw it out the window as an option for change tracking. However, I re-tooled the same concept using SQL Service Broker, and using a simple, generic Trigger, was able to audit ANY table's changes (real-time, with both previous and new values) asynchronously and we were able to audit all databases into one or many audit databases if we wished. Views were created against the meta-data to make "pseudo" tables which resembled the originals, so comparisons could be made. Anyway, it's worked for years and was completely scalable. If we wanted to move the auditing database(s) to another server, it was as easy as "changing the channel" on the service broker, no messy Trigger or Proc changes.

That would make one heck of a nice article. Any chance of you taking that on?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

Roy Ernest (11/29/2011)
Thanks David for the compliment.
To agree to the definition of an Audit is difficult. Everyone has their own take on it. Your definition is easier to understand as well. Thanks for sharing it. 🙂

Heh... I've found that a lot of people define "Audit" as "Keeping historical data so we know who to blame when something goes wrong." 😀

Nicely done on the article. Good order of revelation with nice, simple examples. Well done, Roy.

As a side bar, I'm going to use your article to justify why we don't actually want to use CT when we finally get to 2008 at the new company I'm working at. 🙂 Of course, if they wait a bit longer, we can jump to 2k12.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

Hey Jeff,

Thanks Jeff. The second part will come out hopefully soon. Are there too many negatives on CT for your company to use CT? 😀

-Roy

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

Kind of, Roy. It's not actually negatives with CT... I'd be more likely to call it a lack of positives. CT doesn't appear to actually audit, in my feeble ol' mind. If I understand the article correctly, it only keeps one historical row back.

Like I said... good article. The fact that CT doesn't appear to do what I think of as "auditing" has no bearing on the fact that this is an excellent article about what CT does and doesn't do. Thanks for writing it. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

That is true. From what I have seen, we have to use at least two of the new Auditing technology together to get the "Audit" to work.

SQL Audit actually does audit Who and when but not what. 😀

-Roy

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 7

You can have WHO and WHEN available to you using CT with the CHANGE_TRACKING_CONTEXT method posted in one of my last posts. Storing WHAT changed would be limited by the size of the VARBINARY(128) column so it wouldn't be a good solution.

CT is a lightweight solution available to all editions and it can be quite useful in situations where too much detail isn't needed.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 8

I'll definitely wait for the other articles from Roy but, right now, I'm thinking I'll still with some good ol' fashioned, high performance audit triggers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eric.notheisen SSCommitted Points: 1643 More actions · Answer 9

This article and the subsequent one are very well done from a technical perspective. I think more emphasis needs to be added as to why auditing needs to be done. Many of the data problems we have are a result of internal people accessing or modifying data for their own benefit. I spent a good many years in the late '80s and early '90s as an internal auditor for Martin Marietta Astronautics Group. You would be amazed at how many people tried to modify database entries when it would affect their bonuses. In the middle 90's working as an internal employee for a consulting company we had a recruiter in one case and a sales rep in another attempt to export all the recruiting contacts and sales contacts. In the mid 2000's I worked as a application and database developer for a small international company. With about 36 people total in the company we had two who individuals who maliciously attempted to modify data. One of the two attempted to export all the company contacts for his own future benefit.

What is so amazing is that these kinds of issues are common across the scope of the business and government world. These actions provide me with a high level of distrust of individuals when it comes to protecting the data in the company I work for. The data in our databases is a vary valuable commodity for our organizations. We, in the IT end of the business, have a fiduciary responsibility to protect it from technical flaws and human flaws. Building in security, and auditing is a part of security, is paramount to our fulfilling our duty to our employers.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 10

You have a great point Eric. I should have given a bit more importance on why we need to do Auditing. And you have pointed out one of my weakness in all my articles. I usually dont give much importance on what are the uses of a particular functionality. I will keep that in mind next time I write one.

-Roy

jerryschiffelbein 85806 SSC Journeyman Points: 86 More actions · Answer 11

Actually, in a perfect world, you wouldn't need auditing -- the data is already perfect.

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 12

FYI

...Adds a stored procedure for the manual cleanup of the change tracking side table in SQL Server 2014 SP2 ...

sp_flush_CT_internal_table_on_demand [ @TableToClean= ] 'TableName'

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me