how to design audit trail

Question

Post reply

how to design audit trail

molecule_kaab

SSC Eights!

Points: 826
More actions
March 9, 2008 at 12:08 pm

#179808

I want to design what data should be kept for audit trail
Can anyone give me any ideas to guide me, what the import thing I should have?
My system is about member registration and member can submit document (like submit assignment)
and the admin will come to check the valid of that document and update the status to that document then will pass to another system
Thanks in advance 🙂

Viewing 14 posts - 1 through 13 (of 13 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

First of all you have to determine & define what the Purpose of your Audit Trail is. Is it for:

1) simple diagnostics and troubleshooting of the application & database?

2) Sophisticated diagnostics with the ability to recover from serious application data errors?

3) Security auditing and investigation?

These are substantially different goals that are best served by significantly different designs.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

dataguru1971 SSC Veteran Points: 267 More actions · Answer 2

That can be a pretty broad question.

What do you intend to keep in the trail?

Updating of record?

You can have a trigger set up to store information in another table when something is changed

Movement of data?

A scheduled agent job, SSIS, or DTS package can include an update/insert to a table which logs the completion of the job

Location of the files?

A table could be made to keep location codes or status codes containing required information.

Some of this goes into the actual database design phase as well. you don't decide to build a car, and put the transmission in last.

David.Poole SSC Guru Points: 76035 More actions · Answer 3

rbarryyoung (3/9/2008)
First of all you have to determine & define what the Purpose of your Audit Trail is. Is it for:
1) simple diagnostics and troubleshooting of the application & database?
2) Sophisticated diagnostics with the ability to recover from serious application data errors?
3) Security auditing and investigation?

4. To support a blame culture with proof?:w00t:

As a broad brush approach you need to keep track of when, who and what in that order.

You also need to plan for purging your audit trail otherwise you end up with TB of audit trail for MB of data.

LinkedIn Profile

molecule_kaab SSC Eights! Points: 826 More actions · Answer 4

Thanks, all

Actually I want to keep track for security and navigation

I want the admin can tell who do what transaction and

he can find that it's possible that the person is fake. That's all

🙂

GSquared SSC Guru Points: 260824 More actions · Answer 5

molecule_kaab (3/10/2008)
Thanks, all
Actually I want to keep track for security and navigation
I want the admin can tell who do what transaction and
he can find that it's possible that the person is fake. That's all
🙂

Two ways I know of to do that. One, the most accurate, is get a log parsing program, like Lumigent, or Apex SQL Log, or Red Gate's product (can't remember the name at the moment). The second is to add update/insert/delete triggers, and insert the action into a log table. You can either build you own for that, or again, buy something that will build it for you. Red Gate and Apex both have products for that, if I'm not mistaken.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Will C2 Auditing help in this area?

--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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

molecule_kaab (3/10/2008)
Thanks, all
Actually I want to keep track for security and navigation
I want the admin can tell who do what transaction and
he can find that it's possible that the person is fake. That's all
🙂

In that case, here is the data that I would include:

For every command that they execute:

The command text (as from DBCC INPUTBUFFER)

and from SysProcesses:

spid

database

uid

login_time

ecid

status

sid (you can derive LoginName from this)

hostname

program_name*

hostprocess*

cmd

nt_domain

nt_username

net_address

net_library

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

molecule_kaab SSC Eights! Points: 826 More actions · Answer 8

Now I decide the table for tracking change, including old value and new value

I wonder that if I use TRIGGER, can it tell which field is modified.

I want to keep something like

ID : 1

IP : 11.11.11.1

TableNAME : member

FieldName : Name, LName, Phone

OldValue : mm, aa, 02203030

NewValue : ee, av, 20202032

Action : UPDATE

Actually in member table contains many fields, but there are only three field have been modified.

If I use trigger,it's possible to compare the value in sql?

if yes, please guide me some code.

Thank you very much

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

molecule_kaab (3/23/2008)
Now I decide the table for tracking change, including old value and new value
I wonder that if I use TRIGGER, can it tell which field is modified.
I want to keep something like
ID : 1
IP : 11.11.11.1
TableNAME : member
FieldName : Name, LName, Phone
OldValue : mm, aa, 02203030
NewValue : ee, av, 20202032
Action : UPDATE
Actually in member table contains many fields, but there are only three field have been modified.
If I use trigger,it's possible to compare the value in sql?
if yes, please guide me some code.
Thank you very much

You really need to lookup Triggers and IF UPDATE() in Books Online before you go much further... if you write a trigger incorrectly, it can have a huge negative impact on performance. Learned about how to use the INSERTED and DELETED tables in a set based fashion.

--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)

molecule_kaab SSC Eights! Points: 826 More actions · Answer 10

Thank you very much, Jeff

I have read tutorial of '' if update()''. It's work very good.

Now I have any problem, I develop the web application and want to

send username and IP address of the client to my audit table

How can I send these information to my trigger, if possible?

Thank you again 😀

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

There are several system functions to help you in the area of user names...

USER

USER_ID()

USER_NAME() -- Same as CURRENT_USER

SESSION_USER

SYSTEM_USER -- Probably the one you're looking for.

Using the @@SPID system function, you can get the SPID for whatever connection fired the trigger and use that to read the sysProcesses table to find similar information and the IP address/domain, etc.

Again, take a look at Books Online for explanations as to what are available in the "System Functions" and the sysProcesses table.

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

Also - as you've probably gathered from the above answers - there are lots of ways to accomplish what you want. IMO one of the WORSE ways to go is to substantially deviate from the structure of the original table; besides being a disaster to write, it also tends to be a space waster. I prefer to use a snapshot of the record "as is" during an AFTER INSERT and AFTER UPDATE, so that you have all of the "old" and "new" values you wish - laid out a little differently.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

molecule_kaab SSC Eights! Points: 826 More actions · Answer 13

Thank you very much for your replies.

For system function, it's very new for me and I'll find out it.

But the session that I mean is the session of the user that log in to my website.

when the users login, the system will create session showing their state.

I want to keep who is online and do the transaction on my system.

And my question is - can I send this session to trigger