Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,923
    Rep Power
    533

    Creating audit trails for database changes


    I would like to log all changes to my database on a per user basis. When I say "per user", I mean a user who logs on to my website, not a system or MySQL user. My goal is if some asks who changed a record to a given value, I would be able to research and provide an answer. As such, it is not enough to log "Bob made a change to table ABC on April 4th", but I need to be able to deduce that "Bob changed field XYZ of table ABC to 123 on April 4th" (in the interest of simplicity, I don't need previous values unless it was easy to do so). I have around 50 tables, and I am a little concerned about this whole thing spiraling out of control. If need be, it would be acceptable to just store the actual queries along with the user_id and time of change. I am undecided whether I will bother logging idempotent queries.

    Most of my changes are performed via Ajax, however, some use tradition forms and a couple use interaction from another servers (i.e. a fax server hits my application and indicated that the fax was sent). Regardless, each is processed using a MVC approach where the controller receives a given task, and acts accordingly. Most queries are performed using PDO, so I do not readily have the actual SQL with values, but could obviously parse and create them.

    I would rather not use the MySQL general query log as I don't wish to save certain transactions (i.e. username/password queries for logon, albeit, it is incremented) and it is not easy to correlate users to it. I think it best to log in a database instead of a file, but am not 100% certain. I have never used triggers, and am not sure how they can be used since I also wish to associated the user who made the change, but am not certain.

    Any suggestions on how to implement such audit record documentation?

    Thank you
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,379
    Rep Power
    594
    Would not triggers solve your problem?
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    I guess the least cumbersome way would be to make a log() method, which fetches the prepared (UPDATE) query together with the bound values, parses it and turns it into a log entry stating the changes.

    Getting the query will be the hardest part, depending on whether you use real prepared statements or the fake ("emulated") ones. For real statements, there's PDOStatement::debugDumpParams, but it seems to be broken currently as it doesn't return the actual values. So you either need to apply a patch or manually pass the bind values to the log or use emulated prepared statements.

    The rest should be pretty simple.


    // Of course you could also do this on database level by, for example, adding a "last_changed_by" column to each table, change it on every UPDATE and have a trigger log the data together with the user ID stored in this column. But this would require a lot of stupid work. Maybe ask the database people if there's a better way to associate changes with a custom user ID.
    Last edited by Jacques1; April 4th, 2013 at 10:54 AM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,923
    Rep Power
    533
    I still don't understand how I would use triggers. Note that I do not want to add a new last_user_id column to every table.

    In regards to the PHP application way, I was thinking of override the PDO::exec() and PDO::execute() classes to log the information as needed. Not really sure if it will work, however.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,379
    Rep Power
    594
    You could create a separate table with a column to represent each piece of information you want to track when a record is added/delete/modified. Among those things would be the user, timestamp, effected table and record and any other information you want to keep. Create a trigger for add, delete and modify of whatever records you want to track and put the data into that table.

    Of course this then becomes a MySQL question.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by gw1500se
    Create a trigger for add, delete and modify of whatever records you want to track and put the data into that table..
    And how to you get the user that made this query?

    And don't forget that the association of the query and the corresponding user has to happen atomically. You cannot pass the user ID to the database and then do the UPDATE afterwards. You'd have to wrap every UPDATE in a function or transaction or include the user ID in the query itself.

    With the PHP-side solution, on the other hand, you could keep your queries and table structure and merely add a function call after every query.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,379
    Rep Power
    594
    Originally Posted by Jacques1
    And don't forget that the association of the query and the corresponding user has to happen atomically.
    Hmm. That's the kicker, isn't it? That the user is not in the database is what complicates it. I'll have to think about it for a while but it does sound like a show stopper.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,379
    Rep Power
    594
    I haven't thought this through completely yet, so I'm just throwing it out for consideration. Perhaps, sys_exec would do the trick. Have it call a script that can access the session variables for the user performing the transaction. Then return the user to the trigger for logging. The trick would be to pass enough information about the transaction being logged to the script so it can identify which user/session is performing it.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  16. #9
  17. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    You can pass the ID of the current user to MySQL and store it in a MySQL session variable. It will stay there for the duration of the PHP script execution, and you can use it in triggers. There isn't a race condition here, as long as you're not using persistent connections, because your PHP script is single threaded, your database connection is single threaded, database connections are not shared between instances of an executing PHP script, and your PHP thread and your database connection thread essentially block each other while executing.

    Comments on this post

    • gw1500se agrees : Damn!!! I wish I had thought of that.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,923
    Rep Power
    533
    Is the consensus that triggers are the way to go?

    A PHP alternative is to store every full query directly in a DB along with the user_id and datetime. It doesn't, however, make it easy to determine who made a change to a specific column.

    To do so, I was thinking of something like the following. It is probably a very bad idea, but I will present it anyways. Take the query and deconstruct it to which table and columns are affected, what values they are set for for which PK. Schema would be something like as follows. My concern is that if the main DB schema is changed, the data for this must change. Maybe do something similar but use MySQL's internal DB (probably a worse idea!).

    audits
    -id (PK)
    -user_id
    -ip_address
    -datetime
    -action (insert/update/delete)

    audits_have_tables (My thought was that queries might effect two tables. Maybe combine with "audits" and add two rows?)
    -audits_id (PK/FK)
    -tables_id (PK/FK)
    -pk (not PK of this table, but of the changed records)

    audits_tables_have_columns
    -queries_id (PK/FK. References audits_have_tables)
    -table_id (PK/FK. References audits_have_tables)
    -columns_id (PK/FK)
    -old_value (the old value of the record before the change)
    -new_value (the new value the record is set to)

    // Question? Should tables and columns be given surrogate keys?

    tables
    -id (PK)
    -name

    columns
    -id (PK)
    -table_id (FK)
    -name
    Last edited by NotionCommotion; April 6th, 2013 at 08:04 AM.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,923
    Rep Power
    533
    Hi,

    I am very surprised I don't find more articles written on this subject as it seems like a fairly common requirement.

    Hoping to get some ideas before the weekend. Should I use database triggers, my PHP strategy described by my last post, or some other strategy?

    Thanks
    Last edited by NotionCommotion; April 5th, 2013 at 08:51 AM.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,379
    Rep Power
    594
    Given E-Oreo's reminder about MySQL session variables, I'd vote for triggers.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  24. #13
  25. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Do what you like best. If you're not sure, try it out.

    Personally, I'm rather sceptical about the database approach. Even if you get the user ID through the session variable hack, you still have to track the changes. Looping through OLD and NEW isn't easy. If it's even possible, you'll need yet another hack.

    MySQL simply isn't a procedural language.

    The PHP stuff should be a lot less painful.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,923
    Rep Power
    533
    Thanks for your replies.

    If I go down the PHP route, is there a typical approach to do so? Does my approach seem sound, or should I run away from it?
  28. #15
  29. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    I already suggested that in #3.

    See that post for the concrete methods and issues.

    The first PHP MySQL parser I came across is this:

    https://code.google.com/p/php-sql-parser/
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo