Web lists-archives.com

Re: audit trails

2016/12/06 02:33 ... machiel@xxxxxxxxxxxx:
      The audit table should include the following information:

         -- Who made the changes (username logged in)
         -- What type of change (insert,update,delete)
         -- Date of change including time.
         -- if update, which fields were updated.
         -- Query that was run.

        I have tried to get similar examples on the net, however no
luck so far that gives the specific information.

        I know that this would probably be done using some triggers,
however I am not sure on how to get the relevant information (i.e.
user, query,etc...)

If you use triggers for this you automatically get the "type of change" because that and the table name are part of "CREATE TRIGGER" (q.v.). There is the function "CURRENT_USER()" which you can use. As for the rest, although MySQL keeps track of it, none of it is passed on to the SQL programmer. Furthermore, you CREATE three TRIGGERs for every table.

I suspect one of the logs is more to the point. There is the general log which records _everything_; there is the binary log, meant for replication, which records all changes; there is the audit log, which is a plug-in, beyond which I know nothing about it.

MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql