Thursday 19 June 2008

DynamicData: Database Based Permissions - Part 2

  1. Part 1 - Create the database tables.
  2. Part 2 - Add a User Interface to modify the permissions.
  3. Part 3 - User Marcin's InMemoryMetadataProvider to add the database based permissions to the Metadata at runtime.
  4. Part 4 - Add components from A DynamicData Attribute Based Permission Solution using User Roles to consume the database based metadata.
  5. Part 5 - Oops! Table Names with Spaces in them and Pluralization.

Add a User Interface to Modify the Permissions

In this post we will create a user interface to allow admin to edit the permission assigned to table and columns.

Adding the Admin Tables to the Website

Create a new Linq to SQL Classes Object and call it Attributes to this add the following tables from the modified Northwind database.

  • AttributesTables
  • AttributesTablePermissions
  • AttributesColumns
  • AttributesColumnPermissions
  • aspnet_Roles

It should look like this:

Attribute.dbml diagram

Figure 1 - Attribute (Linq to SQL Classes)

On both the AttributeTablePermissions and AttributeColumnPermissions and change the data type for the Permission column/field from int (System.Int32) to TablePermissionsAttribute.Permissions and ColumnPermissionsAttribute.Permissions .

Note: I've refractored FieldPermissionsAttribute to ColumnPermissionsAttribute as I thought it should be Table and Column or Entity/Class and Field/Property not a mixture and I decided to make it Table and Column.

Entity  Entity Properties

Figures 2 & 3

When you changed both types save the Attributes.dbml file.

Adding the Permissions Attributes MetaModel and Registration

Add a new MetaModel declaration to the beginning of the RegisterRoutes method of the Global.asax file:

MetaModel attributesModel = new MetaModel();

After the default MetaModel registration register the DataContext of the attributesModel.

attributesModel.RegisterContext(typeof(AttributesDataContext), new ContextConfiguration() { ScaffoldAllTables = true });
Note: There two ways of adding a second set of Linq to SQL Classes:  
    1. Is the above way of registering two MetaModels
    attributesModel.RegisterContext(typeof(AttributesDataContext), new ContextConfiguration() { ScaffoldAllTables = true });
    2. Have one MetaModel and register both DataContexts against it.
    model.RegisterContext(typeof(NWDataContext), new ContextConfiguration() { ScaffoldAllTables = true });
    model.RegisterContext(typeof(AttributesDataContext), new ContextConfiguration() { ScaffoldAllTables = true });

Show the MetaModel for the permissions attribute tables on the Default.aspx page

Copy the current GridView and give it the id Menu2 adding a <br /> between them.

<br />
<asp:GridView ID="Menu2" runat="server" AutoGenerateColumns="false" Visible="false"
    CssClass="gridview" AlternatingRowStyle-CssClass="even">
    <Columns>
        <asp:TemplateField HeaderText="Table Name" SortExpression="TableName">
            <ItemTemplate>
                <asp:HyperLink ID="HyperLink1" runat="server" 
NavigateUrl='<%#Eval("ListActionPath") %>'><%#Eval("DisplayName") %>
</asp:HyperLink> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView>

Listing 1 - adding new GridView to page

In the code behind

// Show Admin Table is user in Admin
String[] roles = Roles.GetRolesForUser();
var attributeModel = (MetaModel.GetModel(typeof(AttributesDataContext)));
var visibleTables2 = attributeModel.VisibleTables;
if (visibleTables2.Count > 0 && roles.Contains("Admin"))
{
    Menu2.DataSource = visibleTables2;
    Menu2.DataBind();
    Menu2.Visible = true;
}

Listing 2 - code to show the admin table if user is member of "Admin" roles

Creating the FieldTemplates to deal with Roles and Permissions selection

Three FieldTemplates are required:

  1. Roles.ascx and Roles_Edit.ascx
  2. TablePermissions.ascx and ColumnPermissions.ascx
  3. TablePermissions_Edit.ascx and ColumnPermissions_Edit.ascx

1. Roles.ascx and Roles_Edit.ascx

For the Roles.ascx you just need to copy and rename the Text.ascx file remembering to change the class name in the code behind from TextField to RolesField, and also in the ascx page Inherits="TextField" to Inherits="RolesField".

Roles.ascx in action 

Figure 4

For the Roles_Edit.ascx you can start by copying the Text_Edit.ascx and then rework it.

<%@ Control Language="C#" CodeFile="Roles_Edit.ascx.cs" Inherits="Roles_EditField" %>
<asp:CheckBoxList ID="CheckBoxList1" runat="server" 
    ondatabound="CheckBoxList1_DataBound" 
RepeatDirection="Horizontal" RepeatLayout="Flow"> </asp:CheckBoxList>

Listing 3 - Roles_Edit.ascx

using System;
using System.Collections.Specialized;
using System.Linq;

public partial class Roles_EditField : System.Web.DynamicData.FieldTemplateUserControl
{
    protected override void OnDataBinding(EventArgs e)
    {
        base.OnDataBinding(e);

        // get all the roles from the aspnet_Roles table and
        // populate the CheckBoxList
        var DC = new AttributesDataContext();
        var allRoles = from r in DC.aspnet_Roles
                       select r.RoleName;
        CheckBoxList1.DataSource = allRoles;
        CheckBoxList1.DataBind();
    }

    protected override void ExtractValues(IOrderedDictionary dictionary)
    {
        String value = "";
        for (int i = 0; i < CheckBoxList1.Items.Count; i++)
        {
            // append all the boxes that are checked
            if (CheckBoxList1.Items[i].Selected == true)
                value += CheckBoxList1.Items[i].Text + ",";
        }
        if (String.IsNullOrEmpty(value))
        {
            dictionary[Column.Name] = value;
        }
        else
        {
            dictionary[Column.Name] = value.Substring(0, value.Length - 1);
        }
    }

    public override Control DataControl
    {
        get
        {
            return CheckBoxList1;
        }
    }

    protected void CheckBoxList1_DataBound(object sender, EventArgs e)
    {
        if (FieldValue != null)
        {
            String[] selectRoles = ((String)FieldValue).Split((char)',');
            for (int i = 0; i < CheckBoxList1.Items.Count; i++)
            {
                // select all check boxes that are in the array 
                if (selectRoles.Contains(CheckBoxList1.Items[i].Value))
                {
                    CheckBoxList1.Items[i].Selected = true;
                }
            }
        }
    }
}

Listing 4 - Roles_edit.ascx.cs

Roles_Edit.ascx FiledTemplate in action

Figure 5

2. TablePermissions.ascx and ColumnPermissions.ascx

These two FieldTemplates are basically the same with some minor adjustments for the class name and the attribute Permissions FieldPermissionsAttribute or the TablePermissionsAttribute.

<%@ Control Language="C#" CodeFile="TablePermissions.ascx.cs" Inherits="TablePermissionsField" %>
<asp:Literal ID="Literal1" runat="server"></asp:Literal>
Listing 5 - TablePermissions.ascx and ColumnPermissions.ascx
using System;
using System.Web.UI;

public partial class TablePermissionsField : System.Web.DynamicData.FieldTemplateUserControl
{
    protected override void OnDataBinding(EventArgs e)
    {
        base.OnDataBinding(e);

        object value = FieldValue;
        if (value != null)
        {
// Convert Permission to string
//Literal1.Text = ((FieldPermissionsAttribute.Permissions)value).ToString(); Literal1.Text = ((TablePermissionsAttribute.Permissions)value).ToString(); } } public override Control DataControl { get { return Literal1; } } }
Listing 6 - TablePermissions.ascx.cs and ColumnPermissions.ascx.cs

It should be noted that FieldValue is only valid after OnDataBinding is called, so it is not valid and you will get a runtime error if you try to reference if in the Page_Load event.

3. TablePermissions_Edit.ascx and ColumnPermissions_Edit.ascx

These two FieldTemplates are also very similar to create, so build the first and test then copy rename and alter the TablePermissionsAttribute to FieldPermissionsAttribute and that sorts the other out (don't forget the class names on the ascx and code behind). 

<%@ Control Language="C#" CodeFile="TablePermissions_Edit.ascx.cs" Inherits="TablePermissions_EditField" %>
<asp:DropDownList ID="DropDownList1" runat="server" 
    ondatabound="DropDownList1_DataBound">
</asp:DropDownList>

Listing 7 - TablePermissions_Edit.ascx and ColumnPermissions_Edit.ascx

using System;
using System.Collections.Specialized;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class TablePermissions_EditField : System.Web.DynamicData.FieldTemplateUserControl
{

    protected override void OnDataBinding(EventArgs e)
    {
        base.OnDataBinding(e);
        // get a data bindable list of permissions for the DDL
        //var test = Enum.GetValues(typeof(FieldPermissionsAttribute.Permissions));
        var test = Enum.GetValues(typeof(TablePermissionsAttribute.Permissions));
        DropDownList1.DataSource = test;
        DropDownList1.DataBind();
    }

    protected override void ExtractValues(IOrderedDictionary dictionary)
    {
        dictionary[Column.Name] = DropDownList1.SelectedValue;
    }

    public override Control DataControl
    {
        get
        {
            return DropDownList1;
        }
    }

    protected void DropDownList1_DataBound(object sender, EventArgs e)
    {
        if (FieldValue != null)
        {
            // get the currently assigned attribute
            //var permission = (FieldPermissionsAttribute.Permissions)FieldValue;
            var permission = (TablePermissionsAttribute.Permissions)FieldValue;
            ListItem item = this.DropDownList1.Items.FindByValue(permission.ToString());

            if (item != null)
            {
                // set selected item
                item.Selected = true;
            }
        }
    }
}

Listing 7 - TablePermissions_Edit.ascx.cs and ColumnPermissions_Edit.ascx.cs

Note: It is important to remember that FieldValue is only available after the FieldTemplate's OnDataBinding event has fired, if  you try and reference it before then

Restricting Access to the Admin Tables

When you've copied and renamed we have all the elements of our interface we just need to make sure that the PageTemplates don't allow any user who accidentally find themselves on and admin page to access the data.

All we have to do is add the following code to the beginning of the Page_Load event handler.

protected void Page_Load(object sender, EventArgs e)
{
    table = GridDataSource.GetTable();
    Title = table.DisplayName;

    // Show Admin Table is user in Admin
    String[] roles = Roles.GetRolesForUser();
    var tableDataContext = table.CreateContext();
    if (tableDataContext.GetType() != typeof(AttributesDataContext) && !roles.Contains("Admin"))
    {
        // redirect to Default.aspx with error
        Response.Redirect("~/Default.aspx?error=You do not have access to Admin on this site (Table=" + table.Name + ")");
    }

    InsertHyperLink.NavigateUrl = table.GetActionPath(PageAction.Insert);

    // Disable various options if the table is readonly
    if (table.IsReadOnly)
    {
        GridView1.Columns.RemoveAt(0);
        InsertHyperLink.Visible = false;
    }
}

Listing 8 - Page_Load event handler on the List.aspx PageTemplate.

Add a Label with an Id of Error to the Default.aspx and in the Page_Load event handler Default.aspx.cs code behind.

if (Request.QueryString.Count > 0 && Request.QueryString["error"] != "")
{
    Error.Text = Request.QueryString["error"];
}

Listing 9 - Showing the error message on the Default.aspx

Two Finnished FieldTemplates in operation

Next

This is the tricky bit; in the next post we will parse the database and add attribute dynamically to the metadata at application startup.

Note: This will have the same restrictions as the InMemoryMetadataProvider post by Marcin on his blog post Dynamic Data samples: Custom metadata providers the permissions are set at startup of the application.

9 comments:

Louque said...

Hello,

Do we have to follow the "A DynamicData Attribute Based Permission Solution using User Roles" tutorial before implementing the "DynamicData: Database Based Permissions"?

I am asking because I get an error when I change the Permission field data type to ColumnPermissionsAttribute.Permissions or TablePermissionsAttribute.Permissions

Is there a place where we can download your sample code?

Thank you,
Luc

Stephen J. Naughton said...

Hi Louque have a look here DynamicData: Database Based Permissions - Part 5: http://csharpbits.notaclue.net/2008/06/dynamicdata-database-based-permissions_28.html

Hope this helps :D

Anonymous said...

Hi Steve

This is a great post!!

I've got a short question concerning your role system. You ask for "Deny" whatever. What happens with this system, if user is in more than one role? Is there a reason, why you don't check for "Allow" whatever? Did I miss something?

Thank's for help.
Sebastian

Stephen J. Naughton said...

Hi, I just decided that I wanted a simple system where you had access to everything and just took permissions away so Deny. Also I have a newer version (not using the DB for permissions) here this is for Preview 4 of DD.

Steve :D

Anonymous said...

Hi Steve

Sorry for my late reply. Your were indeed much faster! :-) The Link you've posted is quiet interresting, but I can't change my solutions everytime. They are now on productive systems.

What I did is, I've changed the "HasAnyRole" function a bit. Now it returns only true, if user belongs to roles for which access to table is defined. Doing this allows me to assign more than one role AND setting access like the easy way you do. The Code looks like this:

public Boolean HasAnyRole(String[] roles)
{
// call extension method to convert array to lower case for compare
foreach (var role in roles.AllToLower())
{
if (!_roles.AllToLower().Contains(role.ToLower()))
return false;

}
return true;
}

Anonymous said...

Great source of info. I am moving from vb.net to csharp. Refactoring is kinda new to me. How do you refactor the permission column above?

Stephen J. Naughton said...

Hi not sure what you mean, do you want to e-mail me (e-mail address is at top right) and we can get into a bit more detail.

Steve :D

Tim Hamilton said...

Hi Steve. I just wanted to say great job on this and the many other posts you have done on ASP.NET Dynamic Data. They have helped me a lot in developing using this Technology.

Stephen J. Naughton said...

Yolu welcome Tim, expect the same from me on LightSwitch

Steve