Re: audit trails

2016/12/07 01:26 ... [email protected]:
>   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
binlog_format=STATEMENT
; 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