Re: audit trails

Hi,

On 8/12/2016 18:39, [email protected] 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