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" <h...@tbbs.net>:
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
