DynamicData: Database Based Permissions - Part 1 (original) (raw)

**** 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:

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 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 NOT NULL, [Permission] [int] NOT NULL, [Roles] nvarchar 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 NOT NULL, [ColumnName] nvarchar 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 NOT NULL, [ColumnName] nvarchar NOT NULL, [Permission] [int] NOT NULL, [Roles] nvarchar 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.

Updated Connection strings

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.