SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Auditing / History / Deleting

    Through the course of developing an application the need for some application-level security auditing has been identified. It’s mostly an accountability tool – who edited this record last, we have some questions to ask sort of thing.

    I’ve been working it over in my head and I’ve come up with two ways to record audit information:
    1) in the table itself
    2) separate audit table

    In the table itself would be extra columns like this:
    Code:
    CREATE TABLE bob (
      some_row,
      some_row2,
      ...
      creator_userid,
      create_date,
      edit_userid,
      edit_date,
      delete_flag )
    So, we record who created the row (and when) along with the last user who edited the record (I’ll talk about the delete_flag column later).

    However, this records no historical information so sort of leaves us out in the cold if we wanted to know what happened to the row two edits ago. Also if I wanted to do some sort of a ‘global’ audit on a particular user to see what they did yesterday my application would have to visit *every* table and pull out audit information where userid = xyz.

    A audit table like this:
    Code:
    CREATE TABLE audit (
      auditid,
      userid,
      date,
      table,
      action,
      command,
      rowid )
    would allow me to record what a particular user did at any one moment. A sample row would look like this:
    1, ‘mattr’, ’11-27-2001_16:46:22’, ‘user’, ‘update’, ‘update user set password = ‘’ where userid = 123’, ‘0x1234’

    So it would tell me that some user updated the table where userid = 123. The rowid is an Oracle-specific thing which points to an exact row; it could easily be a numeric auto_increment field in MySQL for example.

    This also allows me to easily grab all audit records by a particular userid. Since this is an audit log keeping a foreign key to any table wouldn’t be a good idea in case a row was deleted (say if I removed user MattR he still made changes to rows).

    I think the second method is a little easier since I can easily audit *any* table in the system without heavy modification (if I used a DB abstraction layer I can write that into my UPDATE/DELETE code to automagically write the audit log).

    What about historical data? Keeping an audit trail is good but it isn’t terribly fun to look through to easily find what previous values of a particular column were.

    So I figure have the audit table in conjunction with a ‘history’ table like this:
    Code:
    CREATE TABLE history (
      auditid,
      column,
      oldval,
      newval )
    So that allows me to audit changes to a particular column.. This would, of course, be handled in the application – any time an update query came it’s way for a particular row it would have to check the old value of the row and see if it was updated. This could easily be an update trigger but I think the application level is fine since this would be used for when users edit a particular row (a particular user account for example).

    Of course, I could always append the ‘column’, ‘oldval’, and ‘newval’ columns to the audit tables, however if the user is executing a DELETE statement it wouldn’t really have a column, oldval, or newval values to enter. But since they would be text or varchars they wouldn’t really waste any space either.

    If you recall waaaayyyy up top I spoke about the ‘delete_flag’. Well, it’s always better, in my opinion, to logically rather than physically delete items from your application. What if a cracker steals an admin’s password and systematically deletes 1000 rows? What if a user accidentally deletes a row which causes a cascade effect causing 2 hours worth of work to re-enter all the dependencies? Plus, insertions and deletions are the most expensive (comparatively speaking) DML operation you can execute on the system. Indexes have to be updated, rows have to be shuffled around, etc.. A simple UPDATE bob SET delete_flag = 1 WHERE rowid = xyz is very easy (and very fast) to execute. Then, after a pre-determined amount of time, you can go ahead and run some clean-up operations which delete the rows and then defrag your tables (as an example). It also leaves time for users to go ‘holy crap!’ and un-delete the row. Or if you prefer, only admins would have the power to un-delete – to the user it would be just like the item was removed from the database.

    Thoughts? Suggestions? What do you use for auditing, historical information, and delete-safe?

    Thanks,

  2. #2
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've only ever really seen one thorough audit system, which was at my last workplace - the system I worked on had a legal obligation to store the state of all of its data at every point in the last 12 months in case of any accidents (it provided information that was used in a safety-critical area).

    The way we approached it was to simply create an audit history table for each table in the database (so table USERS had corresponding table USERS_H etc.) These history tables were duplicates of the tables they audited, with some extra fields; history_user_id and history_revision_time. The normal tables already stored the user id and revision time of the records, so in the audit table you had the time a record was valid from and until, and who performed any changes. This made it possible to track what had happened to a record from creation to deletion.

    That system worked very well, but it's a bit more time-consuming than your solution - obviously you end up with multiple history tables to look through, and store a lot more data just for audit purposes. Keeping them updated can be handled with triggers/stored procedures, and it lets you get a lot of information out, but it might be more difficult to work with.

    Anyway, that's the only really decent audit system I've handled. The system you're describing seems to cover everything you'd need too, I imagine it'd work well.

    With the audit system we used, we just allowed records to be deleted as we had a full snapshot of the record as it was deleted - so recovering the information later was simple. If you didn't have that, then using a "deleted" flag rather than physically deleting a record would be a great idea - in fact I think I might start using that myself .

    Not much help, but to my mind at least your solution seems sound .
    Nick Wilson [ - email - ]

  3. #3
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use "delete flags" in Access. Works a treat. I have a web-based admin form with a "compact" button that runs thru the database and deletes any records with the flag set. Only admins get to run it - about once a month.

    I'm just playing round with the idea of an audit table myself, as part of my quest for the perfect CMS (see this thread). I can't really see anything wrong with your solution. Matter of fact, I'm going to have a close look at it and see if I can get any ideas
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •