Tracking of user activities is a frequent requirement in many line-of-business applications. Knowing when a particular database record has been created, as well as who changed it is crucial in many industries.
Application developers rely on a proven set of techniques to satisfy data auditing requirements. They introduce data auditing columns in relevant database tables and utilize event logs to keep a journal of the system events. Values of auditing columns are updated by the application.
If the field name contains words “create”, “modified” or “update” , it’s purpose is probably to track user actions.
Examples
Consider customer table of Sakila database used to train MySQL developers. Field create_date tells when the record was created. Field last_update indicates when the customer record has changed.
If you generate an app based on this database then you will find out that additional manual configuration will be required to prevent users from entering values directly in the input fields and to ensure that fields are updates as intended. Manual configuration of auditing fields is described at /Documents/CB-CreatedBy_CreatedOn_ModifiedBy_ModifiedOn.pdf.
Unlimited edition of Code On Time offers a powerful option that makes configuration of auditing fields virtually effortless.
Select your project on the start page of the code generator and choose Settings action.
Select Features and activate EASE Configuration section.
Click on the “here” link under Audit Field Map to create sample map entries. Change the entries as shown below or in the picture.
Modified By User Name =
Modified By User ID =
Modified On Date = last_update
Created By User Name =
Created By User ID =
Created On Date = create_date
Click
Finish and regenerate the project. Try to update existing records or create new ones - you will notice that users are not able to change the auditing fields directly, and that values of the fields are processed automatically.
Consider another example.
Almost all tables in the sample Adventure Works database are provided with ModifiedDate column. There are 99 tables and views in the R2 release of this popular training database used to illustrate the capabilities of Microsoft SQL Server 2008.
Here is how the audit field ModifiedDate look is presented if you generate a brand new Adventure Works application.
Change the Audit Field Map as follows:
Modified By User Name =
Modified By User ID =
Modified On Date = ModifiedDate
Created By User Name =
Created By User ID =
Created On Date =
Generate the app and the same screen will look differently. Try editing records and observe that the field value is changed accordingly.
Understanding Audit Field Map
Audit Field Map associates predefined “logical” field names with the patterns of actual field names of the application database tables and views.
The “logical” field names are self-explanatory. If your application does not require a specific type of auditing then do not enter a pattern matching the logical audit field or delete the logical field from the map.
The pattern is treated as a regular expression. If you are consistently using the same field names for auditing purposes then enter the full field names as patterns. If there are variations of the fields conceptually playing the same audit role then separate the full names or name fragments with the symbol of “|” (do not include double quotes).
Let’s build an application with the complete set of audit fields in at least one database table.
Start by creating a Web Site Factory application using the Northwind Sample.
Locate the Products page in the generated web application and begin editing a product record. Here is our version of the Products page.
Modify the Products table in SQL Server Management Studio by adding the six audit columns that follow right after the Discontinued column in the next screenshot. Allow null values in the new columns and save the changes.
Code generator does not know that the database has changed. We need to refresh the application baseline.
Select the project name on the start page of the code generator and choose Refresh action.
Toggle the check box next to the name of the data controller Products and click OK button.
Generate the application and select a product record one more time. New fields are now available and clearly require additional configuration.
Select the project on the start page of the code generator and choose Settings. Proceed to Features | EASE Configuration and enter the following Audit Field Map.
Modified By User Name = ModifiedByUserName
Modified By User ID = ModifiedByUserId
Modified On Date = ModifiedOnDate
Created By User Name = CreatedBy
Created By User ID = CreateId
Created On Date = CreatedOn
Generate the web application and edit the exact same record.
If you compare the previous screen with the next one then you will notice that we are left with the four visible audit fields. The two fields designed to capture user IDs are not visible. Both CreateId and ModifiedByUserId are hidden from the application end users. The rest of the new audit fields are blank.
Click OK button to save the record.
Start editing the same record one more time. If you are signed in as user admin then your will see “admin” in the fields Created By and Modified By User Name.
Notice that both field names are rendered as links. If you click on a link then the default email software installed on your computer will be activated with the user’s email in the “To” input of the new message.
The physical ID of the user has been captures as well.
We suggest that you experiment edited data under different user identities to see the audit processing in action.
If you start creating new records then the audit fields will be prepopulated right away.
Future Audit Enhancements
The impressive ease of EASE Auditing will not stop here.
We are working on adding three major auditing enhancements that will become available in February of 2012.
- Access By/On columns will allow tracking the event of the web application user selecting a master record on the page. Typically users are looking for a record and then drill in the details. This moment in time and the user identity will be captured when needed. A perfect example of such event is a customer service representative accessing a customer account. Nothing has been changed but the fact of a person looking at the data is captured.
- Deleted By/On columns will help implementing a “soft delete”. The application will detect the presence of such fields and will convert “Delete” action into “Update” of the field values. The application will also filter out the “deleted” rows. Many businesses are now trying to avoid a loss of important business information and “soft delete” is a popular technique.
- Code On Time web applications have unique knowledge of the data and actions that pass from the client to the server. The upcoming release of Dynamic Access Control List will also introduce an automatic logging of all user actions as a part of EASE Auditing.