This page is old, and Electronic Locks have changed. Please contact James Quinlan for current information. 6/16/06

SECS Electronic Locks

Current System (Winter 2006)

I have implemented a Grizzly Code system. Professors access their class lists in SAIL, copy and paste it into Excel, and email me the Excel file. I save the Excel file onto the hard drive of the laptop, then modify it so that each row contains student last name, first name, Grizzly number, professor of class, and lab room numbers. I then create a few cells that have formulas that cut short, modify, and concatenate to some up with the access Credential ("9"+ right 7 digits of Grizzly number) and Group (shortened, concatenated form of rooms and professor). I then copy and past the individual Excel files into one master file.

The master excel file needs some more work. One possible error is if a student is listed twice or more from having multiple classes. I set up one cell to check whether the last name of the current row is the same as the next row. I then sort the entries by last name, and look through the lists for any "TRUE"s. If there is a true, and the first names are the same (middle initials don't always show up in adjunct professor's class lists), I do a little editing. Specifically, I try to copy one row's rooms and professors into another's extra rooms. It looks funny, but if it communicates what rooms this person needs and what professors he/she has, it has done its job. I then erase the extra rows.

Another error check that I am working on involves redundant punch codes. The system thinks of some numbers as the same: 1&2, 3&4, 5&6, 7&8, 9&0. (All grizzly numbers start with 0. I start the punch code with 9 because the system doesn't like starting zeroes. The students don't need to know the difference, because 9&0 are the same button on the lock). The system will accept regular numbers, but it won't allow students to use the same punch sequence, i.e. it will reject 90111112 if somebody already has 90111111. In these cases, I smash the two last names together, and the two front names together, and the rooms together, giving two people the same identity. This is still pretty secure, since nobody will know who has a "doppelganger".

As you can see, I use Excel a lot in the lock programming. It has a lot of good functions for error checking. In the future, someone may wish to build macros that would check for errors and auto correct.

After the errors are taken care of in Excel, I make sure no cells have #REF in them (the LockLink Express III program has a problem with them). I save the Excel file as a tab-delimited text file. I then open LockLink Express III and choose import users. I choose the text file, and configure the categories so last name becomes Last Name, first name becomes First Name, punch code becomes Access Right:PIN, and group becomes Group, and the rest is ignored. I then import. All goes well, there will be no errors. If there are errors, they will be listed in another text file.

Once I have the users imported, I go to Access Rights in LockLink, and choose Select By Door. I then select each group name (in order) that includes that door number, and click on add, select all the people in the pop up list, and choose Add. Going through all the doors, and all the groups, gives everybody the access right they need.

Professors never email me lists all at the same time. The first ones need access before the last ones have sent me their list. The best way to handle this is by reimporting the Master Excel list everytime. Specifically, I copy the newer lists into the master list. I then do the proper error checking and correcting. I then erase all users from LockLink except for Administrators. Finally, I reimport the master list.

As for students, they never need to register anything. They just need to go up to a lock and punch in their Grizzly number (without the "G"). The one exception is the glass door, which is the same number for everybody (221637). It doesn't have enough memory for all the users.

As a full-time staff member using the numbers in a non-revealing fashion, I have been given permission by OU's legal council to collect and use the students' grizzly numbers. Faculty don't need to give me their Grizzly number if they don't want to. Those who want access without giving me their number will get a, 8-digit number chosen by my, usually designed to be easy to memorize. I always start them with two 9s or 0s, same as the G numbers.

For some labs I have also kept up the tradition of a 5-digit number for everybody. I make sure it doesn't start with two 9s or 0s. I give the number to the professors, who pass them out to their students. Since this code is insecure, I try to persuade people to do the Grizzly thing first. Anyways, the 5-digit numbers belong to "ghost" users who belong to the Administrator Group.

-James

Programming Instructions (Lots of reading, some of it redundant, some of old, all of it from Fall 2005)

The software that manages all users and programs the lock is called "LockLink Express III". There is only one copy of it, loaded on the Dell laptop in 145 DHE. The software was registered by James Quinlan for Oakland University.

The software allows you to create objects called "Users" and "Doors". Doors have a lock type (CM) and a Holidays schedule (Semester Break).

Users have a Last Name, Middle Initial, and First Name. They also have address and phone number fields, which I left blank. They also have a Group, which is the only item other than name that you can sort by. The selection of Groups is the most important element for managing the users.

Users also have Credentials. There is usually one (the ID card, with its number) per user. Not all users are people: I have users named "temp [room #]" for rooms that need a punch code, with the credential being the code. I also have a user and credential for the one iButton that the school has. In most cases, the credential is normal, meaning that the user cannot access the lab during the door's Holidays.

So far, we have had students register their card ID online, and the professors email me their class lists and the rooms they need access to. I have been reading the online database, and adding users and their card ID credentials to the LockLink Express software. The users get an initial Group name of "?", meaning that they are not associated with any classes. Finally, some users (staff members) fall into group names of "Administrators" or "Temp Admin". Their credentials have also been assigned as Pass Through, allowing them to access labs even during Holidays.

Then I have been reading the emailed class lists. If a name on the class list is already in LockLink, I change the User's group to a group name associated with the class (usually the name/number of the class, like "SYS325"). If a name on the class list is not in LockLink, I add the user to the system and give him the class's group name. This means that when the student does register his card online, I can give him a credential and keep him in the needed class group.

Sometimes, a student has more than one class with a locked lab. In this case, I make a new group, like "SYS325 &C/E 4/570".

After handling the users and doors, go to Access Rights. A nested cycle is now performed. I usually go Select by door, and cycle through the doors. With each door, I cycle through viewing each group that should have access rights. I select Add which gives a windowed list of all people with credentials in that group who don't currently have access to that door. Select all all the people in that window, and add add and close. Keep repeating through the cycles, and everybody will be assigned their appropriate access.

I have been playing around with the user Import function in the program. It has its limitations. For one, it doesn't allow the importation of Access rights, just User Information (including Credentials). If you try to import a user with a credential that's already in use, it will still create the user (you won't be given a choice to stop it), but it won't give them the credential (and it will generate a non-fatal error that's recorded in a .txt file).

So far, I have practiced two very different approaches. The first, detailed above in the non-bold text, describes the entirely manual approach. This system makes it simple to add users, but is very slow for large groups.

The second method is best for large numbers of users. It involves the use of the Import function. First, Names and Card IDs from the website are exported into an Excel sheet. The names are delimited (Using text to columns) so that Last Name, First Name, and Middle Initial are all in separate columns. Names from many class lists (from the professors) are also imported into the Excel Sheet, and delimited. The class title (i.e. SYS325) is also recorded in the excel sheet in the same row as each student in each class. Additional information is also placed in the Excel sheet (such as Grizzly ID, userid, etc.) to help verify any strange names.

The Excel sheet full of information is then sorted according to last name. (Note: if the first row of the sheet has titles {i.e. "Last Name"} and there are no blank rows between the titles and the entries, then the Sort Command will give you sorting options with to the tiles.) I manually look through the list, and when there are identical names where one name has a card ID and the other has a couse, I move the card ID to the name with the course and erase the (now non-informative) redundant name. If the same name has a card ID and multiple classes, I (creatively) give them a new class name that shows both classes, and then move the card ID and erase the redunant (now non-informative) names.

Finally, I sort the Excel sheet according to Class and card ID (Sorting by two parameters). The users ready for access are now in neat blocks which can be moved to a new Excel page by themselves. The new page can be saved as a tab delimited text file. Then Locklink Express III can be opened, the and the import tool used. Import the text file, select tab delimited, and set the order of the information received. The new users will be imported complete with card credential and group (class name). Now, we can just go to Access Rights, select each group and door, and assign the doors to accept an entire group of names at once. Now program files are generated, and individually downloaded to the locks, and the students now have access.

This method appears to be the best, pending some improvements. For instance, the majority of faculty just email the class lists from Banner. We could request that all class lists come from Banner, which would standardize names and formats. We could then change the Register Card ID system to include separate fields for Last Name, First Name, and Middle Initial, with a message to write them exactly as they appear in SAIL (which should be the exact same as the Banner names). The names already registered would be manually changed over by us staff into the standardized form. This guarantees all identical names are exactly identical. An Excel macro could then be written to merge names with card IDs and names with classes.

Furthermore, the Excel macro could merge class names into new forms for students with multiple classes. Or, it could modify the card ID slightly so that multiple copies of each student can exist in LockLink Express, each with the same card, but each with a unique class. This last option uses a unique loophole: a Card ID can have an "=" symbol and some some small number of digits added after the real number. The lock will ignore the "=" and everything with it, but LockLink will think they are separate credentials and thus not allow both to exist. So the macro can move the card ID from one name to all identical names with classes, adding "=" and unique number to the card ID. This eliminates the need for merged class names, which cuts down the number of groups in LockLink Express, which makes adding access rights much easier.

Codes and Things

Computers and Such

Programming and Such

History

The first labs that didn't require checking out a key were equipped with lockboxes. These are mechanical devices that are shaped like a large padlock and attached to a round door handle. They contain an alphabetic combination lock and a switch. Turning the right combination allows the switch to be used and the combination face detaches, revealing the key attached to the other side. A student opens the door and reattaches the key and face to the lockbox.

Lockboxes had several problems. Proliferation of the combination to friends, lack of auditing of lab access, and frequent vandalism or outright theft of the lockbox and key.

Our solution to the lock box are the Schlage Electronic Locks seen on many lab doors today. Their initial use was similar to the lockbox: use a combination and get access. But these couldn't be vandalized or stolen like the lockboxes. And they look nice. Their battery lasts a while, and a key will ALWAYS open the lock. But they sometimes have mechanical problems that require us to call the University Key Shop.

Overall cost was about $700 per lock plus an hour of skilled maintenance time for installation. The programming instructions came on a sheet of paper, and it took maybe 10 minutes per lock to reset and reprogram the codes. But proliferation of the code to friends remained a problem.

Meanwhile, other departments such as Chemistry and Laboratory Safety started using these same locks. But they purchased a lock programming kit that allow them to use some generic purchased cards to access the rooms with electronic locks instead of keys or combinations. The University ID Card Office only supported networked electronic locks to be used with the Grizzly ID; these networked locks costed $4000 and not many people wanted to pay that much.

One day I asked Dominic Luongo for a demonstration of his lock programmer. For fun, I attempted to use my Grizzly card with the system. Lo and behold, it read the number on the card. This proved we could come up with our own card swipe system for much cheaper.

Electronic Lock System (last modified 2007-02-22 18:30:29)