Re: audit trails

[Jesper Wisborg Krogh]

Hi,

On 8/12/2016 18:39, mach...@seworx.co.za wrote:

...

 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.


If your trigger generates an error, the only way to get it to log an 
entry into a table is to ensure that table is not using a transactional 
storage engine. Otherwise both the attempted delete and the audit insert 
will be rolled back. Using a non-transactional storage engine of course 
has its own problems, but depending on the requirements of the logging, 
it may be good enough.


An alternative as already mentioned is to use an audit log plugin. In 
MySQL 5.7.13 and later, there are extensive filtering options available 
to avoid logging everything, e.g. it's possible to limit the audit 
logging to specific actions and/or tables. See also 
https://dev.mysql.com/doc/refman/5.7/en/audit-log-filtering.html - 
Disclaimer: I work for MySQL so will of course be happy to see you 
choose our audit log plugin.


If you intend deletes not to be possible, I will also recommend you to 
remove the DELETE and DROP privileges to the table for your users.


Best regards,
Jesper Krogh
MySQL Support


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

Re: audit trails

[machiel]

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 <vegiv...@tuxera.be>:


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" <h...@tbbs.net>
To: "MySql" <mysql@lists.mysql.com>
Sent: Wednesday, 7 December, 2016 14:56:55
Subject: Re: audit trails



2016/12/07 01:26 ... mach...@seworx.co.za:

  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

Re: audit trails

[Johan De Meersman]

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" <h...@tbbs.net>
> To: "MySql" <mysql@lists.mysql.com>
> Sent: Wednesday, 7 December, 2016 14:56:55
> Subject: Re: audit trails

> 2016/12/07 01:26 ... mach...@seworx.co.za:
>>   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

Re: audit trails

[Hal.sz S.ndor]

2016/12/07 01:26 ... mach...@seworx.co.za:

  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

Re: audit trails

[machiel]

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" :


2016/12/06 02:33 ... mach...@seworx.co.za:

 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

Re: audit trails

[Hal.sz S.ndor]

2016/12/06 02:33 ... mach...@seworx.co.za:

  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

audit trails

[machiel]

Good day all

   I am hoping this mail finds all well...

I am in need of some assistance please.

We have a few tables that I need to add auditing information for.

 The idea is that we need to create an audit table that will track
the following on the main table :

   - any changes (updates,deletes, inserts) to the main table

  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...)



   Any help would be appreciated.


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