Re: audit trails
- Date: Thu, 8 Dec 2016 18:47:13 +1100
- From: Jesper Wisborg Krogh <mysql@xxxxxxxxxx>
- Subject: Re: audit trails
On 8/12/2016 18:39, machiel@xxxxxxxxxxxx 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
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.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql