Web lists-archives.com

Re: audit trails

2016/12/07 01:26 ... machiel@xxxxxxxxxxxx:
  well in essence the following is required.

    we need to know who made what changes to tables.

There is a machination that you can try in every trigger that will add the user-name to the binary log:

set @asdfasdfasd = CURRENT_USER();
INSERT INTO T VALUE ( ... @asdfasdfasd, UNIX_TIMESTAMP() ... );

The value assigned the variable @asdfasdfasd, since it is used to change a table, will show up in the binary log. The function "UNIX_TIMESTAMP" yields a number that matches TIMESTAMP in the binary log.

      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.

You did not know where the binary log was saved? That is set by you in the global variables "log_bin_basename" and "log_bin_index".

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.

Only if the DELETE looks valid is the BEFORE DELETE trigger triggered. If the deletion would yield inconsistency according to the constraints that you set up and MySQL supports then the deletion is aborted and rolled back ere AFTER DELETE trigger is triggered.

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.

I use
; then the transaction is logged--but MySQL Cluster does not support this.

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