security

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

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.

1. 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.
2. 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.
3. 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.

Powerful and simple mechanism of data controller URL parameters allows easy manipulation of the page behavior in Code On Time web applications. For example, consider Products page at http://northwind.cloudapp.net/pages/products.aspx. If you navigate to the page and login as admin/admin123% then you will see the following screen with a list of products.

If you want to navigate to a specific product then try the following link. The same page will open in edit mode on the product with the primary key equal to 7.

http://northwind.cloudapp.net/pages/products.aspx?ProductID=7&_controller=Products&_commandName=Edit&_commandArgument=editForm1

Data controller parameters offer a simple and powerful method of affecting user interface presentation. Couple that with robust Access Control Rules implementation and your web app will provide an excellent and secure mechanism of navigation to specific records.

Sometimes you may want to prevent any possibility of external commands sent to your application via URL parameters unless the commands were initiated by the web application itself or an external “friendly” source.

Enabling URL Hashing

Unlimited edition of Code On Time offers a new feature called URL Hashing, which is available as a component of EASE (Enterprise Application Services Engine). The purpose of URL Hashing is to ensure that only encrypted commands can be passed in the URL of your web application.

Start the web application generator, select your project and click Next button a few times until your reach the Features page. Enable URL Hashing under EASE Configuration.

Proceed to generate the project.

Navigate to any page of your web application displaying data and try passing any parameter in the URL. For example, if you are looking at Products.aspx page then change the URL in the address bar of the browser to Products.aspx?AnyParam=Hello and hit enter key to navigate to the page.

You will see the following response.

All applications pages are now protected and will not allow inclusion of any URL parameters.

Internal URL Parameters

Your web application may be using URL parameters for its own purpose. For example, configure a Navigate action in the Northwind sample as explained next.

Select your project on the start page of the web app generator and click Design. Select Products data controller on All Controllers tab and activate Action Groups page.

Select action group ag1 with the scope of Grid and switch to Actions tab.

Add a new action with the following properties. Note that Command Argument must be entered without line breaks.

The value of command argument instructs the application to navigate to the current page that hosts the data controller view and pass the ProductID of selected row in the URL. The other URL parameters will force the data controller to open the specified product in editForm1 in Edit mode.

Save the new action, exit the Designer and generate your project.

Navigate to Products page and select the context menu of a product row.

Your browser will navigate to the currently active page with the URL that looks as follows. Notice that there is a parameter “_link” with the cryptic looking value in the address bar of the browser.

URL Hashing mechanism embedded in your application only allows this particular parameter and demands that the value of “ _link” parameter is encrypted.

Alter any portion of the parameter or add any additional URL parameters and the request will fail to display the page with the same HTPP error code 403 presented above.

External URL Parameters

Sometimes you may need to pass URL parameters from an external web application. If URL Hashing feature is enabled then this task becomes impossible unless you encrypt the URLs passed by external web application.

The implementation of encryption can be found in the StringEncryptorBase class of your application source code. The partial code below shows encryption key (Key) and initialization vector (IV). Both properties are passed by methods Encrypt and Decrypt as arguments to the default implementation of Advanced Encryption Standard (AES) available in Microsoft.NET Framework.

C#:

public class StringEncryptorBase
{
    
    public virtual byte[] Key
    {
        get
        {
            return new byte[] {
                    253,
                    124,
                    8,
                    201,
                    31,
                    27,
                    89,
                    . . . .
                    153};
        }
    }
    
    public virtual byte[] IV
    {
        get
        {
            return new byte[] {
                    87,
                    84,
                    163,
                    98,
                    205,
                    . . . .
                    112};
        }
    }
    
    public virtual string Encrypt(string s)
    {
        . . . . . . 
    }
    
    public virtual string Decrypt(string s)
    {

        . . . . . .
    }
}

VB:

Public Class StringEncryptorBase

    Public Overridable ReadOnly Property Key() As Byte()
        Get
            Return New Byte() {253, 124, . . . ., 153}
        End Get
    End Property

    Public Overridable ReadOnly Property IV() As Byte()
        Get
            Return New Byte() {87, 84, 163, 98, . . . ., 112}
        End Get
    End Property

    Public Overridable Function Encrypt(ByVal s As String) As String
        . . . . .
    End Function

    Public Overridable Function Decrypt(ByVal s As String) As String
        . . . . .
    End Function
End Class

Copy this class to the external application and make sure to encrypt the URLs that are passed to a Code On Time web application with URL Hashing enabled.

You may also want to change the key and initialization vector. The default values are hard-coded and shared by all applications generated with Unlimited edition of Code On Time.

Conclusion

URL Hashing is always performed for History and permalinks. If the application is generated without URL Hashing enabled then the permalinks are simply encoded with base-64 encoding to mask the nature of URL parameters.

Advanced encryption with hash code validation will be performed on links create with View Details command and on results of any actions with Command Name set to Navigate.