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

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