Need Quality Code? Get Silver Backed

Custom Role Providers with Windows Authentication - Part 1 (Repositories)

2ndJun

0

by Gary H

Rank does not confer privilege or give power. It imposes responsibility.

Peter Drucker

One of the most useful areas of ASP.Net MVC is the ability to restrict access to actions based on a users role. In this article we will look at how we can leverage a custom RoleProvider to work alongside Windows Authentication so that we can maintain an application specific collection of roles without needing to move users into AD groups.

We will build on the principles we previously covered in our article on the repository pattern and look at a sample that abstracts away our data layer for future unit testing. We will also show how to map some existing AD groups automatically onto our own roles and how to automatically create a local user from an authenticated windows login.

Preparing our database

Step one is to create our database structure. In this sample we will model a simple User with an AD name, email address and display name. We will also create a simple Role with an ID and Description and a mapping table that we will use to link Users to Roles. Finally we will pre-populate our role table with a handful of roles that we want in the system.

CREATE TABLE [dbo].[Role] (
	[Id] uniqueidentifier NOT NULL ,
	[Name] nvarchar(250) NOT NULL ,
	PRIMARY KEY ([Id])
)
GO

ALTER TABLE [dbo].[Role] ADD CONSTRAINT [UQ_Role_Name] UNIQUE ([Name])
GO

INSERT INTO [Role] VALUES 
	('B51662EE-398B-4CB4-A38B-83DCC000B888', 'Administrator');
INSERT INTO [Role] VALUES 
	('94D61BC2-6DEA-41A9-BC38-E6CD0B203F21', 'Development');
INSERT INTO [Role] VALUES 
	('ECA07E90-85F2-42D1-A4B7-82059F7324A2', 'Approver');
INSERT INTO [Role] VALUES 
	('2BE8C9DD-778A-41F5-87C2-7D6910A363BA', 'Reviewer');
INSERT INTO [Role] VALUES 
	('DEE897B6-06F1-45FE-812D-9F0154FA1F8F', 'User');
GO

CREATE TABLE [dbo].[User] (
	[Id] uniqueidentifier NOT NULL ,
	[ADName] nvarchar(250) NOT NULL ,
	[DisplayName] nvarchar(250) NULL ,
	PRIMARY KEY ([Id])
)
GO

ALTER TABLE [dbo].[User] 
	ADD CONSTRAINT [UQ_User_ADName] UNIQUE ([ADName])
GO

CREATE TABLE [dbo].[UserRole] (
	[UserId] uniqueidentifier NOT NULL ,
	[RoleId] uniqueidentifier NOT NULL ,
	PRIMARY KEY ([UserId], [RoleId])
)
GO

ALTER TABLE [dbo].[UserRole] 
ADD CONSTRAINT [FK_UserRole_User_Id] FOREIGN KEY ([UserId]) 
	REFERENCES [dbo].[User] ([Id]) 
	ON DELETE CASCADE ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[UserRole] 
	ADD CONSTRAINT [FK_UserRole_Role_Id] FOREIGN KEY ([RoleId]) 
	REFERENCES [dbo].[Role] ([Id]) 
	ON DELETE CASCADE ON UPDATE CASCADE
GO

Prepare Models

With a schema in place we move onto preparing our models. We will build on the techniques used in our previous article, creating an interface and POCO (Plain Old Code Object) model for both our User and Role.

public interface IUser : IModelBase
{
	string ADName { get; set; }

	string DisplayName { get; set; }

	string EMailAddress { get; set; }

	IList<IRole> Roles { get; set; }
}

public interface IRole : IModelBase
{
	string Name { get; set; }
}

The Models are just straight implementations of the interface. As we are building on top of Dapper and Dapper Extensions we will implement a custom mapper for our User class so that DapperExtensions ignores the Roles collection.

public class User : ModelBase, IUser
{
	public string ADName { get; set; }
	public string DisplayName { get; set; }
	public string EMailAddress { get; set; }

	public IList<IRole> Roles { get; set; }

	public User()
	{
		Roles = new List<IRole>();
	}
}

public sealed class CustomMapper : ClassMapper<User>
{
	public CustomMapper()
	{
		Table("User");
		Map(f => f.Id).Column("Id").Key(KeyType.Guid);
		Map(f => f.DisplayName).Column("DisplayName");
		Map(f => f.EMailAddress).Column("EmailAddress");
		Map(f => f.ADName).Column("ADName");
		Map(f => f.Roles).Ignore();
	}
}

Create Repositories

We will start with the base repository as in our previous article and start by adding the methods we will need to interact with our Users and Roles. Beginning with our User repository we will want to override the Get method to support retrieving a User by both their ID and their AD Name and to automatically populate their roles. We also want to override the Save method so that we save that users roles back to the database.

public class UserRepository : BaseRepository<IUser, User>, 
								IUserRepository
{
	public override IUser Get<TId>(TId id, 
		System.Data.IDbConnection existingConnection = null)
	{
		return WrappedDbAction(existingConnection, conn =>
			{
				if (!(id is Guid))
				{
					var usr = conn.Query<User>(
						"SELECT * FROM [User] WHERE ADName=@adn", 
						new {adn = id.ToString()}).FirstOrDefault();

					if (usr != null && usr.Id.HasValue)
					{
						usr.Roles = conn.Query<Role>(
							@"SELECT [Role].[Id], [Role].[Name] 
							 FROM [Role] INNER JOIN [UserRole] ON 
							 	[Role].[Id]=[UserRole].[RoleId] 
							 WHERE [UserId]=@Id", 
							new { Id = usr.Id.Value })
							.Cast<IRole>()
							.ToList();
					}
					return usr;
				}


				var multi = conn.QueryMultiple(
					@"SELECT * FROM [User] WHERE Id=@id; 
					 SELECT * FROM  [Role] 
					 INNER JOIN [UserRole] 
					 	ON [Role].[Id]=[UserRole].[RoleId] 
						WHERE UserId=@id;", 
						new { id });

				var user = multi.Read<User>().FirstOrDefault();

				if (user != null)
				{
					user.Roles = multi.Read<Role>()
								.Cast<IRole>()
								.ToList();
				}

				return user;
			});
	}

	public override Guid Save(IUser source, 
		System.Data.IDbConnection existingConnection = null, 
		System.Data.IDbTransaction transaction = null)
	{
		var conn = existingConnection ?? CreateConnection();
		var txn = transaction ?? CreateTransaction(conn);

		try
		{
			var id = base.Save(source, conn, txn);

			if (source.Roles != null && source.Roles.Any())
			{
				conn.Execute("DELETE FROM [UserRole] WHERE UserId=@id", 
							new {id}, txn);

				foreach (var role in source.Roles.Where(r => 
										r.Id.HasValue))
				{
					conn.Execute(@"INSERT INTO [UserRole] 
									(UserId, RoleId) VALUES (@id, @rid)", 
									new { id, rid = role.Id.Value}, txn);
				}
			}

			txn.Commit();
			return id;
		}
		catch (Exception)
		{
			txn.Rollback();
			throw;
		}
		finally
		{
			if (transaction == null)
			{
				txn.Dispose();
			}

			if (existingConnection == null)
			{
				conn.Dispose();
			}
		}
	}
}

The Role repository by comparison is easier to deal with requiring a method for looking up a role by it's name.

Wrapping Up

With the repositories and models ready we have our groundwork in place. The next steps are to wire this up to your dependency injection framework of choice prior to using it in our role provider. In our Next Article we will create the Role Provider, add it to our site and look at AD integration.

Part 2 - Role Provider and AD Integration

C# , MVC , Patterns

Comments are Locked for this Post