I don’t like writing about something that I’m “currently working on” too often. The problem with that is sometimes other things (e.g. life) get in the way of my proposed timelines. All that aside, there are times when the standard SQL web providers are overly complicated for the rest of my database schema. I think it’s great that you can use the same tables for multiple applications. It’s also great that the primary keys for most of the entities are GUIDs, since these are much harder for an unauthorized user to guess.

Sometimes it’s all just too much!

Integration with the Rest of Your Schema

The default implementation of the ASP.NET providers are not ideal for integrating with the rest of your database schema. By that I mean, if you want to include a foreign key to a user’s ID in a table that you’ve created for your application, you’ll have to play by those default rules. Your foreign key will have to be a unique identifier (SQL Server’s GUID data type).

What if I’ve already defined my entire schema and the User ID I had in mind is an integer?

Write Your Own Providers

Something that I truly appreciate with ASP.NET since version 2 are providers. Being able to customize the implementation details of specific pieces of a well-defined machine is empowering. Unfortunately, for the membership, role and profile providers, your data for each will more than likely be interdependent. For example, you cannot use the default SqlMembershipProvider with a custom role provider that does not link to the aspnet_Users table or use a GUID for the User ID when associating roles to users. If you want to change the data type of the primary keys for some entities, you’ll have to write custom providers for all aspects you wish to use in your application.

It’s also not as simple as just changing the data type from a uniqueidentifier to an int. Ideally, the flexibility to configure the providers to use one of your own existing tables in the database would be more robust than hard-coding several table names into the code. What if the provider uses the correct database provider based on the supplied connection string? How about the option to use dynamic SQL or stored procedures?

These would all be very nice features for a set of ASP.NET providers. Of course, I have a real-world example. My host lets me create MySQL databases for my website. They have MySQL 5 installed, which has the ability to create stored procedures (after a bit of a wait). The only trick here is, stored procedures in MySQL 5 are actually stored in the “mysql” database. This can pose a problem for security in a shared hosting environment. Everyone’s stored procedures are in the same “bucket”! It would be great if I could configure my membership and role providers to use MySQL and build dynamic SQL instead of relying on stored procedures.

They’re Coming

That’s the plan. I am currently writing ASP.NET providers that offer all the benefits mentioned above. When will this project be complete? Who knows with my schedule! I can tell you that I have a couple of big websites that I’m working on that will require MySQL as the backing database. They also require membership and roles support. I guess I’ll be finishing the providers in order to complete those websites. ;-)

I work for a community college as their webmaster/web programmer and things tend to be very disparate with regards to systems. The student information system speaks its own language and interfaces with its own data. The Active Directory trees are updated manually each semester. The website has several internal and external tools that various users can use to achieve certain goals.

When trying to communicate across these and other involved systems, it can get a bit crazy. The Web tools are my primary concern. They require authorization granularity that just is not offered via our Active Directory (AD) layout. For example, some of those tools have some users that share a similar AD group and other users that do not. In addition, not all users of an existing group should have access to a given tool. Yet specific collections of users needs access to these tools and I am responsible for granting such access.

Read the rest »