A tool for creating audit tables and triggers for logging data changes in a MySQL database.
PhpAudit is a tool for creating and maintaining audit tables and triggers for creating audit trails of data changes in MySQL databases.
PhpAudit has the following features:
- Creates audit tables for tables in your database for which auditing is required.
- Creates triggers on tables for recording inserts, updates, and deletes of rows.
- Helps you to maintain audit tables and trigger when you modify your application's tables.
- Reports differences in table structure between your application's tables and audit tables.
- Disabling triggers under certain conditions.
- Flexible configuration. You can define additional columns to audit tables. For example to log user and session IDs.
Using the audit trail you track changes made to the data of your application by the users of the application. Even of data that has been deleted or changed back to its original state. Also, you can track how your application manipulates data and find bugs if your application.
In this section we give a real world example taken from a tournament on the Nahouw. We have reduced the tournament table to two columns and changed some IDs for simplification.
select * from nahouw.NAH_TOURNAMENT where trn_id = 4473
The audit trail for this tournament:
select * from nahouw_audit.NAH_TOURNAMENT where trn_id = 4473
|2012-05-05 08:36:06||INSERT||NEW||310616503508533789||2||34532889||65||4773||Wrong name|
|2013-02-01 10:55:01||UPDATE||OLD||311037142136521378||5||564977477||107||4773||Wrong name|
|2013-02-01 10:55:01||UPDATE||NEW||311037142136521378||5||564977477||107||4773||Correct name|
Notice that the audit table has 7 additional columns. You can configure more or less columns and name them to your needs.
|audit_timestamp||The time the statement was executed.|
|audit_statement||The type of statement. One of INSERT, UPDATE, OR DELETE.|
|audit_sate||The state of the row. NEW or OLD.|
|audit_uuid||A UUID per database connection. Using this ID we can track all changes made during a page request.|
|audit_rownum||The number of the audit row within the UUID. Using this column we can track the order in which changes are made during a page request.|
|audit_ses_id||The ID the session of the web application.|
|audit_usr_id||The ID of the user has made the page request.|
From the audit trail we can see that user 65 has initially entered the tournament with a wrong name. We see that the tournament insert statement was the second statement executed. Using UUID 310616503508533789 we found the first statement was an insert statement of the tournament's location which is stored in another table. Later user 107 has changed the tournament name to its correct name.
On table NAH_TOURNAMENT we have three triggers, one for insert statements, one for update statements, and one for delete statements. Below is the code for the update statement (the code for the other triggers look similar).
create trigger `nahouw`.`trg_trn_update` after UPDATE on `nahouw`.`NAH_TOURNAMENT` for each row begin if (@audit_uuid is null) then set @audit_uuid = uuid_short(); end if; set @audit_rownum = ifnull(@audit_rownum, 0) + 1; insert into `nahouw_audit`.`NAH_TOURNAMENT`(audit_timestamp,audit_type,audit_state,audit_uuid,rownum,audit_ses_id,audit_usr_id,trn_id,trn_name) values(now(),'UPDATE','OLD',@audit_uuid,@audit_rownum,@abc_g_ses_id,@abc_g_usr_id,OLD.`trn_id`,OLD.`trn_name`); insert into `nahouw_audit`.`NAH_TOURNAMENT`(audit_timestamp,audit_type,audit_state,audit_uuid,rownum,audit_ses_id,audit_usr_id,trn_id,trn_name) values(now(),'UPDATE','NEW',@audit_uuid,@audit_rownum,@abc_g_ses_id,@abc_g_usr_id,NEW.`trn_id`,NEW.`trn_name`); end
PhpAudit can be installed using composer:
composer require setbased/php-audit
Or you can obtain the sources at GitHub.
Right now we are working on the manual and will be online soon.
We are looking for contributors. We can use your help for:
- Fixing bugs and solving issues.
- Writing documentation.
- Developing new features.
- Code review.
- Implementing PhpAudit for other database systems.
You can contribute to this project in many ways:
- Fork this project on GitHub and create a pull request.
- Create an issue on GitHub.
- Asking critical questions.
- Contacting us at Gitter.
For commercial support, please contact us at email@example.com.
PhpAudit has the following limitations:
TRUNCATE TABLEwill remove all rows from a table and does not execute triggers. Hence, the removing of those rows will not be logged in the audit table.
- A delete or update of a child row caused by a cascaded foreign key action of a parent row will not activate triggers on the child table. Hence, the update or deletion of those rows will not be logged in the audit table.
Both limitations arise from the behavior of MySQL. In practice these limitations aren't of any concern. In applications where tables are "cleaned" with a
TRUNCATE TABLE we never had the need to audit these tables. We found the same for child tables with a
ON UPDATE CASCADE or
ON UPDATE SET NULL reference option.
The project is licensed under the MIT license.