Web lists-archives.com

Re: audit trails

Hi Guys

   Thank you very much for all the input, I appreciate it.

 @ Johan

Once again glad to deal with you. I do agree that triggers are not the way to go and I personally also do not like using it unless absolutely required. I do feel other solutions may be better, however , unfortunately they have had this issue burn them so many times that they are forcing a quick solution that they want implemented within a day or two.

This means that I have not time to implement proper solutions or test it yet. I have only recently taken over these servers and there are so much to do still to get things stable.

I have however provided the possible issues to management and will be making some suggestions in terms of more permanent solutions so thank you for your input their as well.

@ Sándor

In terms of the binary logs, the problem was not due to not knowing where it was saved. The problem was due to the fact that they generate about 90Gb worth of binary logs per day each being 500Mb in size, so it takes time to sift through all these files to find out when the deletes were performed. To add to that, they only picked up the issue 4 days after it actually happened so this meant sifting through a LOT of files.

The audit table was decided to only contain some basic information for now. However the entries for Delete attempts are not yet writing to the audit table.

     So to recap what has been done for now :

- Triggers to insert a record in audit table to show the table, type of query(insert/update) and who made the relevant change. - Trigger to prevent deletes from tables which will feedback an error to state that deletes are not allowed.

     What I need to still resolve:

-- Trigger for deletes should still log an entry into the audit table to notify which user attempted to do a delete.
                  -- More permanent solutions to be implemented.


Quoting Johan De Meersman <vegivamp@xxxxxxxxx>:

Triggers are not the best way to go about this. Consider:

 * What is to stop a malicious user from truncating the audit table?
* Triggers may fail (corrupt target table, for instance) and a trigger failure may cancel the source statement * Triggers have a performance impact - you're basically doubling every DML action. * Triggers get executed sequentially for multi-inserts, slowing the whole operation down

I suggest having a look at one of the available audit plugins.

* Percona has one that can iirc also be compiled against the standard (oracle) community edition * Oracle have one too in newer versions (I think from 5.7?) but it's enterprise licensed * MariaDB has one in the community version, but only works against MariaDB server
 * McAfee also had one, but I'm unsure about it's current status

The benefit of a plugin is that the code runs out of the "userspace", has lower performance impact (parallelism) and cannot cause originating statements to fail. Additionally, I would assume that some of these, if not all, can also log towards an external target (file, network, ...).


----- Original Message -----
From: "Sándor Halász" <hsv@xxxxxxxx>
To: "MySql" <mysql@xxxxxxxxxxxxxxx>
Sent: Wednesday, 7 December, 2016 14:56:55
Subject: 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

Unhappiness is discouraged and will be corrected with kitten pictures.

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