Rolling one’s own when it counts

I spent most of the last two weeks dealing with English Learner reporting requirements for CALPADS.  You don’t want to know about this, unless you are in the California school system somewhere. If you are in the California school system somewhere, you probably already do know more about this than me.

I’ve been ranting about CALPADS on and off to C since I got this job. Turns out, all this time, he thought I was saying “cowpads” as a polit-er alternative to “bullsh*t”. Turns out, all this time, he wasn’t far wrong.

While waiting for various data-cleansing trials to complete, I “amused myself” by working on an application framework for housing Student Information Systems (SIS) extensions. We need some place to attach functionality into the SIS that SRCS uses.  It’s not a particularly bad SIS, it’s just occasionally insufficient for our needs, like any vertical market application tends to be.  When it’s insufficient, we tend to write our own applications to fill the gap. 

What my framework is going to try to do is perform some of the plumbing for our extensions in a consistent way.  You know: navigation, error-handling, and access/security.

Why should a DBA be involved with this work,  you ask? 

Simple.  The way our SIS extensions can integrate with our SIS successfully is on the database level.  We can read and leverage its data, and it doesn’t offer much else in the way of APIs.  Pretty sad state of affairs, really, but not uncommon. 

Eventually we’d like to handle our integration chores through SIF instead of reading the database directly, but even then we’re dependent on the SIS vendor to expose proper services.  At least, at the database level, we don’t have to wait for a vendor to expose the proper web service calls; we just write some SQL code.

So it was that I found myself rolling my own custom ASP.NET Role Provider this week.  This is surprisingly easy to do, and there are many blog posts and tutorials on how to do it.  I wouldn’t be contributing much to the public treasure trove of knowledge if I wrote a walkthrough on creating a provider.

But I do think there are one or two valuable cents that I should throw into the pot.

You’re far too keen on where and how, but not so hot on why

 

Today’s tag line comes from JC Superstar.  Far be it from me to suggest that Microsoft User Ed has something in common with a deity, but …

Time after time you get a feature, information on how to use that future, even sometimes information on how you could extend that feature… but not a word on why you would want to.

This is definitely something I have ranted about before.  And it’s definitely a close cousin to demos that show you “how easy it is” to use a feature without any depth, something I have ranted about recently.

So, okay.

Why a custom role provider?

How, in your organization, do you define who is in what role for a web application? 

Do you have custom attributes in Active Directory?  Our organization doesn’t do that.

Do you have well-thought-out Windows groups that can be leveraged directly for this purpose?  We don’t.

Our SIS, like most, is a sql database that contains a wealth of information about each administrative and faculty user.  This data can be leveraged directly from the sql database for the purpose at hand.  The problem is that the data isn’t well-organized for the purpose at hand.

Actually it’s not organized at all for the purpose at hand.  To evaluate membership in one role, I’ll probably have to parse it out of job titles.  For another role, I’ll evaluate association with specific schools and department relationships within those schools.

Probably the best way to do it, in our case, is to have a view specific to each role we need, where the logic in each view has absolutely nothing in common with any other.

Out-of-the-box opportunities for SQL role management

As I’m sure you know, ASP.NET comes provided with a SQLRoleProvider out-of-the-box.  It talks to a specific database, with a specific schema, which you can manage through the ASP.NET Web Site Configuration tool, at least while you’re figuring stuff out.  Eventually you’ll create an admin interface that manages roles through the Role Management API. You can also script roles and role membership by invoking the db’s convenient sprocs, which is what the APIs do, of course. 

Sql scripting is convenient when you have a lot of roles and role members to handle, or when you haven’t created your own admin interface yet and don’t want to be bothered hacking ASP.NET Web Site Configuration to work when and where you want it to.

I considered employing sql scripting on an ETL basis: use SSIS to refresh the standard asp.net schema based on what the SIS told me, on a nightly basis. While it wouldn’t be realtime, staff users are already comfortable with the idea that some of their information in the SIS is refreshed nightly from an external HR source. 

If we already had a non-transactional DW db available — and we eventually will — I probably would have gone this way, migrating as much of the asp.net schema into the DW as I needed, which probably would have been more than I really wanted, but not too much of a PITA for a lazy DBA trying to avoid .net development work whenever possible!

I also considered hacking the standard asp.net membership and roles db procedures and views to look at SIS instead of its own tables.  While this approach allows me (the DBA, remember?) to do as much of the work in SQL as possible, it doesn’t have a lot else to recommend it.  You’ve got the overhead of another database that you don’t really want to use, you have to figure out where to site it where it can reliably and safely talk to your real data, and somebody else is going to come along and really scratch their heads trying to figure out why changes in ASP.NET administrative interfaces, or other normal methods, don’t work as expected.

Why a custom SQL provider is better

The standard asp.net membership and roles schema is essentially arbitrary.  If you have role and membership information someplace else, whether well- or ill- organized, and if “somewhere else” just happens to be a sql database, writing your own provider makes a lot more sense than twisting yourself into knots to fit, or refresh, that arbitrary schema.

The provider has a number of methods, but you don’t have to implement all the ones that handle administration of roles and members.  After all, your maintenance of role members is being handled by another application (the SIS) in the first place, remember?  All you really need to do is satisfy one runtime requirement: is a current user in a role, or is the user not in that role?  You do this, and everything else pretty much just works.

In my case, the critical method boiled down to this:

Public Overloads Overrides GetRolesForUser(ByVal username As String) As String()

   Dim cmd As String = “exec srcs_sp_SISX_GetRolesForUser @UserName = ‘” & username & “‘ “
   Return SQLUtils.GetGenericList(cmd)
   — where SQLUtils is nothing special, just whatever you usually use to…
   — return a list.  You *will* need such a method, but it’s the only one.
   — The provider interface works pretty much entirely in String() and Boolean
   — return values.

End Function

… with everything else that is needed at runtime for access and permissions piggy-backed on top of that one:

Public Overrides Function IsUserInRole( _
   ByVal username As String, ByVal roleName As String) As Boolean
 
   Return(GetRolesForUser(username).Contains(roleName))

End Function

Nothing else was really pertinent and, as you can see, all the “real” work is still done in SQL where I (the DBA, remember?) can be happy about it, not to mention change it later.

I’m not sure what the standard SQL provider code looks like (to be honest, I didn’t look to see if it is open source — drop me a line if you know).   It’s probably not much more than this.

Note that this a “dumb” implementation; the single stored procedure I’ve written so far has to union the result of all the views representing existing roles. If I wrote a separate stored procedure for the second function above, instead of calling the first, passing the sproc both the user name and the role required, it would be more efficient. 

Still, even in my “dumb” implementation — which incidentally puts the stored procedure in another database, on another server, from the SIS for now — it runs surprisingly fast.  There is no noticeable lag evaluating roles, whether from code I call from a page to evaluate user privileges or from native ASP.NET code, such as dynamic role-driven menu item access.

The asp.net standard roles and membership schema is arbitrary.  You’ve already got a database that has what you need, in another schema.  Your database and the asp.net database probably  have nothing in common, whether yours is well-organized or not.

When you look at it that way, why wouldn’t you write a custom SQL provider?