Confused about capturing user info when they make changes??

[accessinformation]

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.

 

[philhege]

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.

 

[Thargy]

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

 

[SQLSister]

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