With identity theft on the rise and regulations getting tighter, Grant Adult Education (now renamed to Twin Rivers Adult School), decided to take proactive action to change all Student ID numbers from being the students’ Social Security Numbers (SSNs) to a random ID.
While in the end, it took a short piece of SQL code to make this happen; this code changed tens of thousands of records, representing every student record for the history of the school’s database. Because of the tremendous potential for damage and unintended consequences, this project required special care.
First, it was important to ensure that this change would break the database. Thankfully, the student information system (SIS) being used by the school was ASAP, which followed a best practice of separating the Student ID field from the internal primary key field. If this had not been the case, this task would have been much more risky and required more work, because changing the field in the student’s table would disconnect those records from every other previously connected table in the database.
The next issue was to ensure that there would be no duplicate numbers with the new Student ID numbers. In computer science, if two generated identifiers ended up being the same, this is called a “collision”. It was critical that this didn’t happen, because it would break the database if two different students somehow ended up with the same ID. So a standard random number generator would not work, because it could generate two of the same numbers. Thus, a custom pseudo-random number generator was created that converted sufficiently unique parts of each student’s information into a number that would avoid any data collisions.
Last, even though it seemed that things should work; there were important precautions. First, a copy of the database was made, and put it in a “sand box” (a separate server so that the database can be played with, without worrying about screwing up the real database). By doing this, a test run was accomplished, and it could be seen whether any obvious problems would arise.
But that alone would not be sufficient caution for doing this system-wide change. Next, through careful coordination with other IT department members, outside of regular work hours, the database was backed up, and was on the ready to be restored. Then the change was made.
At this point, the database was checked to see if anything seemed amiss. But, problems with data don’t always show up at first. So an email was sent to all staff, to inform them of the change, and to keep a look out for anything that seemed strange in the coming week, so that they could quickly report it, and it could be investigated.
Through these careful steps, the database was successfully changed, and the students became more protected from potential identity theft.