Re: audit trails

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" <[email protected]>:

> 2016/12/06 02:33 ... [email protected]:
>>      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