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.

9 comments:

mchatter said...

I'd like to implement som kind of database based permissions and a login process for a Dynamic Data Entity Framework application.

Before implementing your solution could you tell me if it designed to work with DD EF apps ?

Thanks

Steve said...

The published version will not work with EF but I have a version 1 (permissions in metadata) that does if you like I can e-mail it to you?

Steve

Michael said...

That would be a great help - Thanks...

Steve said...

You'll need to sent me your e-mail, you can fine mine in my profile on this page.

Steve :D

Mae said...

Hi, Steve! This is a great article!!! I am using .NET 3.5 SP1 using EF and DD. I have just downloaded your code and reviewing it now.

Above, Michael asked about using this solution with EF. Could I also have a copy of the EF version you mentioned above? Will it work with .NET 3.5 SP1?

Thanks in advance for your help!

Steve said...

Sorry Mae, I never finished getting this working with EF only had it working with L2S

Steve

Anand said...

Hi Steve,

I have a SQL Server database and Oracle database. SQL Server database acts as the application database and manages application user, roles, etc. Oracle database contains the tables on which the users want to perform CRUD operations. I have created two Entity Framework Models for these two databases. Obviously, the users (ohter than administrators) should not see the tables in SQL Server database. An administrator can see tables in both the databases. How can this be implemented? If you have any example, please share it across..
Thanks in advance.

Anand said...

Hi Steve,

Adding on to my previous comment - I have written a code in Global.asax to perform role based access which works fine when I debug the application for the first time. But from the next time, it throws "Item has been added already" exception. More detail on this code and error is in stackoverflow http://stackoverflow.com/questions/7851440/exception-item-has-already-been-added-in-metamodel-register?answertab=votes#tab-top

Steve said...

Hi Anand, these are my latest bits here http://csharpbits.notaclue.net/2010/06/securing-dynamic-data-4-replay.html

this may solve your second issue also.