Auditing the Database
Database level auditing is available for Suite. This type of auditing is performed on the database side, completely independent of the application. All transactions in the database are logged in auditing tables by database triggers. To activate database auditing, scripts provided by Mitratech must be run to create the auditing tables and triggers in the database. Once the tables and triggers have been created, the database will log any insert, update, or delete transactions against the audited tables. All tables that store user-entered data are audited; only some system tables—such as those that store information about most recently accessed items, searches, etc.—are not audited.
Audited Tables
The following tables support auditing:
- CurrentID
- Document_ID
- Lobby_Audit
AuditTransaction Table
The auditing scripts create a table called AuditTransaction, which tracks all transactions against the audited tables. The AuditTransaction table contains the following fields:
- AuditTransaction_ID – Unique identifier for the transaction.
- TransactionType – Type of transaction (I = insert, U = update, D = delete).
- TransactionUser – Login name of the user from the application or “Unknown” if from a source other than a Suite application.
- TransactionDate – Date of the transaction.
- TableName – Name of the table involved in the transaction.
Triggers
The auditing scripts also create triggers and auditing tables for each audited table. The auditing tables are in the format <TableName>_Audit and contain the following fields:
- old_<FieldName> – The previous value of the updated or deleted field.
- new_<FieldName> – The new value of the updated or inserted field.
- Transaction_ID – Unique identifier for the transaction from the AuditTransaction table.
- TransactionType – Type of transaction (I = insert, U = update, D = delete).
- TransactionUser – Login name of the user from the application or “Unknown” if from a source other than a Suite application.
- TransactionDate – Date of the transaction.
Example
First, a user creates a new address record, which causes the following to happen in the database:
- A new record is created in the Address table.
- A new record is created in the AuditTransaction table with the following values:
AuditTransaction_ID: 201
TransactionType: I
TransactionUser: Jim
TransactionDate: 2004-10-20 14:57:04.483
TableName: ADDRESS
- A new record is created in the Address_Audit table with the following values:
old_Address_ID: NULL
new_Address_ID: 10035
old_Description: NULL
new_Description: Jim’s Home Address old_Address1: NULL
new_Address1: 1234 Main Street
...
TransactionType: I TransactionUser: Jim
TransactionDate: 2004-10-20 14:57:04.483
Then, the user updates the address record, changing “1234 Main Street” to “101 Second Street”, which causes the following to happen in the database:
- The record in the Address table is updated.
- A new record is created in the AuditTransaction table with the following values:
AuditTransaction_ID: 202
TransactionType: U
TransactionUser: Jim
TransactionDate: 2004-10-20 14:59:05.172
TableName: ADDRESS
- A new record is created in the Address_Audit table with the following values:
old_Address_ID: 10035
new_Address_ID: 10035
old_Description: Jim’s Home Address
new_Description: Jim’s Home Address
old_Address1: 1234 Main Street new_Address1: 101 Second Street
...
TransactionType: U
TransactionUser: Jim
TransactionDate: 2004-10-20 14:59:05.172
Finally, the user deletes the address record, which causes the following to happen in the database:
- The record in the Address table is deleted.
- A new record is created in the AuditTransaction table with the following values:
AuditTransaction_ID: 203
TransactionType: D
TransactionUser: Jim
TransactionDate: 2004-10-20 15:01:01.134
TableName: ADDRESS - A new record is created in the Address_Audit table with the following values:
old_Address_ID: 10035
new_Address_ID: NULL
old_Description: Jim’s Home Address
new_Description: NULL
old_Address1: 1234 Main Street
new_Address1: NULL
...
TransactionType: D TransactionUser: Jim