No manual?

[olleharstedt]

Any manual or docs on usage, e.g. what the config file is expected to include?

[prwater]

Hello Olle,

As soon we have the manual ready we will release 1.0.

An example of a (working) config file can be found at https://github.com/SetBased/php-audit/blob/master/test/MySql/AlterAuditTableCommand/config/audit.json.

You need 2 schemata:

• One for your tables of your application, see database.data_schema in the config file.
• One for the the audit tables, , see database.audit_schema in the config file.

You can not use the same schema for both schemata.

Start with the above mentioned config file, edit the database`` section to your needs and leave the other stuff untouched. I assume you save your config file under etc/audit.json```.

Run the following command:

./vendor/setbased/php-audit/bin/audit audit etc/audit.json

Initially, this command will only make an inventory of your tables.

Edit ````etc/audit.jsonagain an set under thetables`` section the flag ``` audit``` to true for the tables for which your required an audit trail.

Run the audit command again:

./vendor/setbased/php-audit/bin/audit audit etc/audit.json

This time audit tables and triggers will be created.

Insert, update, and delete some rows in a table in the data schema and look at the data in the corresponding table in the audit schema. It will explain it self.

You can run the audit command with the verbose flag as well:

./vendor/setbased/php-audit/bin/audit -v audit etc/audit.json

or even more verbose

./vendor/setbased/php-audit/bin/audit -vv audit etc/audit.json

If you have any questions, you contact us at Gitter (https://gitter.im/SetBased/php-audit?utm_source=badge&utm_medium=badge&utm_campaign=pr-badge) or GitHub

With best regards,
paul

[olleharstedt]

Thank you, I will try this tomorrow!

[olleharstedt]

Hi! I gave this a try, but the script added the audit columns in my existing table. I assumed it would create a new table for auditing? Where in the config do I set the table name for the audit table?

[olleharstedt]

Ah, I think I misunderstood the use-case. You can't used slap it on an existing system without changing your code, e.g. id primary key will not work anymore.

[prwater]

You need two schemas:

• One with the tables of your application, i.e. data_schema in the config file.
• One for the audit tables, i.e. audit_schema in the config file.

For example:

        "data_schema": "test_data",
        "audit_schema": "test_audit"

[olleharstedt]

What's a "schema"? A database?

[prwater]

Yes, indeed, like information_schema, performance_schema, or test.

MariaDB [test]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| test_audit         |
| test_data          |
+--------------------+
4 rows in set (0.000 sec)

[olleharstedt]

Got it, will try again. ^^

[olleharstedt]

So far so good! Two more questions:

1. Is the meta JSON actually used?

2. How do I limit which columns are being audited? Right now, all of them are copied to the audit table.

[prwater]

1. Not any more, I removed this parameter from the config files (see cd78ea2).

2. Short answer: no!

A longer answer to question 2. So far, we had only cases were we want to track all changes in all columns if we choose to audit data changes in a table. However, we have one case were we want to skip the logging of a data change, namely the last login timestamp of a user.

In the skip field of a table you can add a MySQL variable. If this variable is not null the trigger will not add rows to the audit table.

Snippet from audit.json:

"ABC_AUTH_USER": {
            "audit": true,
            "alias": "usr",
            "skip": "@abc_g_skip_abc_auth_user"
        },

Snippet from the SQL code:

  set @abc_g_skip_abc_auth_user = 1;

  update ABC_AUTH_USER
  set    usr_last_login = now()
  where  usr_id = p_usr_id
  ;

  set @abc_g_skip_abc_auth_user = null;

If we choose to audit not all columns the triggers will be still executed if only not monitored columns are affected by a SQL statement. Hence, rows will be added to the corresponding audit table.

I am interested in your use case.

[olleharstedt]

Thanks for your reply!

Our use-case is logging of a payout table. We are doing payouts to our third-party clients, whom we have a partnership contract with. Some of the columns become redundant when logging, like the created column. But yes, maybe it's just easier to log everything.

[prwater]

Please, review the documentation at Read the Docs: https://php-audit.readthedocs.io/en/latest/?badge=latest

[olleharstedt]

👍