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
   
   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.
   
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

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.