Web lists-archives.com

Re: audit trails




Thank you for the reply.

  well in essence the following is required.

    we need to know who made what changes to tables.

we recently had a case of important data being deleted, however finding it i binary logs proved 2 things :

1. it takes very long to find as we did not know in which file the details were.
2. binary logs did not contain the user who executed the commands.

Thus adding auditing table to record the date, change type, and user name would make it easier to find this.

I have managed to figure that part out almost fully and have one or two more kinks to work out. We will be adding an error in the trigger for deletes, however it should still log the delete attempts to audit table and this is where I am stuck now.

I hit the error, however the attempt to delete is not being logged to the audit table.

The problen however is now that they would like to know what query was run. i.e. was it a straight query or was it run by calling some procedure. I am however not sure if this will even be something that can be logged.

Regards





Quoting "Hal.sz S.ndor" <hsv@xxxxxxxx>:

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




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