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

6 comments:

mlho said...

I can not update from the memory when metaattributes make a change to the database in the model attributes, for example, have the table "Users" which has write permissions only, and in the global.asax are loaded Attributes permissions in memory, but when you make a change to the table to say that the write-only attribute to read only happens now, does not update the changes, I have to close the asp.net development server and then re-start the program to reload the global.asax and recently there had made the changes mentioned above ...

greetings in advance

Stephen J. Naughton said...

Hi Mlho, I'm not sure I follow you you may be better served by looking at my new Security sample which uses some new methods of filtering access, I will be augmenting this soon by doing a DD v1 version that does everything by routing.

Anonymous said...

Hi, I'm using your code, and have trouble when add or remove permission to an user, it not refresh the changes that I made when user logout and login.

Stephen J. Naughton said...

The one drawback with Dynamic Data is that the metadata is only loaded at Application Start, so you need to restart the app everytime you change permissions.

This maybe possible to overcome in DDv2

Steve :D

Mae said...

Hi, Steve.

Is the drawback you described above, still an issue in .NET 4.0 Dynamic Data?

Right now I'm still using .NET 3.5 SP1 but really interested if your solution is now possible with .NET 4.0 without having to restart the application.

Thanks in advance for your help.

Stephen J. Naughton said...

Hi Mae, no the issue is with the lateness of the attributes are added, I have an idea for another way but no time to pursue it myself :(

Steve