The Audit Config File¶
This chapter is the specification of the audit config file.
For most projects the audit config file must added to the VCS and distributed to the production environment of your project (unless you have some other mechanism for maintaining audit tables and triggers).
The audit config file is a JSON file and consist out of four sections which we discuss in detail in the following sections.
{
"database": {},
"audit_columns": [],
"additional_sql": [],
"tables": {}
}
The Database Section¶
The database
section holds the variables described below:
credentials
(optional) The filename relative to the path of the audit config file of a supplementary configuration file. Any configuration setting in the supplementary configuration file will override the setting indatabase
section of the audit config file. You can choose your favorite configuration format for the credentials file: ini, json, xml, or yml. You can only store the password in the supplementary configuration file or all database settings.host
(mandatory) The host were the MySQL server is runninguser
(mandatory) The user that is the owner of the tables in thedata schema
andaudit schema
.password
(mandatory) The password of the owner.data_schema
(mandatory) The schema (database) with your application tables.audit_schema
(mandatory) The schema (database) for the audit tables. Thedata schema
and theaudit schema
must be two different schemata (databases).port
(optional) The port number for connecting to the MySQL server. Default value is 3306.
Convention¶
You are encouraged to follow this naming convention for the data schema
and audit schema
.
Both schema (databases) names start with the name or abbreviation of your project followed by _data
for the data schema
and _audit
for the audit schema
. For example foo_data
and foo_audit
.
Examples¶
Example 1¶
A basic example.
audit.json
:
{
"database": {
"host": "localhost",
"user": "foo_owner",
"password": "s3cr3t",
"data_schema": "foo_data",
"audit_schema": "foo_audit"
}
}
Example 2¶
In this example the password stored in credentials.ini
will be used.
audit.json
:
{
"database": {
"credentials": "credentials.ini",
"host": "localhost",
"user": "foo_owner",
"password": "foo_owner",
"data_schema": "foo_data",
"audit_schema": "foo_audit"
}
}
credentials.ini
:
[database]
password = s3cr3t
Example 3¶
In this example the user name and password stored in credentials.xml
will be used.
audit.json
:
{
"database": {
"credentials": "credentials.xml",
"host": "localhost",
"data_schema": "foo_data",
"audit_schema": "foo_audit"
}
}
credentials.xml
:
<?xml version="1.0" encoding="UTF-8"?>
<config>
<database>
<user>foo_owner</user>
<password>s3cr3t</password>
</database>
</config>
Example 4¶
In this example only settings stored in credentials.json
will be used.
audit.json
:
{
"database": {
"credentials": "credentials.json"
}
}
credentials.json
:
{
"database": {
"host": "127.0.0.1",
"user": "foo_owner",
"password": "foo_owner",
"data_schema": "foo_data",
"audit_schema": "foo_audit",
"port": 3307
}
}
The Audit Columns Section¶
The audit columns section specifies the additional columns that will added to each audit table in the audit schema
.
The additional column specification become in two flavors:
- value is either the action (i.e.
insert
,update
, ordelete
) or the state of the row (i.e.NEW
orOLD
), - value is a valid SQL expression that can be used in an insert statement in a trigger.
Example¶
{
"audit_columns": [
{
"column_name": "flavor 1",
"column_type": "...",
"value_type": "..."
},
{
"column_name": "flavor 2",
"column_type": "...",
"expression": "..."
}
]
}
Both flavors have the fields column_name
and column_type
in common.
column_name
The name of the additional column in the audit table. You must choose a name that is not been used in any of your tables in thedata schema
(for which auditing is enabled).column_type
The column type specification as used in aCREATE TABLE
statement.value_type
EitherACTION
orSTATE
.ACTION
The action of the SQL statement that has fired the audit trigger. Possible values areINSERT
,UPDATE
, orDELETE
.STATE
The state of the row.- An insert statement will insert one row in the audit table with value
NEW
. - A delete statement will insert one row in the audit table with value
OLD
. - An update statement will insert two rows in the audit table:
OLD
with the values of the row (in the data table) before the update statement andNEW
with the values of the row (in the data table) after the update statement.
- An insert statement will insert one row in the audit table with value
expression
Any valid SQL expression that can be used in an insert statement in a trigger.
Convention¶
You free to choose any column name for an additional table column as long the column name does not collide with a column name in a data table.
You are encouraged to follow this naming convention for the additional table column: the name of an additional table column has prefix audit_
.
Examples¶
In this section we provide several useful examples for additional columns.
Additional columns are optional, however, in practice additional columns given in examples 1, 2, and 3 are at least required to record a useful audit trail.
Examples 4 and 5 for recording all data changes made in a database session and the order in which they are made.
Example 1: Timestamp¶
Recording the time of the data change.
{
"audit_columns": [
{
"column_name": "audit_timestamp",
"column_type": "timestamp not null default now()",
"expression": "now()"
}
]
}
Example 2: Statement Type¶
Recording the statement type of the query.
{
"audit_columns": [
{
"column_name": "audit_statement",
"column_type": "enum('INSERT','DELETE','UPDATE') character set ascii collate ascii_general_ci not null",
"value_type": "ACTION"
}
]
}
Example 3: Row State¶
Recording the state of the row.
{
"audit_columns": [
{
"column_name": "audit_type",
"column_type": "enum('OLD','NEW') character set ascii collate ascii_general_ci not null",
"value_type": "STATE"
}
]
}
Example 4: Database Session¶
Recording the database session (a single connection from your PHP application to the MySQL instance). See The Additional SQL Section for setting the user defined variable @audit_uuid
in MySQL.
{
"audit_columns": [
{
"column_name": "audit_uuid",
"column_type": "bigint(20) unsigned not null",
"expression": "@audit_uuid"
}
],
"additional_sql": [
"if (@audit_uuid is null) then",
" set @audit_uuid = uuid_short();",
"end if;",
]
}
Example 5: Order¶
Recording the order of the data changes. See The Additional SQL Section for setting the user defined variable @audit_rownum
in MySQL.
{
"audit_columns": [
{
"column_name": "audit_rownum",
"column_type": "int(10) unsigned not null",
"expression": "@audit_rownum"
}
],
"additional_sql": [
"set @audit_rownum = ifnull(@audit_rownum, 0) + 1;"
]
}
Example 6: Database User¶
Recording the database user connection to the server. This example is useful when different database user can connect to your database. For example you have an application with a HTML frontend connecting to the database with user web_user
, a REST API connecting to the database with user api_user
, and some background process connecting to the database with user mail_user
.
{
"audit_columns": [
{
"column_name": "audit_user",
"column_type": "varchar(80) character set utf8 collate utf8_bin not null",
"expression": "user()"
}
]
}
On MariaDB the maximum length of a user name is 80 characters, on mysql the maximum length of a user name is 32 characters.
Example 7: Application Session¶
Recording the session ID. This example is useful tracking data changes made in multiple page request in a single session of a web application.
{
"audit_columns": [
{
"column_name": "audit_ses_id",
"column_type": "int(10) unsigned",
"expression": "@audit_ses_id"
}
]
}
When retrieving the session you must set the user defined variable @audit_ses_id
in MySQL from your PHP application. See Setting User Defined Variables in MySQL for examples of setting user defined variables in MySQL.
Example 8: End User¶
Recording the user ID. This example is useful for recording the end user who has modified the data using your PHP application.
{
"audit_columns": [
{
"column_name": "audit_usr_id",
"column_type": "int(10) unsigned",
"expression": "@audit_usr_id"
}
]
}
When retrieving the session and when signing in or off you must set the user defined variable @audit_usr_id
in MySQL from your PHP application. See Setting User Defined Variables in MySQL for examples of setting user defined variables in MySQL.
The Additional SQL Section¶
The additional SQL section specifies additional SQL statements that are placed at the beginning of the body of each created audit trigger.
Example¶
This example show how to set the variables @audit_uuid
and @audit_rownum
mentioned in sections Example 4: Database Session and Example 5: Order.
{
"additional_sql": [
"if (@audit_uuid is null) then",
" set @audit_uuid = uuid_short();",
"end if;",
"set @audit_rownum = ifnull(@audit_rownum, 0) + 1;"
]
}
The Tables Section¶
The tables sections holds an entry for each table in the data schema
. New tables are automatically added to the tables section and obsolete tables are automatically removed from the tables section when your run PhpAudit with the audit
command.
Foreach table in the table section there are three fields:
audit
The audit flag. A boolean indication auditing is enabled or disabled.true
Recording of an audit trail for this table is enabled.false
Recording of an audit trail for this table is disabled.null
Recording of an audit trail for this table is not specified. Each time your run PhpAudit with theaudit
command PhpAudit will report that a new table has been found.
alias
An alias for the table. This alias must be unique and will be used in the names of the audit trigger for this table. If you don’t specify a value PhpAudit will generate automatically an alias when auditing is enabled.skip
An optional variable name. When the value of this variable is not null the audit trigger will skip recording data changes.
When you disable recording of an audit trail of a table the audit triggers will be removed, however, the audit table will remain in the audit schema
.
Examples¶
Example 1: No audit trail¶
No audit trail will be recorded for table TMP_IMPORT
.
{
"tables": {
"TMP_IMPORT": {
"audit": false,
"alias": null,
"skip": null
}
}
}
Example 2: Audit trail¶
An audit trail will be recorded for table FOO_USER
.
{
"tables": {
"FOO_USER": {
"audit": true,
"alias": "usr",
"skip": "@audit_skip_foo_user"
}
}
}
When user defined variable @audit_skip_foo_user
in MySQL is set no audit triggers will record data changes. In the SQL code below updating column usr_last_login
will not be recorded.
set @audit_skip_foo_user = 1;
update FOO_USER
set usr_last_login = now()
where usr_id = p_usr_id
;
set @audit_skip_foo_user = null;