Security
Logging Data Access

Requirements for high-security web applications may require the logging of user credentials when data is accessed. Let’s create two fields, AccessedBy and AccessedOn, and use SQL Business Rules to automatically update these fields.

Adding AccessedBy and AccessedOn Columns

Start SQL Server Management Studio. Connect to your database. In the Object Explorer, right-click on Databases / Northwind / Tables / dbo.Customers node, and press Design.

Design context menu option for Customers table in Northwind database in SQL Server Management Studio.

Add two columns to the table:

Column Name Data Type Allow Nulls
AccessedOn datetime true
AccessedBy nvarchar(50) true

Save the table.

Switch back to Code On Time web application generator. Refresh the project.

Refresh the Code On Time web application project to bring in the new columns into the web app.

Configuring the Fields

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on Customers / Fields / AccessedOn node.

AccessedOn field under the Customers controller in the Project Explorer.

Change the following properties:

Property Value
Values of this field cannot be edited true
Data Format String g

Press OK to save the field. Double-click on Customers / Fields / AccessedBy node.

AccessedBy field under the Customers controller in the Project Explorer.

Mark the field as read-only:

Property Value
Values of this field cannot be edited true

Press OK to save the field. Right-click on Customers / Business Rules node, and press New Business Rule.

New Business Rule context menu option for Customers controller.

Give this rule the following properties:

Property Value
Type SQL
Command Name Select
View editForm1
Phase Execute
Script
set @AccessedOn = getdate()
set @AccessedBy = @BusinessRules_UserName

Seeing the Results

On the toolbar, press Browse. Navigate to the Customers page, and select a customer. The Accessed On and Accessed By fields will be appear on the form with updated values.

AccessedOn and AccessedBy fields are populated in the user interface.

However, if you check the records in the database, the columns will not be updated.

AccessedOn and AccessedBy fields are not populated in the database.

This is due to the way that the client library works – when data is selected from the database, the business rule adds the values to AccessedOn and AccessedBy before they are displayed in the user interface. If the user tries to save the values, the client library will compare the data requested from the server (with appended values) and any new values. Because the user has not changed these fields, the client library assumes that the values are unchanged and does not update the record.

There are two methods of updating the field value:

  1. Update the field value when the user selects the record.
  2. Update the field value after the user updates the record.

Method 1: Update When User Selects

Switch back to the Project Designer. The business rule will still be open in the Browser window. Change the Script property as follows:

Property New Value
Script
set @AccessedOn = getdate()
set @AccessedBy = @BusinessRules_UserName

update Customers
set AccessedOn=@AccessedOn, AccessedBy=@AccessedBy
where CustomerID=@CustomerID

Press OK to save the business rule. On the toolbar, press Browse.

Navigate to the Customers page, and select a record. The Accessed On and Accessed By fields will be populated in the user interface.

AccessedOn and AccessedBy fields are populated in the web application.

The database record will be updated as well.

AccessedOn and AccessedBy fields are also populated in the database.

Method 2: Update When User Updates

Make sure you are not using the business rule from Method 1. Switch back to the Project Designer. Right-click on Customers / Business Rules and press New Business Rule.

New Business Rule context menu option for Customers controller in the Project Explorer.

Give this business rule the following properties:

Property Value
Type SQL
Command Name Update
Phase Execute
Script
update Customers
set AccessedOn=@AccessedOn, AccessedBy=@AccessedBy
where CustomerID=@CustomerID

Press OK to save. On the toolbar, press Browse.

Navigate to the Customers page. Select a customer, and the fields will be populated in the user interface.

AccessedOn and AccessedBy fields populated in the user interface.

However, the database will not be updated.

AccessedOn and AccessedBy fields not populated in the database.

Press Edit, change a field value, and press OK to save.

Edit, change, and save the customers record in the web application.

The column values will be updated only when the record is updated.

AccessedOn and AccessedBy fields have been populated in the database.