Monday 30 June 2008

Routing and DynamicData

*** I've pulled this after what David Ebbo said in the this thread Re: Manual Scaffolding and Routing with Compound PK's until I really understand what I'm talking about ***

Some useful links regarding Routing:

Mike Ormond's using ASP.NET Routing independent of MVC

Ian Blackburn's passing of parameters to the aspx page

Enjoy.

Saturday 28 June 2008

DynamicData: Database Based Permissions - Part 5

**** OOPS ****

  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.

Oops! Table Names with Spaces in them and Pluralization smile_embaressed

This is a fix for the issue brought to light by António Borges from Portugal when he was implementing Database Based Permissions add on to a Dynamic Data website, with my project as a starting point. He noticed that although the user Fred had the correct permissions for the Orders table, he seemed to have full permissions to the Order_Details table even though in the admin interface it showed as restricted in the same way as the Orders table.

This turned out to be an issue similar to Pluralization which adds and removed S's (which I like). It turns out that when you drop a table on a Linq to SQL Classes design surface and the table name has spaces in it the designer adds an "_" underscore in place of the space (makes sense as you can't have a class name with a space in it) but because I was populating the tables with T-SQL I was getting the table names in a raw SQL state (I hadn't noticed this before even though I have been using Northwind with Linq to SQL since early betas of it in VD 2005).

So the fix it to populate the tables in code using Linq to SQL (which I also like :D) this will take information from the MetaModel which has table and column names which match the Linq to SQL Classes. I have also taken the opportunity to move some of the inline code in the Global.asax file to a separate class which I have imaginatively called the DatabaseMetadata class. Please see below:

using System;
using System.Linq;
using System.Web.DynamicData;
using Microsoft.Web.DynamicData;

public static class DatabaseMetadata
{
    /// <summary>
    /// Adds and Removes table and column info for the metadata tables
    /// </summary>
    /// <param name="model">Referances the model for the site</param>
    /// <param name="ADC">DataContext of the Attributes model</param>
    public static void UpdateAttributesTables(this MetaModel model, AttributesDataContext ADC)
    {
        // import tables and columns from MetaModel
        foreach (var table in model.Tables)
        {
            // if no table then
            if (ADC.AttributesTables.SingleOrDefault(at => at.TableName == table.Name) == null)
            {
                var t = new AttributesTable()
                {
                    TableName = table.Name
                };
                ADC.AttributesTables.InsertOnSubmit(t);
            }
            foreach (var column in table.Columns)
            {
                // if no column then
                if (ADC.AttributesColumns.SingleOrDefault(ac => ac.TableName == table.Name &&
                    ac.ColumnName == column.Name) == null)
                {
                    var c = new AttributesColumn()
                    {
                        TableName = table.Name,
                        ColumnName = column.Name
                    };
                    ADC.AttributesColumns.InsertOnSubmit(c);
                }
            }
        }

        // remove data from attributes table and columns 
        // where tables have been removed from the model
        var tableInADC = ADC.AttributesTables;
        var tableInModel = model.Tables;
        foreach (var table in tableInADC)
        {
            if (tableInModel.SingleOrDefault(mt => mt.Name == table.TableName) == null)
            {
                // remove column permissions
                var acp = from c in ADC.AttributesColumnPermissions
                          where c.TableName == table.TableName
                          select c;
                ADC.AttributesColumnPermissions.DeleteAllOnSubmit(acp);

                // remove columns
                var ac = from c in ADC.AttributesColumns
                         where c.TableName == table.TableName
                         select c;
                ADC.AttributesColumns.DeleteAllOnSubmit(ac);

                // remove table permissions
                var atp = from t in ADC.AttributesTablePermissions
                          where t.TableName == table.TableName
                          select t;
                ADC.AttributesTablePermissions.DeleteAllOnSubmit(atp);

                // remove table
                ADC.AttributesTables.DeleteOnSubmit(table);
            }
        }

        // finally submit changes
        ADC.SubmitChanges();
    }

    /// <summary>
    /// Imports Database Table Attributes
    /// </summary>
    /// <param name="model">Referances the model for the site</param>
    /// <param name="ADC">DataContext of the Attributes model</param>
    public static void ImportTableAttributes(this MetaModel model, AttributesDataContext ADC)
    {
        var tableAttributes = ADC.AttributesTablePermissions;
        if (tableAttributes.Count() > 0)
        {
            foreach (var ta in tableAttributes)
            {
                var table = model.Tables.SingleOrDefault(t => t.Name == ta.TableName);
                String[] roles = ta.Roles.Split(new Char[] { ',' });
                if (table != null)
                    InMemoryMetadataManager.AddTableAttributes
                        (
                            table.EntityType,
                            new TablePermissionsAttribute(ta.Permission, roles)
                        );
            }
        }
    }

    /// <summary>
    /// Imports Database Column Attributes
    /// </summary>
    /// <param name="model">Referances the model for the site</param>
    /// <param name="ADC">DataContext of the Attributes model</param>
    public static void ImportColumnAttributes(this MetaModel model, AttributesDataContext ADC)
    {
        var columnAttributes = ADC.AttributesColumnPermissions;
        if (columnAttributes.Count() > 0)
        {
            foreach (var col in columnAttributes)
            {
                var table = model.Tables.SingleOrDefault(t => t.Name == col.TableName);
                var column = table.EntityType.GetProperties().SingleOrDefault(c => c.Name == col.ColumnName);
                String[] roles = col.Roles.Split(new Char[] { ',' });
                if (table != null)
                    InMemoryMetadataManager.AddColumnAttributes
                        (
                            column,
                            new ColumnPermissionsAttribute(col.Permission, roles)
                        );
            }
        }
    }
}

Listing 1 - DatabaseMetadata Class

//model.RegisterContext(typeof(NWDataContext), new ContextConfiguration() { ScaffoldAllTables = true });
model.RegisterContext(typeof(NWDataContext), new ContextConfiguration()
{
    ScaffoldAllTables = true,
    MetadataProviderFactory =
        (
            type => new InMemoryMetadataTypeDescriptionProvider
                (
                    type, new AssociatedMetadataTypeTypeDescriptionProvider(type)
                )
        )
});

// get data context for attributes
var ADC = new AttributesDataContext();

// get any new tables and their column into the AttributesDataContext
ADC.UpdateAttributesTables(model);

// import Database Table Attributes
model.ImportTableAttributes(ADC);

// Import Database Column Attributes
model.ImportColumnAttributes(ADC);

attributesModel.RegisterContext(typeof(AttributesDataContext), new ContextConfiguration() { ScaffoldAllTables = true });

Listing 2 - excerpt from the Global.asax file changes is BOLD

The beauty of Linq to SQL is that the code reads so easily, I don't think much if any explanation is needed. Just note about the new functionality the UpdateAttributesTables extension method populates the attribute tables (Tables and Columns) in the AttributesDataContext much like the T-SQL did in Part 1 but now it will add any new tables you add to the MetaModel and also if a table is removed from the MetaModel it is removed and ALL record associated with it including any attributes you created.

I have also updated the project files for download.

SQL Server 2005 version

SQL Server 2008 version

Sunday 22 June 2008

DynamicData: Database Based Permissions - Part 4

**** UPDATED ****

  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. 

Attribute Based Permissions added to Consume the Databased Based Metadata

I've added all the attribute based permissions login from my previous series A DynamicData Attribute Based Permission Solution using User Roles and zipped up the finished project files.

Here are the project files for Visual Studio 2008 with database in SQL Server 2005 and 2008, I've updated both downloads to use DynamicDataExtensions see below and to fix a small bug see Part 3 updates in bold.

Download for SQL server 2005

Download for SQL Server 2008

Note: I must emphasize that when you update the Table or Column attributes tables that the application MUST be restarted, see Marcin Dobosz's Dynamic Data samples: Custom metadata providers article on his blog under the section Truly dynamic metadata.

The user names and passwords:

Are now shown on the Login screen.

Yes the password is password, I know it's not good practice but it's only for demo  purposes.

Other changes I'm now making use of the DynamicDataExtensions (soon to be renamed DynamicDataFutures) and in particular the following FieldTemplates Enumeration.ascx & Enumeration_Edit.ascx instead of my own ColumnPermissions and TablePermissions. Further to the above I've added more users (see login page) and added more permissions to table and columns.

Thanks to David Ebbo for the above suggestions.

Saturday 21 June 2008

DynamicData: Database Based Permissions - Part 3

**** UPDATED ****

  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 the Database Based Permissions to the Metadata.

In this post we will add the final bit in this series which is using Marcin's InMemoryMetadataProvider to apply the permissions data from the database to the MetaModel used in the application. I've got to admit I was expecting this to be the most difficult bit of the series but thanks to Marcin and his InMemoryMetadataProvider sample Dynamic Data samples: Custom metadata providers on his blog is was easy hardly any reflection used at all.

model.RegisterContext(typeof(NWDataContext), new ContextConfiguration()
{
    ScaffoldAllTables = true,
    MetadataProviderFactory = 
        (
            type => new InMemoryMetadataTypeDescriptionProvider
                (
                    type, new AssociatedMetadataTypeTypeDescriptionProvider(type)
                )
        )
});

Listing 1 - adding the InMemoryMetadataProvider

As you can see all I have had to do here is add same code as Marcin had in his blog to add the InMemoryMetadataProvider nice. And flowing on from that we add our two pieces of code one for the table attributes and one for the column attributes.

var tableAttributes = ADC.AttributesTablePermissions;
if (tableAttributes.Count() > 0)
{
    foreach (var ta in tableAttributes)
    {
        var table = model.Tables.SingleOrDefault(t => t.Name == ta.TableName);
        String[] roles = ta.Roles.Split(new Char[] { ',' }); // Added
        if (table != null)
            InMemoryMetadataManager.AddTableAttributes
                (
                    table.EntityType,
                    new TablePermissionsAttribute(ta.Permission, roles) // Changed ro roles from ta.Roles
                );
    }
}

Listing 2 - applying the table attributes

In this we run through all the column attributes in the returned form the query finding the table using Linq (My personal favourite addition to c# 3.0) which makes getting the table easy and future maintenance of the code easy as its pretty clear what you are doing.

// Import Database Column Attributes
var columnAttributes = ADC.AttributesColumnPermissions;
if (columnAttributes.Count() > 0)
{
    foreach (var col in columnAttributes)
    {
        var table = model.Tables.SingleOrDefault(t => t.Name == col.TableName);
        var column = table.EntityType.GetProperties().SingleOrDefault(c => c.Name == col.ColumnName);
        String[] roles = col.Roles.Split(new Char[] { ',' }); // Added
        if (table != null)
            InMemoryMetadataManager.AddColumnAttributes
                (
                    column,
                    new ColumnPermissionsAttribute(col.Permission, roles) // changed to roles from col.Roles
                );
    }
}

Listing 3 - applying the column attributes

it's in this where we are getting the column that we need a tiny bit of reflection which to be honest you have to debug to see that's what you are doing as the column tat is returned from the query in the foreach loop is of the Type System.Reflection.PropertyInfo.

And that is it for the functionality the Database Based Permissions series. I think that the next step is to provide a tool for adding the columns that the project uses to a database easily, and also i would like to add at least one sproc (stored procedure) to the database that can be called to update the AttributesTables and AttributesColumns with the latest tables and columns from the database and maybe clean up orphaned attribute but I'll have a think about that a bit first.

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.

Monday 16 June 2008

DynamicData: Database Based Permissions - Part 1

**** UPDATED ****

  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.

Creating the Permissions Tables

The first thing I decided was that the Permissions and Roles needed to be in the same database as the tables the permissions were to be set on. The reasons were as follows:

  • For the  user interface access to the Roles for ForeignKey relationships.
  • The T-SQL needed access to the actual database where tables were stored to get a list of tables and fields.

Adding the Tables from ASPNETDB to the Northwind Database

This meant creating the table for the ASPNETDB in the Northwind database, I had not intended to show how to do this but as I had to dig around to find out how I am going to do it here.

  1. Close Visual Studio 2008 and open SQL Server Management Studio and temporarily attach the Northwind database in your App_Data folder.
  2. open an Visual Studio 2008 Command Prompt
    Windows Vista Start->All Programs->Microsoft Visual Studio 2008->Visual Studio Tools->Visual Studio 2008 Command Prompt
    Click Start->All Programs->Microsoft Visual Studio 2008->Visual Studio Tools->Visual Studio 2008 Command Prompt just to be safe right mouse click and choose Run as Administrator.
  3. From the command prompt type Aspnet_regsql.exe press enter.
  4. Follow the wizard through choosing your database.
    Aspnet_regsql.exe wizard choose database
  5. When you have clicked finish.
  6. Now you have the ASPNETDB tables in the Northwind database.

Creating the Permissions Tables

For this I've created a bit of T-SQL (revised to use table and column names as primary keys instead of Id (int))

USE [Northwind]
GO
/****** Object:  Table [dbo].[AttributesTables]    Script Date: 06/12/2008 19:09:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

PRINT 'Create Attribute Permissions Tables';
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AttributesTables]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[AttributesTables](
        [TableName] [nvarchar](50) NOT NULL,
     CONSTRAINT [PK_AttributesTables_1] PRIMARY KEY CLUSTERED 
    (
        [TableName] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY];
END
ELSE 
    PRINT 'Error: [dbo].[AttributesTables] already exists';
GO
/****** Object:  Table [dbo].[AttributesTablePermissions]    Script Date: 06/12/2008 19:09:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AttributesTablePermissions]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[AttributesTablePermissions](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [TableName] [nvarchar](50) NOT NULL,
        [Permission] [int] NOT NULL,
        [Roles] [nvarchar](1024) NOT NULL,
     CONSTRAINT [PK_AttributesTablePermissions] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY];
END
ELSE 
    PRINT 'Error: [dbo].[AttributesTablePermissions] already exists';
GO

/****** Object:  Table [dbo].[AttributesColumns]    Script Date: 06/12/2008 19:09:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AttributesColumns]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[AttributesColumns](
        [TableName] [nvarchar](50) NOT NULL,
        [ColumnName] [nvarchar](50) NOT NULL,
     CONSTRAINT [PK_AttributesColumns_1] PRIMARY KEY CLUSTERED 
    (
        [TableName] ASC,
        [ColumnName] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY];
END
ELSE 
    PRINT 'Error: [dbo].[AttributesColumns] already exists';
GO

/****** Object:  Table [dbo].[AttributesColumnPermissions]    Script Date: 06/12/2008 19:09:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AttributesColumnPermissions]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[AttributesColumnPermissions](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [TableName] [nvarchar](50) NOT NULL,
        [ColumnName] [nvarchar](50) NOT NULL,
        [Permission] [int] NOT NULL,
        [Roles] [nvarchar](1024) NOT NULL,
     CONSTRAINT [PK_AttributesColumnPermissions] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY];
END
ELSE 
    PRINT 'Error: [dbo].[AttributesColumnPermissions] already exists';
GO

/****** Now set up the relationshipd ******/
PRINT '';
PRINT 'Setup table relationships';
GO

/****** Object:  ForeignKey [FK_AttributesColumnPermissions_AttributesColumns]    Script Date: 06/14/2008 11:30:54 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AttributesColumnPermissions_AttributesColumns]') AND parent_object_id = OBJECT_ID(N'[dbo].[AttributesColumnPermissions]'))
BEGIN
    ALTER TABLE [dbo].[AttributesColumnPermissions]  WITH CHECK ADD  CONSTRAINT [FK_AttributesColumnPermissions_AttributesColumns] FOREIGN KEY([TableName], [ColumnName])
    REFERENCES [dbo].[AttributesColumns] ([TableName], [ColumnName]);
    ALTER TABLE [dbo].[AttributesColumnPermissions] CHECK CONSTRAINT [FK_AttributesColumnPermissions_AttributesColumns];
END
ELSE 
    PRINT 'Error: [FK_AttributesColumnPermissions_AttributesColumns] already exists';
GO

/****** Object:  ForeignKey [FK_AttributesColumns_AttributesTables]    Script Date: 06/14/2008 11:30:54 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AttributesColumns_AttributesTables]') AND parent_object_id = OBJECT_ID(N'[dbo].[AttributesColumns]'))
BEGIN
    ALTER TABLE [dbo].[AttributesColumns]  WITH CHECK ADD  CONSTRAINT [FK_AttributesColumns_AttributesTables] FOREIGN KEY([TableName])
    REFERENCES [dbo].[AttributesTables] ([TableName]);
    ALTER TABLE [dbo].[AttributesColumns] CHECK CONSTRAINT [FK_AttributesColumns_AttributesTables];
END
ELSE 
    PRINT 'Error: [FK_AttributesColumns_AttributesTables] already exists';
GO

/****** Object:  ForeignKey [FK_AttributesTablePermissions_AttributesTables]    Script Date: 06/14/2008 11:30:54 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_AttributesTablePermissions_AttributesTables]') AND parent_object_id = OBJECT_ID(N'[dbo].[AttributesTablePermissions]'))
BEGIN
    ALTER TABLE [dbo].[AttributesTablePermissions]  WITH CHECK ADD  CONSTRAINT [FK_AttributesTablePermissions_AttributesTables] FOREIGN KEY([TableName])
    REFERENCES [dbo].[AttributesTables] ([TableName]);
    ALTER TABLE [dbo].[AttributesTablePermissions] CHECK CONSTRAINT [FK_AttributesTablePermissions_AttributesTables];
END
ELSE 
    PRINT 'Error: [FK_AttributesTablePermissions_AttributesTables] already exists';
GO

**** The code below is now defunct Part 5 now cover this ****

/****** Now populate tables with Table and Column Names ******/
PRINT ''
PRINT 'Populate AttributesTables and AttributesColumns Tables';
GO

IF (SELECT COUNT(*) FROM [dbo].[AttributesTables]) = 0
BEGIN
    PRINT 'Populating: [AttributesTables]';
    INSERT INTO [dbo].[AttributesTables]
        SELECT [TABLE_NAME] AS [TableName]
          FROM [INFORMATION_SCHEMA].[TABLES]
          WHERE [TABLE_TYPE] = 'BASE TABLE' AND SUBSTRING([TABLE_NAME], 1, 10) <> 'Attributes' AND SUBSTRING([TABLE_NAME], 1, 7) <> 'aspnet_';
    PRINT '';
END
ELSE
    PRINT 'Error: [dbo].[AttributesTables] already has rows';


IF (SELECT COUNT(*) FROM [dbo].[AttributesColumns]) = 0
BEGIN
    PRINT 'Populating: [AttributesColumns]';
    INSERT INTO [dbo].[AttributesColumns]
        SELECT [AT].[TableName], [COLUMN_NAME] AS [ColumnName]
          FROM [INFORMATION_SCHEMA].[COLUMNS] AS T
          JOIN [dbo].[AttributesTables] AT ON
          [T].[TABLE_NAME] = [AT].[TableName];
    PRINT '';
END
ELSE
    PRINT 'Error: [dbo].[AttributesColumns] already has rows';
GO

This listing will create the Permissions Tables and populate them with the base data. This is what we will end up with (revised see above):

Attribute tables diagram

Attribute Tables

When you've run this T-SQL detach the database from the SQL Server Management Studio.

Updating web.config

The web.config must be updated (see below) to configure membership, profile and roleManager providers to use the Northwind database.

<connectionStrings>
    <remove name="NorthwindConnectionString" />
    <add name="NorthwindConnectionString" 
         connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Northwind.mdf;Integrated Security=True;User Instance=True"
    providerName="System.Data.SqlClient" />
</connectionStrings>

Updated Connection strings

<authentication mode="Forms">
    <forms loginUrl="~/Login.aspx" protection="All" path="/"/>
</authentication>
<authorization>
    <deny users="?"/>
</authorization>
<membership>
    <providers>
        <remove name="AspNetSqlMembershipProvider"/>
        <add name="AspNetSqlMembershipProvider" 
             type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" 
             connectionStringName="NorthwindConnectionString" 
             enablePasswordRetrieval="false" 
             enablePasswordReset="true" 
             requiresQuestionAndAnswer="false" 
             applicationName="/" 
             requiresUniqueEmail="false" 
             passwordFormat="Hashed" 
             maxInvalidPasswordAttempts="5" 
             minRequiredPasswordLength="7" 
             minRequiredNonalphanumericCharacters="0" 
             passwordAttemptWindow="10" 
             passwordStrengthRegularExpression=""/>
    </providers>
</membership>
<profile>
    <providers>
        <remove name="AspNetSqlProfileProvider"/>
        <add name="AspNetSqlProfileProvider" 
             connectionStringName="NorthwindConnectionString" 
             applicationName="/" 
             type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
    </providers>
</profile>
<roleManager enabled="true">
    <providers>
        <remove name="AspNetSqlRoleProvider"/>
        <add name="AspNetSqlRoleProvider" 
             connectionStringName="NorthwindConnectionString" 
             applicationName="/" 
             type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
        <remove name="AspNetWindowsTokenRoleProvider"/>
        <add name="AspNetWindowsTokenRoleProvider" 
             applicationName="/" 
             type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
    </providers>
</roleManager>

Updated <system.web>

I've updated the connection string to remove the connection first and added membership, profile and roleManager sections to the <system.web> section of the web.config file.

Revised

I've changed the format of the tables so that they no longer use Id (int) as primary keys. I have substituted TableName and ColumnName as primary keys this will allow the content of the main tables (AttributesTables & AttributesColumns) and  to be updated while the user maintained table (AttributesTablePermissions & AttributesColumnPermissions) remain intact (for the most part, if you for instance rename a table or column then the attributes defined in the user maintained tables will become orphaned).

More additions

You will need to add a Login.aspx page with a login control on to the root of the website.

Next

Creating the user interface for setting the database based attributes.

Saturday 14 June 2008

ForeignKey and Entity Reference Columns

Just a note David Ebbo explains Understanding foreign keys and relationship columns in Dynamic Data on his blog this is something that confused me when I first started creating custom pages in DynamicData.

Thanks again to David.

Friday 13 June 2008

DynamicData: Database Based Permissions

In this sequel to the A DynamicData Attribute Based Permission Solution using User Roles I will user Marcin Dobosz's

Dynamic Data samples: Custom metadata providers InMemoryMetadataProvider to add metadata from a database.

Steps to build

  1. Create the database tables
  2. Add a User Interface to modify the permissions
  3. User Marcin's InMemoryMetadataProvider to add the database based permissions to the Metadata at runtime.

I'll be back soon with the first step.

Wednesday 11 June 2008

DynamicData - Limit Data Displayed By User

Articles in this Series

Limiting Data by the Current User

This is a really simple scenario where the table has a column which is update with the current user name at record creation (see: Automatic Column Update how to automatically update tables) all that is required is to add a WhereParameter to the pages LinqDataSources. In the Page_Load add the line for each LinqDataSource on the page (this line of code is for the List.aspx page).

GridDataSource.WhereParameters.Add(new Parameter("CreatedBy", DbType.String, User.Identity.Name));
Obviously you may whish to add a CheckBox or RadioButton to toggle this feature or even add a filter to filter several users at once (see Marcin Dobosz Blog post Dynamic Data samples: Extending the FilterRepeater for details on how to extend the FilterRepeater)