This June, we’re finally, finally going to stop using SSN as a primary key at UMBC, and frankly, the big day can’t come soon enough. And it’s not for the reasons you’d think. Yeah, there are a lot of privacy and liability issues at stake, but putting that aside, it causes us nothing but headaches. For starters, a primary key is supposed to be a unique, non-changing entity. SSN is neither of these. This is particularly true for international students. Many of these students arrive at UMBC without a permanent, government-issued SSN. We issue these students a temporary, bogus SSN, so we can get them into our various systems (SIS, HR, etc). Then, sometime during their stay at UMBC, they get a permanent SSN. And, depending on how the change percolates through our various systems of record, this wreaks unholy havoc on the student’s myUMBC account.

Nothing will illustrate this better than a real-life example. Read on for the gory details.

[More:]

Today I looked at a grad student, who we’ll call Larry (names changed to protect the innocent). When Larry logged into myUMBC, he was only getting the Personal and Support tabs. Well, it turns out that Larry is an international student. He’s also a graduate assistant, which means he has an HR entry as well as a SIS entry. He’s in SIS under his temporary, bogus SSN, and he’s in HR under his real, permanent SSN. As a result, he has two entries in our LDAP directory, one for each data source. When I do an LDAP lookup on his account, it brings up the entry from HR, which is lacking a student affiliation. Thus, Larry doesn’t get any student content in myUMBC.

Now, the first thing to do here is to get Jason to merge the two LDAP entries together. In the meantime, I can grant Larry a temporary student affiliation, which will give him the tabs he needs to see. But, that’s still not enough. SIS still has his old, temporary SSN. When the portal goes to look up Larry’s class schedule, it’s going to use the new, correct SSN, and it’s not going to find any info there. So Larry gets an Academics tab full of blank channels.

The permanent solution here is to get the registrar to update Larry’s SIS records to reflect the new SSN. However, for reasons beyond my comprehension, there always seems to be an interminable amount of time between requesting this and actually seeing the change reflected in SIS. In the meantime, we have to do something so that Larry can access myUMBC. Which brings us to…. the SSN translation table.

For students like this, I have an SIS table (AUXIL.MYUMBC_USER_PREFERENCES) where I store the student’s username, and the student’s SSN as shown in SIS. When the student logs into myUMBC, the portal first checks this table. If it finds an entry matching the student’s username, it uses that SSN to override the one it gets from LDAP. So for Larry, I just plug his bogus SIS SSN into AUXIL.MYUMBC_USER_PREFERENCES, and presto, myUMBC magically starts working for him.

But, we’re still not done. In effect, we’ve created a time bomb. Because eventually, SIS is going to get updated to reflect the student’s real SSN. It could be a week from now, it could be a year from now. But when that happens, the overridden entry will still be out there, and it will continue to map the student to the old SSN. So at some point, Larry’s myUMBC account is going to break again. At that point, I’ll need to delete his entry from AUXIL.MYUMBC_USER_PREFERENCES, and then he’ll finally be fixed for good.

I’ve been dealing with this (ahem) CRAP (I’ll keep this PG-rated) for 6 years now, and once we finally stop using SSNs as primary keys, it’s finally going to end. Students will get an SIS ID, it’ll be unique, it’ll never change, these problems will be gone, and the world will be happier. At least in theory.

Just to document this for future reference.. In uPortal, the AUXIL.MYUMBC_USER_PREFERENCES lookup happens in the UMBC Person Factory (edu.umbc.uportal.UmbcPersonFactory), via the UMBC Local Connection Context (org.jasig.portal.security.UMBCLegacyLocalConnectionContext).

Followup.. in a sudden stroke of brilliance, I added a join against the SIS BADDR.MNAME table to my overridden SSN query. If there’s no BADDR.MNAME data for the SSN, it won’t use it. That way we automatically stop using overridden entries once they become obsolete. That should be a big help.

Urrgh. I added new functionality to the Perl myUMBC codebase today, and I had forgotten what a pain it was. You know things are bad when you wrote the code, and you have to spend an hour trying to recall how it works. Since it looks like the Perl stuff is going to be around for awhile, I figured I might as well document this process while it’s fresh on my mind. Maybe it’ll save me some time down the road.

Just to clarify… when I speak of myUMBC here, I’m just talking about the Perl stuff, not uPortal. With that in mind, here goes.

Background: Everything in myUMBC is database driven. Every link, table, dropdown menu, heading, etc. that you can see, click on, or otherwise manipulate, has a corresponding entry in a database table. All internal URLs in myUMBC are of the form

http://blah.blah/myumbc?function=foo

The function code tells myUMBC what the end user is currently doing. Most of myUMBC’s core functions (registration, etc.) actually have two function codes, one for the initial screen, and one for the results page. Every function code needs to have an entry in the database. Otherwise the user will get a “not authorized” type error. This is to keep users from entering arbitrary function codes and producing undefined results. The database table that contains all of this stuff is called BRCTL.PROD_PROG_DESC (don’t ask about the name).

To add to the confusion, myUMBC also has a built-in function mapping table, which maps shorter function names to possibly-longer Perl subroutine names. For example, the Financial Aid Inquiry function I added today has a short name faidinq which maps to a subroutine finaid_inquiry. There’s no real reason for doing this, other than shortening the URL.

To make a long story short, first, you need a PROD_PROG_DESC entry with PPD_LINK_TYPE set to “function” and PPD_URL set to the name of the Perl subroutine that builds the initial HTML (which could be a table, dropdown etc). Then, you need a second PROD_PROG_DESC entry for the results page. This one should have PPD_URL set to NULL, and PPD_FUNCTION_NAME should match the Perl subroutine that generates the results page.

That’s basically it… unfortunately, that probably didn’t make much sense unless you’re already intimately familiar with the myUMBC Perl codebase. One of these days I’ll improve and expand on this, to make it fit for general consumption. That’s why it’s in my blog and not the Syscore Wiki..

[More:]

6/16: Here’s how I made our new Student Parking Registration form come up:

INSERT INTO prod_prog_desc

    (ppd_prog_id, ppd_url, ppd_desc, ppd_add_date_time,

     ppd_add_user_id, ppd_link_type, ppd_restrict_access,

     ppd_auth_level, ppd_function_name, ppd_portal_disposition)

VALUES

    ('NEWPARK', '%2bParkingRegistrationForm',

     'Student Parking Registration (new)', sysdate, 'paulr',

     'myumbc', 'N', 'password', '+ParkingRegistrationForm', 'maximized')

Great to have notes to refer back to..