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
