Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Confused about capturing user info when they make changes??

Confused about capturing user info when they make changes??

Confused about capturing user info when they make changes??

I am really confused after reading some books and net stuff on security, etc.  I am working on a database and the company really wants to be able to track user information when a user makes changes.

Would this be tracked specifically in the table on which the change was made i.e., I would need code for each table?  Is there anywhere that addresses this specifically?  I found a lot on security and user groups, etc.  but haven't stumbled onto how to monitor transactions of sorts by user ID.

Thank you for any help or direction.

RE: Confused about capturing user info when they make changes??

This is usually handled by placing insert/update/delete triggers on the affected table(s).  Depending on your environment and database package, you can capture the user ID, the table name, the new record ID (for inserts), the changed information (for updates), the deleted record ID (for deletes) and place the information in a journal table.

RE: Confused about capturing user info when they make changes??

A better way is to design your database with a transaction API, using stored procedures.

All (and I do mean all) access to tables is via stored procedure(s).  this means that when a select is carried out, the sp is invoked.  The SP should correctly return the requested records, but behind the scenes it will also insert into a "designed in from the start" audit table, that such and such a user selected these records from that table at timestamp xyx.  Because the SP is the only way in and out for data, audit cannot be circumvented, other that by a very privileged user, e.g. a DBA.

Note that even what I've just said is the barely acceptable minimum.  Since a business wants business process audit, a table by table audit is almost useless, where for example, a single transaction updates 50 data in 20 tables.  Only a wizard could correlate 50 timestamps from these tables and mentally build up a picture of the business process which must have caused it.

I imagine that your employers want functional audit, so build that into your transaction API, not table audit, unless you are truly hard-pressed.

There is one far more knowledgeable that I, who has chapter and verse on this at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:25405782527721

Don't get confused with a table API (which is quite rightly panned) Tom advocates a transaction API.  Unfortunately I have seen both of these abbreviated to TAPI, which is highly confusing, I admit.

If you do what tom proposes, build a table API, which is called by the transaction API (this is the best solution, but naturally, most work).  The users are only given access to the transaction API.  The application will know nothing of the underlying data tables.

If you build a transaction API which accesses tables directly, this is still great, just not quite as good as doing both transaction and table API's.

Even though the discussion is Oracle-centric, the principles still apply to other technologies, e.g. sql server and my sql.

Grinding away at things Oracular

RE: Confused about capturing user info when they make changes??

Another term to search for is audit table. We capture all this information in our audit tables and can trace through several changes. These tables will grow very large (much larger in time than the orginal tables) so you will need a plan for when to delete records from the audit table or move them to an archive location.

YOu can also capture the last person to update or insert a record in a table by putting an Updated by column inthe table and populating it with a trigger. How to get the value is a database specific question.

However, this won't show who deleted a record (often a greater concern!) so the audit table approach is really the better approach.

"NOTHING is more important in a database than integrity." ESquared

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close