Using the ASP.NET membership provider database with your own database?

Question

Hello everybody,

We are developing an ASP.NET MVC Application that currently uses it's own database ApplicationData for the domain models and another one Membership for the user management / membership provider.

We do access restrictions using data-annotations in our controllers.

[Authorize(Roles = "administrators, managers")]

This worked great for simple use cases.

---

As we are scaling our application our customer wants to restrict specific users to access specific areas of our ApplicationData database.

Each of our products contains a foreign key referring to the region the product was assembled in.

A user story would be:

• Users in the role NewYorkManagers should only be able to edit / see products that are assembled in New York.

We created a placeholder table UserRightsRegions that contains the UserId and the RegionId.

How can I link both the ApplicationData and the Membership databases in order to work properly / having cross-database-key-references? (Is something like this even possible?)

All help is more than appreciated!

Answer 1

It sounds like you might need to create your own customized Membership Provider. You can probably (not positive here) extend the existing one so you don't have to completely reinvent it. Here is a video from ASP.net that describes how to do that. Google "asp.net membership provider" for tons more.

Answer 2

You can try rolling your own membership or just extend is like Dave suggests.

Create your own [Users] Table which can be populated based off the aspnet_Membership table. So therefore you could have more control over it.

You could also just implement a more involved Profiles system. The .NET team has improved the way profiles are stored now, so instead of "blobicizing" them, you can set them up to be stored in an actual table now [thank god].

Table Profile Provider

Answer 3

In my opinion, you should be able to integrate your database with the standard aspnet_db reliably, but I would advise against duplicating or replacing the aspnet_users table.

That is the focal point of all the providers that use the aspnet_db schema, including custom providers that may augment but do not implement custom replacement.

To maximize reuse of strong tested infrastructure code in the provider stack/API it is best to go with that flow.

You will want to be very attentive to any modified membership core functions and ensure that the way your new constraints behave in an expected fashion in each case.

The facet of the membership story that I have found needs the most attention is deleting a user, and a simple modification/addition to the delete user sproc can manage this capably.

Answer 4

If you find the right articles, it's really easy to extend the membership provider to allow for extra functionality. I've moved my users table to my main SQL server table and have written my own role manager that gets values from a separate table. What it sounds like you need to do is to set up a table in your users DB with the location of each user, then create a method on the user object something like "GetLocation()" that returns the user's location from the DB, you could then user that to filter your data from your main DB. Here's a few articles I had kicking aroundin my bookmarks, see if they help, if you have a look on the main ASP.NET site or google for membership provider extending articles, there are plenty around. http://msdn.microsoft.com/en-us/library/ms998347.aspx http://www.4guysfromrolla.com/articles/120705-1.aspx http://msdn.microsoft.com/en-us/library/aa479048.aspx

Answer 5

As the others have pointed out there are many good resources available that can help you with creating your custom provider using the existing database.

It looks like you are headed in the right direction with mapping tables. I think the one piece you are missing is Distributed Queries. This link is specific to Sql Server 2008. There is a link there to the documentation for Sql Server 2005 if that is what you are using.