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.