Projects:Audit Trail/Functional Documentation
Contents |
Overview
Currently Openbravo ERP has basic support for audit trailing, allowing only to maintain the user and time that created each record and the user and time for the last modification of each record.
Purpose
The purpose of this project is to enhance audit trail support, to allow in Openbravo ERP to keep a complete history of information in the system.
Scope
In order to have a more detailed control, it would be needed to keep track of all the changes done in the database (configurable by System Administrator so he can decide which tables are tracked). That would include, registering for any change in any field the following information:
- Field that has been changed
- Old value
- New value
- Modification date
- User who did the modification
References
Design Considerations
Assumptions
Dependencies
Constraints
Glossary
Functional Requirements
User roles & profiles
The roles affected by this projects are:
- System Administrator: Who defines the tables to be audited.
- Standard users: Whose activities will be tracked.
- Administrator: Who will be able to view the audit track.
Business process definition
- System Administrator defines which are the tables that will be audited.
- He executes a process that creates the infrastructure to support auditing.
- From this point regular users activity starts to be logged.
- Administrator users can see the history for a record or a complete table. This history includes who modified the record and the window/process the record was modified from, in this way it is easily differentiable whether a record was manually modified or it was modified by a process executed by a user.
User stories
Functional requirements based on business processes
Audit trail management
To support historical data log it is necessary to:
- Configuration: Add a new check in AD tables (isFullyAudited). Information in tables marked as fully audited will be automatically audited for any change (insert, update, delete). After setting a table to be audited it will be necessary to execute a process to generate triggers for these tables, this process should also be run whenever any of these tables is modified.
Visualization
Once the audit information is stored in database, it is necessary to be able to query it. The queries that should be possible to perform would be:
| No. | Query | Description | Type | Priority |
| 1 | Single record history | All the modifications performed in a single record along the time. The record should be shown in the way it was after each of these modifications. It should be also shown the modification author and the place the modification was done from (process or window) | Record | High |
| 2 | Deleted records in a table | A list of the records that were deleted from a table. From this view it would be nice to be able to navigate to the deleted record history. | Table | High |
| 3 | Table history for a concrete parent. | Modifications in all the child records for a parent, for example all the modifications in the lines of an invoice. | Table | High |
| 4 | Table history. | All the modifications performed in a table. A history of all the records in the same table. | Table | Medium |
| 5 | User table modifications. | Which modification where performed by a single user in a table. | Table | Medium |
| 6 | User record modifications. | Which modification where performed by a single user in a table. | Record | Medium |
| 7 | Record snapshot. | How a record was at a concrete time. | Record | Low |
| 8 | Table snapshot. | How a table was at a concrete time. | Table | Low |
User Interface Mockups
Implementation Phases
First Phase
This section describes the selected features which will be implemented in the first phase of the project. Each selected feature will be linked to the requirements stated above.
Logging of audit information
The logging/recording of audit information will be completely implemented in the first phase of the project as it is the basis for all further items.
Visualization
- A generated window will be implemented which shows all recorded audit information and offers extensive filtering based on the normal filtering/searching capabilities. This generated window allows to view the data in a way as described in the visualization queries labeled: 4,5 and 6 by filtering the generated window by the needed columns (i.e. table or user).
- A single popup will be implemented to more easily access the audit information associated to a single record and the list of deleted records for a single table/tab.
- Record History view: This view allow viewing the recorded audit information (history) of a single record. This will implement the query described above as number 1, with one change: the view will display one line per changed column, and not the state of each record after the modification. Reason is easier display of which columns really changed without the need for duplicating the data for all unchanged columns in the display. This view also allows switching to the 'Deleted records view' described below. TBD: insert screenshot when available.
- Deleted Records view: This view will be linked from the 'Record History' view and shows all deleted records belonging to one table and/or tab (Detail ToBeDecided). The deleted rows records will be shown in a grid where one line in the grid corresponds to one deleted record. For this purpose the audit information (which has one entry per column of a deleted record) will be reassembled to be shown in this more useful format. This view will implement the query 2 as described above and partially query 7, as the view will show the state of a record at time of its deletion (but not at an arbitrary time). The view will allow navigation from a selected deleted record to the full history of this record in the 'Record History view'. TBD: insert screenshot when available
- Rendering of references: If a record contains a field which is a reference (link to another table, foreign key) then the raw audit information just saves the internal, technical foreign key value. Displaying this raw data in a user view is not meaningful. As outlined in the discussion items below several possibilities exist to convert this into a nicer user visible value (example show customer name of a sales order instead of the internal 32char customer uuid. For the first phase the last implementation variant (Show current reference will be implemented). If the reference target record still exists its (current) identifier will be shown, if it does not exists a marker like '(deleted)' will be shown.
Future Work
When matching the views implement in phase one against the queries outlined above the following queries are not addressed or not completely addressed so far:
- 3, Table history for a concrete parent.
- 7, Record snapshot (show the state of a record from a given point in time
- 8, Table snapshot (show the state of a table a a given point in time)
Referring to the rendering of references in a future phase the second option as described in the discussion items below could be implemented. This would (if the needed audit data of the reference target table(s) is available) assemble the historically correct identifier value.
Technical Requirements
- Add a table to maintain the audit trail.
- Add a process that creates the infrastructure for audited tables. This infrastructure will consists on the triggers to be executed when the original table is modified populating the audit table.
- UI visualization for this info audit trail, including:
- A tab at the same level of the audited tab to see the whole history.
- A subtab to see the history of the current record.
- Manage properly this infrastructure by DBSM. As the audit infrastructure is calculated from the model, it shouldn't be exported when the rest of the database is exported.
Non-Functional Requirements
Open Discussion Items
- ALO: As the trail management is going to be done by triggers and DBSM disables all the triggers before doing any data insertion/update, actions performed by DBSM will not be audited. Is this a major issue, or is it ok not to audit this info?
- ALO: Openbravo ERP has some tables that are generated by processes from the information in other tables (for example accounting is stored in fact_acct), and some other ones that are used for logging processes (for example ad_note table maintains accounting log). If these tables are audited the audit table can grow very quickly with useless information. We should decide whether this kind of table is auditable or not. Maybe the solution is to allow audit, but discourage it through documentation, so technically is possible but we don't recommend it.
- ALO: To keep the information about the time when an action was performed it would be nice to do it in a timestamp oracle data type. Currently DBSM only supports date, this means that currently it is only possible to save up to seconds but not second fractions. Is this a real issue, or is it enough with seconds?
Closed Discussion Items
- ALO: Managing audit by triggers has a problem for row deletion. Database does not have information about the user performing the action and there is no way of accessing the Openbravo ERP session. Therefore it is not possible to audit properly this action. It would be possible to do this tracking in WAD windows and in DAL and to create a PL procedure to be called before each deletion, but this means that direct deletes on DB wouldn't be tracked. There is a complete list of PL executing DELETE statements in Technical specifications document. This also would occur to deletes done in WAD or DAL affecting to other tables by a cascade delete foreign key constraint.
- The way to solve this problem is by setting the user that is performing the action in a session table that can be read from database. So when the connection is get this is set, then the trigger, regardless the action, can read the info.
- ALO: Should we maintain audit trail in separate tables, one per audited table, or would it be better to do in a single one. Doing in a single one requires less infrastructure, but it makes harder to query and it could increase in size very quickly.
- Having all the audit trail in a single table seems to be a better approach, since it facilitates dramatically the maintenance and synchronization with the audited tables structure.
- ALO: When visualizing historic data, should references be displayed in the way they were at the time the record is view in? For example if we see an invoice how it was 1 year before, and this invoice has a reference to a business partner, should the business partner identifier appear as it is today or as it was 1 year ago. There are (at least) 3 possible solutions:
- When storing the saving the audit info, save not only the FK value but also the referenced identifier. This approach has 2 problems:
- It would be necessary to query for the identifier, this could produce performance problems.
- Identifier can change depending on the session language, but at this point it should be stored just in one language. Which one?
- When showing the information for references try to obtain the referred data from audit trail info of the referred table. This option only would work in case the referred table is also audited.
- Show current reference.
- For the first phase the current reference will be shown if it can be calculated (if the records and all reference target records still exists)
- When storing the saving the audit info, save not only the FK value but also the referenced identifier. This approach has 2 problems: