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.


Regards



Quoting Johan De Meersman <[email protected]>:

> 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, ...).
>
>
> /Johan
>
> ----- Original Message -----
>> From: "Sándor Halász" <[email protected]>
>> To: "MySql" <[email protected]>
>> Sent: Wednesday, 7 December, 2016 14:56:55
>> Subject: 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
>
> --
> 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