*** 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.
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
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
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.
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.
In this post we will create a user interface to allow admin to edit the permission assigned to table and columns.
Create a new Linq to SQL Classes Object and call it Attributes to this add the following tables from the modified Northwind database.
It should look like this:
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 .
Figures 2 & 3
When you changed both types save the Attributes.dbml file.
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 });
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
Three FieldTemplates are required:
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".
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
Figure 5
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) {Listing 6 - TablePermissions.ascx.cs and ColumnPermissions.ascx.cs
// Convert Permission to string
//Literal1.Text = ((FieldPermissionsAttribute.Permissions)value).ToString(); Literal1.Text = ((TablePermissionsAttribute.Permissions)value).ToString(); } } public override Control DataControl { get { return Literal1; } } }
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.
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
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
This is the tricky bit; in the next post we will parse the database and add attribute dynamically to the metadata at application startup.
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:
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.
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
/****** Now populate tables with Table and Column Names ******/ PRINT'' PRINT 'Populate AttributesTables and AttributesColumns Tables';GO IF (SELECT COUNT(*) FROM [dbo].[AttributesTables]) = 0BEGIN 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]) = 0BEGIN PRINT 'Populating: [AttributesColumns]'; INSERT INTO [dbo].[AttributesColumns] SELECT [AT].[TableName], [COLUMN_NAME] AS [ColumnName] FROM [INFORMATION_SCHEMA].[COLUMNS] AS T JOIN [dbo].[AttributesTables] ATON [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
When you've run this T-SQL detach the database from the SQL Server Management Studio.
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.
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).
You will need to add a Login.aspx page with a login control on to the root of the website.
Creating the user interface for setting the database based attributes.
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.
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.
I'll be back soon with the first step.
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)
