Business Rules/Logic

Code On Time web applications support out-of-the-box reporting capabilities that require zero programming.

Standard Report Actions

All data views offer four standard actions that yield a different output.

• Action “ReportAsPdf” will render the data presented to end users as a Adobe PDF document. The shorthand action “Report” will produce the same result. This type of report requires a compatible software installed on the client computer to view and print the report output. You can download free Adobe Acrobat Reader at at http://get.adobe.com/reader.
   
• Action “ReportAsImage” creates a TIFF image file, which requires a compatible software installed on the client. TIFF format supports multiple pages and is a perfect alternative to PDF. The quality of output in PDF and TIFF formats is equivalent.
   
• Action “ReportAsWord” renders a report as Microsoft Word document with a high-quality output. In some instances the output is less precise then the output produced by the action “ReportAsImage” and “ReportAsPdf”.
   
• Action “ReportAsExcel” produces a Microsoft Excel spreadsheet that offer a lesser quality output due to rendering restriction of the popular spreadsheet software.

If a user  requests a “Report...” action then a report is rendered on the server with the help of Microsoft Report Viewer. The output is streamed to the client browser. Typically a prompt is displayed before a compatible installed software viewer will be activated. Users also have an option to save the output locally. If a viewer is not installed on the client computer then the prompt to save the file is the only option.

Code On Time web applications execute various server calls off-band to provide smooth Web 2.0 user experience. Modern web browsers make sure to prevent all sorts of popups initiated by the scripts embedded in the web pages. The client library makes use of correct techniques to process reports on the server without causing automatic activation of popup blockers.

Custom Report Action Handlers

Code On Time web applications created with version 6.0.0.19 or higher allow developers to perform custom processing of reporting actions in business rules. There are several reasons that may require an execution of  custom code that must precede or override the standard report rendering logic:

• The data must be “prepared” before a report is rendered.
• The report request must be logged.
• An external report rendering engine is available. The custom code will redirect the report request to such an engine.
• A special report preview page needs to be displayed.
• A custom report building code must execute. The custom code replaces the standard report rendering logic.

Create a business rule for your data controller and implement methods to handle the corresponding “Report...” actions when you need to override the standard report processing.

Example

Generate a Northwind sample using Code On Time web application generator.

Select the project name on the start page of the code generator and click Design.

Select Controllers tab in the Project Explorer and enter CustomerBusinessRules in the Handler property of the Customers data controllers. Click OK to save the changes.

Expand Actions node of the Customers data controller in Project Explorer, right-click Actions and create a new action group with the Scope of Action Column.

Add a new action in the newly created action group, set its Command Name to Report, and Command Argument to _blank.

Here is the sub-tree of the Customers data controller with the  Report action selected.

Click Exit on the Designer tool bar and generate your application.

Select the project name on the start page of the web application generator and choose Develop. Visual Studio or Visual Web Developer will start.

Locate the file ~/App_Code/Rules/CustomerBusinessRules.cs(vb) and enter the following code.

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;
using System.IO;

namespace MyCompany.Rules
{
    public partial class CustomersBusinessRules : MyCompany.Data.BusinessRules
    {
        [ControllerAction("Customers", "Report", "_blank")]
        public void RedirectToCustomReport(string customerId, string companyName)
        {
            // Redirect user to another URL
            Result.NavigateUrl = String.Format(
                "~/Pages/Customers.aspx?CustomerID={0}&_controller=Customers" + 
                "&_commandName=Select&_commandArgument=editForm1",
                customerId);
        }

        [ControllerAction("Customers", "ReportAsImage", "")]
        public void GenerateCustomReport(string customerId, string companyName)
        {
            PreventDefault();
            // return the same image in response to all "Report..." commands
            Context.Response.Clear();
            Context.Response.ContentType = "image/jpeg";
            Context.Response.AddHeader("Content-Disposition", 
                String.Format("attachment;filename={0}.jpg", customerId));
            byte[] reportData = 
                File.ReadAllBytes(@"C:\Users\Public\Pictures\Sample Pictures\Koala.jpg");
            Context.Response.AddHeader("Content-Length", reportData.Length.ToString());
            Context.Response.OutputStream.Write(reportData, 0, reportData.Length);
        }
      
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Imports System.IO

Namespace MyCompany.Rules
    
    Partial Public Class Customers
        Inherits MyCompany.Data.BusinessRules

        <ControllerAction("Customers", "Report", "_blank")> _
        Sub RedirectToCustomReport(ByVal customerId As String, ByVal companyName As String)
            ' Redirect user to another URL
            Result.NavigateUrl = String.Format( _
                "~/Pages/Customers.aspx?CustomerID={0}&_controller=Customers" + _
                "&_commandName=Select&_commandArgument=editForm1",
                customerId)
        End Sub

        <ControllerAction("Customers", "ReportAsImage", "")> _
        Sub GenerateCustomReport(ByVal customerId As String, ByVal companyName As String)
            PreventDefault()
            'return the same image in response to all "Report..." commands
            Context.Response.Clear()
            Context.Response.ContentType = "image/jpeg"
            Context.Response.AddHeader("Content-Disposition", _
                String.Format("attachment;filename={0}.jpg", customerId))
            Dim reportData As Byte() = _
                File.ReadAllBytes("C:\Users\Public\Pictures\Sample Pictures\Koala.jpg")
            Context.Response.AddHeader("Content-Length", reportData.Length.ToString())
            Context.Response.OutputStream.Write(reportData, 0, reportData.Length)
        End Sub
    End Class
End Namespace

The first method RedirectToCustomReport will use the ID of the selected customer to compose a URL relative to the application. The URL will open in a new browser window and will force the application to select a customer with the specified ID.

The new window will open if your have entered the command argument as “_blank” when defining the action in Project Designer. If action command argument has been left blank then the new URL will replace the page in the web browser. Users will have an option to return to the previous page using the browser’s Back button.

The screen shot of the Customers page prior to execution of our Report action is shown next.

If you click on the Report button then a new browser window will open. You can see the address bar reflecting the ID of the selected customer.

Our web application is using its own capabilities to present the data with the help of data controller URL parameters. You can redirect the report action to a generic web request handler or to a web-enabled report server such as Crystal Reports or Microsoft SQL Server Reporting Services.

The second method GenerateCustomReport takes control over the report rendering completely.

First, the method cancels out the default reporting logic at the very beginning by calling PreventDefault.

Next it reads the file C:\Users\Public\Pictures\Sample Pictures\Koala.jpg and streams it out. In a real-world application you can produce any sort of output using a custom code.

The method uses the customer ID to assign the file name to the output.

If you are reproducing this sample on a Windows 7 computer then there is not need to change the code . Otherwise change the path to the image file accordingly.

Run your web app, navigate to Customers page and select a customer.

Choose Report | Multipage Image on the action bar above the list of customers.

The custom method will execute and you will see the following prompt to download the file AROUT.jpg if you browsing with IE 9.

Click Open and the default image viewer will start.

If you select any other customer and choose the same action bar option then exactly the same image will be downloaded but the file name will reflect the actual primary key of the selected customer.

In a real-world applications you will likely stream other formats of output such as PDF or custom Microsoft Office documents using 3rd party reporting software.

Web app generator Code On Time introduces Access Control Rules, the first component of EASE (Enterprise Application Services Engine). EASE components simplify implementation of enterprise-class features in line-of-business web applications created with Code On Time and require little or no programming at all.

Introduction

Why do we need access control rules?

Many line-of-business applications start with a simple spreadsheet that allows performing data analysis or calculations to satisfy a specific business requirement. The spreadsheet turns in an application as soon as business users realize that the spreadsheet must accept data input from multiple users. A database is set up and application user interface is built on top of it.

In a few instances the entire database contents may be available to all authorized application end users. In most situations there is a need to create a set of restrictions that would separate slices of data that are available to individual users or users in a given business role.

For example, a web application administrator may be authorized to see the entire list of customers. A sales person will likely be allowed to see only the customers that he or she has a relationship with.

Another example is a line-of-business web application integrated with a content management system such as DotNetNuke or Microsoft SharePoint. The application data must be isolated by a portal or site name. Data created by all  CMS users is stored in the same database tables but under no circumstances shall the users see each other’s records.

Preparing Database for Access Control

A common approach to facilitate access control implementation is to add columns reflecting ownership of data. For example, you can implement User ID column in each table of your database.

If more than one user has a relationship with a data record then developers opt to implement a dedicated table linking a a data record with multiple users.

A typical relational database structure provides certain natural means of establishing access control based on database table relationships. It may be sufficient to implement just one ownership column. For example, consider the following snippet from the Northwind Sample.

Table Orders has a reference to an Employee. If a web application user is in the role of Sales then one can make the following assumptions:

• an employee shall see only his or her orders
• an employee shall see only customers that have matching orders placed by the employee
• a subset of order details are naturally visible to an employee when an order is selected
• if an employee is viewing the global list of order details then only orders of her customers shall be accessible.

The described access control rules can be implemented if we associate each Employees table record with a User ID.

Code On Time allows integrating Microsoft ASP.NET Membership in the generated applications.  User accounts and roles are stored in dedicated tables and user and role management APIs are already built in ASP.NET. Let’s incorporate ASP.NET Membership plumbing in the application database.

Change Employees table to implement two additional columns UserID and UserName. Notice that if you are developing with a database other than Microsoft SQL Server then you may need to choose a different types for UserID and UserName columns. For example, MySQL implementation of ASP.NET Membership uses int as the type of the membership user ID.

Start Code On Time and begin creating a new Web Site Factory project . On the Database Connection page of project wizard press on a button next to connection string input field.

Set the database connection string and press the Add button to create membership tables and stored procedures in the application database.

Continue application configuration until you reach the Data Controllers summary page in the project wizard.

Creating a Business Rules Class

Access Control Rules in Code On Time web applications are implemented as methods in  business rules classes. These methods can be shared by all data controllers of your application or designed to address the needs of a specific data controller.

We will consider both situations and will show the real-live examples of custom and shared access control rules.

We will start by creating a business rules class associated with Customers data controller.

Click on Start Designer  button and select Customers data controller on All Controllers tab of Project Designer.

Enter CustomersBusinessRules in Handler field under Business Rules section and click OK button to persist changes.

Click Exit button at the top of the screen and click Next to generate the project.

The implementation of business rules will require source code text editing and can be done in Notepad or a development tool such as Visual Studio or Visual Web Developer.

If you do not have a development tool on your computer then click “open” under Actions column of the Code On Time start page. 

Select App_Code\Rules\CustomersBusinessRules.cs(vb) file in Windows Explorer and open the file in Notepad.

If you do have a development tool listed above then simply click “develop” link to activate the development tool and open the same file in Solution Explorer.

The extension of the file depends on your programming language. Double click the file name to open the file in the editor.

Next we will show examples in both C# and Visual Basic demonstrating various way of implementing access control rules.

Challenges in Implementing Access Control Rules

Modern database software make it exceptionally easy to select data. The application developer writes a data selection statement in declarative language SQL. Such statements typically list the source tables, table columns, and table join instructions. 

Data selection statements are frequently enhanced with filters to present the data that users would like to see.

There is this pesky requirement to segment the data based on user identity or business role. A developer will have to incorporate access control filters in every single data selection statement and foresee all sorts of business requirements that may call for exceptions to some of the filters.

Because of that, developers end up writing their data selection statement as stored procedures persisted in the database. These stored procedures are fundamentally basic selection statements enhanced with numerous checks and conditions to ensure proper access control.

Code On Time applications create SQL selection statements on-the-fly and incorporate user-defined adaptive filters, search criteria, and sort order. Selection statements are enhanced with parameters to prevent any possibility of an injection attack that plagues many applications with hand-written SQL.

It is impossible to write a stored procedure that accepts an unknown number of filtering and sorting parameters to match on-the-fly SQL statements created by your Code On Time web application. Access Control Rules are designed specifically to address the need to apply access control restrictions to dynamic SQL statements.

Next we will show you several examples of Access Control Rules and will implement filtering based on user identity and business role.

Restricting Access by a Single Value

Consider the list of customers presented in the following screenshot. We can see a list of customers from many countries.

Let’s limit this list of customers to USA only and have this rule apply to all application users.

Enter the following method in the CustomersBusinessRules.cs(vb) and save the file.

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class CustomersBusinessRules : MyCompany.Data.BusinessRules
    {
        [AccessControl("Customers", "Country")]
        public void CountryFilterThatAppliesToEverybody()
        {
            RestrictAccess("USA");
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace MyCompany.Rules

    Partial Public Class CustomersBusinessRules
        Inherits MyCompany.Data.BusinessRules

        <AccessControl("Customers", "Country")> _
        Public Sub CountryFilterThatAppliesToEverybody()
            RestrictAccess("USA")
        End Sub
    End Class
End Namespace

The name of the method plays no role. Use you imagination to ensure that it will be easy to understand the purpose of the method in the future. The method is decorated with AccessControl attribute .

The first parameter of the attribute constructor indicates the data controller that will use this access control method. The second parameter indicates the field that will be filtered by the access control method.

If the data controller is requested to retrieve data then it will scan the list of methods of the associated business rules class and find those that are decorated with the matching AccessControl attribute. By default the data controller will assume that the method is designed to allow access to data. The data controller will invoke the method and if any calls to RestrictAccess are executed then every restriction will be incorporated in the access control filter of SQL data selection statement. In this instance the method will ensure that all users are restricted to see customers from USA regardless of their identity or business role.

Notice that the list of options available in the adaptive filter is considerably shorter. Any user-defined filters will be applied on top of the filters produced by Access Control Rules. Access control filter in the example is activated in all instances of requests to retrieve a list of customers.

Access control methods can utilize any sort of logic to decide if a restriction is applicable at any given moment. For example, a filter can prevent access to the list of customers during non-working hours. The following access control method will allow access to the list of customer from 9 AM though 5:30 PM. If the data is being accessed outside of this time period then a restriction by a non-exiting country is activated.

C#:

[AccessControl("Customers", "Country")]
private void CountryFilterThatAppliesToEverybody()
{
    DateTime today = DateTime.Now;
    DateTime workDayBegins = new DateTime(today.Year, today.Month, today.Day, 9, 00, 00);
    DateTime workDayEnds = new DateTime(today.Year, today.Month, today.Day, 17, 30, 00);
    if (workDayBegins <= today && today <= workDayEnds)
        RestrictAccess("USA");
    else
        RestrictAccess("*****");

}

Visual Basic:

<AccessControl("Customers", "Country")> _
Private Sub CountryFilterThatAppliesToEverybody()
    Dim today As DateTime = DateTime.Now
    Dim workdayBegins As DateTime = New DateTime(today.Year, today.Month, today.Day, 9, 0, 0)
    Dim workdayEnds As DateTime = New DateTime(today.Year, today.Month, today.Day, 17, 30, 0)
    If (workdayBegins <= today And today <= workdayEnds) Then
        RestrictAccess("USA")
    Else
        RestrictAccess("*****")
    End If
End Sub

Multiple AccessControl attributes can be applied to the same method.

If several methods with AccessControl attribute are discovered then the data controller will incorporate SQL data selection statement restrictions by concentrating them with “and” logic.

Restricting Access by Multiple Values

If more than one value must be used to filter out the data then simply call RestrictAccess method multiple times and pass the values that are compatible with the data type of the field specified in AccessControl attribute.

The following method will limit the list of customers to three specific IDs when presenting the list to users with roles other than Administrators.

C#:

[AccessControl("CustomerID", AccessPermission.Allow)]
protected void NonAdministrativeUsersAreAuthorizedToSeeOnlyThreeCustomers()
{
    if (UserIsInRole("Administrators"))
        UnrestrictedAccess();
    else
    {
        RestrictAccess("GREAL");
        RestrictAccess("OLDWO");
        RestrictAccess("THEBI");
    }
}

Visual Basic:

<AccessControl("CustomerID", AccessPermission.Allow)> _
Protected Sub NonAdministrativeUsersAreAuthorizedToSeeOnlyThreeCustomers()
    If UserIsInRole("Administrators") Then
        UnrestrictedAccess()
    Else
        RestrictAccess("GREAL")
        RestrictAccess("OLDWO")
        RestrictAccess("THEBI")
    End If
End Sub

This example is using UnrestrictedAccess method to indicate that the restriction does not apply to Administrators. Access control methods will only result in data filtering if at least one call to RestrictAccess has been made. You can use UnrestrictedAccess method to negate the result of the access restriction previously applied within the same method execution path.

Here is the slightly shorter version of the method restricting access to specific customer accounts for non-administrative users. If a user does not belong to the role Administrators, then no restrictions will be imposed on the the list of customers.

C#:

[AccessControl("CustomerID", AccessPermission.Allow)]
protected void NonAdministrativeUsersAreAuthorizedToSeeOnlyThreeCustomers()
{
    if (!UserIsInRole("Administrators"))
    {
        RestrictAccess("GREAL");
        RestrictAccess("OLDWO");
        RestrictAccess("THEBI");
    }
}

Visual Basic:

<AccessControl("CustomerID", AccessPermission.Allow)> _
Protected Sub NonAdministrativeUsersAreAuthorizedToSeeOnlyThreeCustomers()
    If Not UserIsInRole("Administrators") Then
        RestrictAccess("GREAL")
        RestrictAccess("OLDWO")
        RestrictAccess("THEBI")
    End If
End Sub

The screenshot shows the list of customers presented to a user with role Users.

Denying Access

The previous examples of access control rules are specifying AccessPermission.Allow value passed as a parameter of AccessControl attribute. This is default permission of access control rules and can be omitted.

There is always a possibility that a data access exception must be implemented. For example, you may need to prevent administrators from being able to access The Big Cheese customer from USA. The following method does just that.

C#:

[AccessControl("CustomerID", AccessPermission.Deny)]
public void ExceptionForAdministrators()
{
    if (!UserIsInRole("Administrators"))
        UnrestrictedAccess();
    else
        RestrictAccess("THEBI");
}

Visual Basic:

<AccessControl("CustomerID", AccessPermission.Deny)> _
Public Sub ExceptionForAdministrators()
     If (Not UserIsInRole("Administrators")) Then
        UnrestrictedAccess()
    Else
        RestrictAccess("THEBI")
    End If
End Sub

If both “allow” and “deny” access control rules are imposing restrictions at runtime, then the data controller will compose an access control filter such as the one below.

(List of  “Allow” restrictions) and Not (List of “Deny” restrictions)

Restricting Access via Parameterized SQL

Our examples are using static values to compose restrictions. In real world situations you may need to examine a user identity and figure the appropriate restriction value on the fly.

It may be impractical to invoke RestrictAccess method for each restriction due to a very large number of such restrictions.

You can solve both problems by incorporating SQL statements in the definition of AccessControl attribute.

For example, you may want to consider restricting non-administrative accounts to see only those customers that they have a relationship with. The following method makes an assumption that the user with User Name = ‘user’ is a sales person and shall see only customers matching Employee ID = 1.

The total number of records in the Customers table of Northwind database is 91. If you implement the method presented next then the user with the name “user” will only see 65 records. Make sure to comment out or delete any previously defined access control methods described above to prevent the cumulative effect of access control restrictions.

C#:

[AccessControl("CustomerID", Sql = 
    "select distinct CustomerID from Orders where EmployeeID = @EmployeeID")]
public void LimitUserToSeeOnlyHerCustomers()
{
    if (!UserIsInRole("Administrators"))
        if (Context.User.Identity.Name == "user")
            RestrictAccess("@EmployeeID", 1);
        else
            UnrestrictedAccess();
}

Visual Basic:

<AccessControl("Customers", "CustomerID", _
    "select distinct CustomerID from Orders where EmployeeID = @EmployeeID", _
    AccessPermission.Allow)> _
Public Sub LimitUserToSeeOnlyHerCustomers()
    If (Not UserIsInRole("Administrators")) Then
        If Context.User.Identity.Name = "user" Then
            RestrictAccess("@EmployeeID", 1)
        Else
            UnrestrictedAccess()
        End If
    End If
End Sub

The data controller will inject SQL statement defined in AccessControl attribute in the data selection statement.

The value of parameter @EmployeeID is assigned by the code in the access control method.

If you call method RestrictAccess with two arguments then the first argument is the name of the parameter in Sql statement of the attribute and the second argument is its value.

The data controller will compose a restriction that looks as follows.

“Customers.CustomerID” in (select distinct CustomerID from Orders where EmployeeID = @EmployeeID)

The expression in AccessControl attribute is inserted as-is. It is up to you to ensure that the expression is valid and will return a correct list of values.

Implementing Restrictions for Northwind Sample

Let’s implement access control rules in the Northwind sample that rely on columns User ID and User Name, implemented previously in this article.

We will make a few changes to the web application design.

Run Code On Time web application generator and select your project.

Click the Next button twice to reach Business Logic Layer page in the project wizard. Select “Generate a shared business rules class …” check box and continue pressing Next until you reach the summary of Data Controllers in your project.

Click Start Designer button to activate the project designer.

Select Employees data controller and activate Fields tab.

Select UserID field, activate Field tab, and change its Items Style to User ID Lookup under Lookup section.

Enter UserName=UserName in the Copy field right under the Items Style. This instruction will ensure that the value of UserName field from Membership Manager will be copied into Employees.UserName field.

Scroll down and modify the Security section if you want to ensure that only Administrators are able to assign/create users while editing the Employees record. The following configuration will put no restrictions on who can see the user name and will enable business users with role Administrators to change the user account associated with the employee.

Click OK and select this field one more time. Activate Data Fields tab.

Change both instances of the UserID data field to reference UserName field as an alias of UserID. Select each binding (data field) and edit Alias property under General section.

Bind UserID field to grid1 view to see the User Name associated with each record in Employees table when viewing the list of employees. Click New|New Data Field and proceed to create a data field (binding) linking field UserID to view grid1. Make sure to leave the field Category blank and choose UserName as the field’s Alias. Press OK.

Your list of data fields associated with field UserID will look as follows.

Select Controller:Employees link in the path of the Designer and select UserName field on the Fields tab.

Delete both bindings of UserName field to views editForm1 and createForm1. These bindings are shown prior to deletion in the next screenshot.

Exit the Project Designer and generate the project.

Sign-in as admin/admin123% and associate user account user with the employee with the last name of Davolio.

Open the CustomersBusinessRules.cs(vb) file in the text editor and delete any previously defined access control rules. Also, replace the base class with MyCompany.Rules.SharedBusinessRules. This will ensure that any global shared access control rules will be inherited in custom business rules associated with Customers data controller.

The new version of CustomersBusinessRules is presented next.

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class CustomersBusinessRules :
       MyCompany.Rules.SharedBusinessRules
    {
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace MyCompany.Rules

    Partial Public Class CustomersBusinessRules
        Inherits MyCompany.Rules.SharedBusinessRules

    End Class

End Namespace

Refresh the project tree in Solution Explorer if you are using Visual Studio or Visual Web Developer.

Open file ~/App_Code/Rules/SharedBusinessRules.cs(vb) in the editor.

This file implements MyCompany.Rules.SharedBusinessRules class. The data controller implementation of your web application will create an instance of this class when preparing to process requests from any data controllers of your project if a dedicated data controller is not available.

We will implement a collection of access control rules to perform consistent data selection restrictions based on user identity.

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class SharedBusinessRules : MyCompany.Data.BusinessRules
    {

        public object UserID
        {
            get
            {
                return System.Web.Security.Membership.GetUser().ProviderUserKey;
            }
        }

        [AccessControl("EmployeeID", Sql =
            "select EmployeeID from Employees where UserID = @UserId")]
        [AccessControl("CustomerID", Sql =
            @"select distinct CustomerID from Orders 
            inner join Employees 
            on Orders.EmployeeID = Employees.EmployeeID 
            where Employees.UserID = @UserID")]
        public void RestrictByEmployeeIdAndCustomerId()
        {
            if (!UserIsInRole("Administrators"))
                RestrictAccess("@UserID", UserID);
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace MyCompany.Rules
    
    Partial Public Class SharedBusinessRules
        Inherits MyCompany.Data.BusinessRules

        Public ReadOnly Property UserID As Object
            Get
                Return System.Web.Security.Membership.GetUser().ProviderUserKey
            End Get
        End Property

        <AccessControl(Nothing, "EmployeeID", _
            "select EmployeeID from Employees where UserID = @UserID")> _
        <AccessControl(Nothing, "CustomerID", _
            "select distinct CustomerID from Orders " & _
            "inner join Employees " & _
            "on Orders.EmployeeID = Employees.EmployeeID " & _
            "where Employees.UserID = @UserID")> _
        Public Sub RestrictByEmployeeIdAndCustomerId()
            If Not UserIsInRole("Administrators") Then
                RestrictAccess("@UserID", UserID)
            End If
        End Sub

    End Class
End Namespace

Paste the source code defined above in the ~/App_Code/Rules/SharedBusinessRules.cs(vb), save it and try it in action by signing in as user/user123%.

You will notice that this simple restriction affects uniformly Customers, Order Details, Employees, Employee Territories, and Customer Demographics.

Any data view that has either CustomerID or EmployeeID field will be automatically restricted if the current is not a member of Administrators role.

You can widen the reach of shared restrictions to Order Details if you simply add EmployeeID field to this data controller and mark it as Hidden.

The command text of Order Details data controller is defined as follows. The command is a simple SQL selection statement joining relevant tables with main table dbo.[Order Details].

select
    "OrderDetails"."OrderID" "OrderID"
    ,"Order"."CustomerID" "OrderCustomerID"
    ,"OrderCustomer"."CompanyName" "OrderCustomerCompanyName"
    ,"OrderEmployee"."LastName" "OrderEmployeeLastName"
    ,"OrderShipVia"."CompanyName" "OrderShipViaCompanyName"
    ,"OrderDetails"."ProductID" "ProductID"
    ,"Product"."ProductName" "ProductProductName"
    ,"ProductCategory"."CategoryName" "ProductCategoryCategoryName"
    ,"ProductSupplier"."CompanyName" "ProductSupplierCompanyName"
    ,"OrderDetails"."UnitPrice" "UnitPrice"
    ,"OrderDetails"."Quantity" "Quantity"
    ,"OrderDetails"."Discount" "Discount"
from "dbo"."Order Details" "OrderDetails"
    left join "dbo"."Orders" "Order" on "OrderDetails"."OrderID" = "Order"."OrderID"
    left join "dbo"."Customers" "OrderCustomer" on "Order"."CustomerID" = "OrderCustomer"."CustomerID"
    left join "dbo"."Employees" "OrderEmployee" on "Order"."EmployeeID" = "OrderEmployee"."EmployeeID"
    left join "dbo"."Shippers" "OrderShipVia" on "Order"."ShipVia" = "OrderShipVia"."ShipperID"
    left join "dbo"."Products" "Product" on "OrderDetails"."ProductID" = "Product"."ProductID"
    left join "dbo"."Categories" "ProductCategory" on "Product"."CategoryID" = "ProductCategory"."CategoryID"
    left join "dbo"."Suppliers" "ProductSupplier" on "Product"."SupplierID" = "ProductSupplier"."SupplierID"

We will create a field that uses Order alias of table dbo.Orders to reference Orders.EmployeeID column in SQL Formula.

Start the Project Designer and select Order Details data controller. Activate Fields tab and and choose New|New Field option on the action bar. Enter the following properties for the new field under New Field section and save its settings by pressing OK button.

Notice that we have wrapped the word Order with double quotes to make sure that it will not be misinterpreted by the database server as SQL keyword “order” .

The field will be automatically available in all data views of controller Order Details but remain hidden from end users. The presence of the field will allow it to participate in the access control rules.

Generate the application and observe that shared business rules now extend to Order Details as well. Non-administrative users will only see the order details of orders that they have placed in the system.

Availability

The new mechanism of access control rules greatly simplifies creation of a consistent data segmentation in multi-tenant applications that require isolation of database content created by users.

Access Control Rules are available in the Premium and Unlimited editions of Code On Time web application generator. Owners of Free and Standard editions can use Filter Expressions as discussed later.

What’s Next?

Unlimited edition of Code On Time generator will soon be offering Dynamic Access Control List, another powerful component of EASE (Enterprise Application Services Engine).

Dynamic Access Control List is designed to complement Access Control Rules and allow luxury of defining precise access control at runtime. The real world business processes make it difficult to foresee all possible access control restrictions, and most importantly exceptions to the rules at design time.

Dynamic Access Control List will maintain access control rules in the application database. Administrative user interface will enable dynamic creation of access control rules to respond to the business requirements at runtime without making any changes to the application code.

Filter Expressions

Developers working with Free and Standard editions can use Filter Expressions to implement access control rules in their applications.

Filter expressions are defined on a level of a view and can reference properties of business rules classes as parameters.

For example, if you make changes to dbo.Employees table as described at the top of the article and add UserID and UserName columns then you can filter Orders by EmployeeID. You will have to enable shared business rules and define filter expressions on grid1 and editForm1, followed by implementation of EmployeeID property in the SharedBusinessRules class.

Select your project on the start page of Code On Time web application generator, click Design button, select Orders data controller on All Controllers tab, and activate Views tab.

Change both grid1 and editForm1 views to have the following Filter Expression under Sort and Filter section of the property page.

Generate your project and change ~/App_Code/Rules/SharedBusinessRules.cs(vb) file to have the following code.

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class SharedBusinessRules : MyCompany.Data.BusinessRules
    {

        public object UserID
        {
            get
            {
                return System.Web.Security.Membership.GetUser().ProviderUserKey;
            }
        }

        public int EmployeeID
        {
            get
            {
                using (SqlText findEmployee = new SqlText(
                    "select EmployeeID from Employees where UserID = @UserId"))
                {
                    findEmployee.AddParameter("@UserId", UserID);
                    object id = findEmployee.ExecuteScalar();
                    if (DBNull.Value.Equals(id))
                        return -1;
                    else
                        return Convert.ToInt32(id);
                }
            }
        }

    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace MyCompany.Rules
    
    Partial Public Class SharedBusinessRules
        Inherits MyCompany.Data.BusinessRules

        Public ReadOnly Property UserID As Object
            Get
                Return System.Web.Security.Membership.GetUser().ProviderUserKey
            End Get
        End Property

        Public ReadOnly Property EmployeeID As Integer
            Get
                Using findEmployee As SqlText = New SqlText( _
                    "select EmployeeID from Employees where UserID = @UserId")
                    findEmployee.AddParameter("@UserId", UserID)
                    Dim id As Object = findEmployee.ExecuteScalar()
                    If DBNull.Value.Equals(id) Then
                        Return -1
                    Else
                        Return Convert.ToInt32(id)
                    End If
                End Using
            End Get
        End Property

    End Class
End Namespace

The value of property EmployeeID will be automatically evaluated and passed as a parameter in SQL statements created to render data for presentation in grid1 and editForm1 views of Orders data controller. Here is how a user associated with employee Davolio may see a list of Orders.

Class SharedBusinessRules is created to handle requests for all data controllers. This allows referencing EmployeeID as a parameter in other data controllers including EmployeeTerritories, Employees, and Order Details.

Filter expressions lack the flexibility of conditional restrictions available with Access Control Rules and Dynamic Access Control List. You will need to compose filter expressions that use more than one parameter to accomplish an effect of conditional filtering.

If you need to return more than one value for filtering purposes, then change the type of property to be a list of values or an array. Also refer to the property values as shown in the example below.

EmployeeID in @EmployeeID

Filter expressions can be used with Access Control Rules when needed if a presentation of data in a specific view requires additional filtering.