Check out the new USENIX Web site.


LISA '05 Paper    [LISA '05 Technical Program]

Manage People, Not Userids

Jon Finke - Rensselaer Polytechnic Institute

Pp. 263-277 of the Proceedings of LISA '05: Nineteenth Systems Administration Conference,
(San Diego, CA: USENIX Association, December 2005).

Abstract

Despite the title, this is not about managing people, but rather managing the enterprise data about the people, especially in defining the relationship between a person and the organisation and controlling functions based on that relationship, or what some people might refer to as identity management.

Single sign-on is an attractive goal for many organisations. When you include parking gates and badge readers on building entrances, the problem gets even more interesting. As we expand our deployment of wireless access points and publically accessible network jacks, the need to require authentication for access to our virtual world grows stronger. With the need for authentication, so grows the demands on the systems that provide authentication and authorisation, especially in the area of managing who gets access and revoking that access at the appropriate time. Concurrently, with the rising interest in physical security of our facilities, the need for authentication and controlling access to our physical world is also growing. This also requires tools and systems to manage the people and their status and privileges.

Both of these issues share many common attributes and can be well addressed by merging them into a single system to manage people information, and from that, access to the virtual (network) world as well as the physical world. By combining these projects, we are able to take advantage of the mandate (and administrative support) to identify all of the people on our campus to provide physical access control, and so, manage our virtual world. We will also attempt to define a somewhat generic or standard methodology for doing this with our particular business rules and requirements confined to a few limited and specific areas.

While the technical issues are challenging, the more daunting task comes with negotiating the institutional politics and getting adequate "buy in" from the appropriate departments to provide the people and resources willing to operate and use the eventual technical solutions. This paper discusses both the social and technical aspects of those solutions.

Introduction

There have been a number of systems developed over the years to manage user accounts, Moira [12] in 1988 (part of MIT's Project Athena), to Accountworks [2] 10 years later and many others. In many ways, this process is pretty well understood, and many mechanisms exist for taking a user account and getting it the end system, be it Windows 2000 [7] and others [10, 11, 3]. In A Retrospective on Twelve Years of LISA Proceedings [1], the authors identified 23 papers dealing with account management. Under the section Future Research Opportunities they write:

Like the organisations cited above, we here at RPI, have been developing our own system (Simon) for automatic creation and management of computer accounts. Since Simon's inception in the early 1990s, it has evolved to maintain our telephone directory [5] and ID card and parking control systems, as well as providing a consistent data feed [9] of people and directory information to a number of other systems on campus. This paper is in part a case study on how we addressed both the political and technical issues.

The basic objective of all of these systems is to automatically create and maintain computer accounts for all students, employees, contractors, etc. as needed. Given a good data feed, this is basically a technical exercise, which has been documented in the references cited above and many other places. But in all of these cases, the differences come from that prerequisite; the good data feed (or feeds), ideally from the system of record for that data element. While every site is different, I feel that they all share some commonality and I hope to outline some approaches that may be applicable to your specific site. One of the challenges we face, is that for the most part, no one outside of the IT department really cares all that much about solving this problem. After all, it is IT's problem to solve, and it doesn't really impact people outside of the IT department at all. We found that by expanding the scope of our project from just computer accounts, to other "people" related activity such as the phone directory or the ID card system, we were able to get the interest and assistance of some of the key players outside of the IT department.

From a technical standpoint, while I don't propose to describe the universal tool in this paper, I do hope to describe a close to universal methodology that can be applied to most educational or corporate[Note 1] environments. As suggested in the above quote, the problem is to define a generic enough tool that it can handle the requirements of different installations, while still allowing the easy application of the appropriate business rules with minimal changes to the code base.

For the most part, dealing with data feeds from Human Resources (for employee data) and the Registrars office (for student data) is well understood. The challenge we face is dealing with the "none of the above" categories.[Note 2] In an ideal world, we would find a way to push that maintenance load out on the departments and offices. From the IT standpoint, we don't really have the juice (we can't mandate cooperation) to get departments to maintain status information, but by merging the computer account management with the more general ID card and directory processes, we are able to get sufficient interest to maintain the status information.

In this paper I hope to first describe how we addressed the political and social issues for implementing this system. I will discuss some of the arrangements that were made, and some of the arguments that were used to convince people in other departments and divisions that they needed to be part of this project and provide the operational support to make it all work. In addition, I will describe some of the philosophy and guidelines we adopted dealing with sources and maintenance of information, and some of the pitfalls we encountered.

Moving from the human realm to the technical realm, I will next describe a general schema for managing "people" (and "department") information, and given that, then describe a general, adaptable system for managing and delegating all of the oddball categories of people, and provide code examples that do NOT have our business rules built in, but are easily adaptable for any site. From this, we derive a general mechanism to evaluate and integrate the "status" of a person, in an easily adaptable and flexible manner. This in turn drives the business needs of the enterprise, be it computer accounts, ID cards, directory entries, or whatever the access and authentication needs are.

Obtaining Buy In from Others

The fall of 2003 brought to the fore, the issue that would finally get serious administrative support behind the unified people status project, and possibly a hot button topic just about anywhere, Parking. In our case, it was the installation of parking gates. Now it really mattered if someone's ID card expired - they would be denied access to our campus. In point of fact, there were at least 40 different departments or units identified as "stake-holders"[Note 3] in this project. The parking lot access project let us bring some focus to the project and get a reasonable number of key people into meetings.

Authoritative Data Sources

As part of an earlier data feed project, we found it useful to identify the authoritative data source or "system of record" for each type of data. Despite having both student and employee information in our central administrative database (SCT Banner),[Note 4] it quickly became obvious that although most people were there, not all people could be found there. Thus after much discussion, it was decided that although Banner would remain the system of record for student and employee data, the official, campus-wide system of record for people data was our locally developed Simon system. As each additional group or type of person came along, we had to identify the "system of record" for that part of the data feed.

Identifying systems of record is an iterative process. Once you identify a data element, and who in the organisation owns and maintains it, you then need to get them to agree to supply you with an on-going feed. This may be complicated by then discovering that the authority doesn't really have all the data you need. For example, we were finally able to identify the authority for building names, by asking the University President at an open "town meeting". This approach may not endear you to the lucky selectee, but it does help the process move forward. We then discovered that although we could get the "official" names for buildings, they didn't have the more common names[Note 5] or abbreviations for buildings. Abbreviations are important - we cut the size of our campus directory from 220 pages to 203 simply by using building abbreviations! We are still working on resolving this issue completely.

Our current strategy for picking up the smaller groups and data elements, is when someone asks for it, we ask them who is authoritative for that group. For example, when the Provosts office asked for a mailing list for emeritus faculty, I asked them who was in charge of that information. They admitted that they were responsible, at which point I offered them a tool to maintain that list, that would also be able to give them mailing lists, interface with the campus mail room distribution system and feed into the ID card system. They agreed, we did some training and that part of the puzzle dropped into place. It was helpful to have a tool ready to go when they asked.

Sharp Edges

Even with a good data feed, you still have to watch out for some differing expectations between the users of the system and the mechanical reality. This issue was driven home recently at the end of the fiscal year. From a database standpoint, a data value is often a time/date value. If you have a time component, there is almost 24 hours between a termination date of 06/30/05 and a start date of 07/01/05. Thus, we had a bunch of folks who terminated on June 30th just after midnight and their new position did not start until the following day at midnight. As a result, about 40 people lost their parking access when they shouldn't have. We will be implementing a "look ahead" function that when someone's job status is terminated, we will look ahead to see if they are starting a new job within the next two weeks.

Empowering the Process

Although we had identified Banner as the system of record for employee data, and the Human Resources department as the maintainer of that data, we discovered that although Banner does a fine job tracking eligible employees and payroll, it wasn't ready to handle real time operations. From a database perspective, a potential employee is entered into Banner with some demographic information, a department affiliation, etc. This can take place weeks before their actual start date, and not everyone entered here actually starts as an employee. So rather than key off of this, we would wait until payroll actually entered them and assigned them a job and job classification. When all this was doing was driving the telephone directory, we could tell folks to wait a few days and the new folks would show up in the online directory. But when this entry was required to issue the person an ID card and a Parking transponder, waiting a few days was not acceptable.

Another requirement for employment, was the completion of the IRS I9 form. Historically HR had problems getting folks to come up to their office, which was located some distance from the main campus, to actually sign these forms. So we wrote a little application for HR staff, and made a deal with them. If they would use this application to mark when a new employee was "OK", (and indicate faculty or staff status), we would refuse to issue an ID card, or parking transponder or computer account until HR set the flag. With this in place, new employees had to visit Human Resources before they could really do much of anything. This also encourages the departments to follow the HR hiring process.

The other end of the employment process is employee separation. This impacts not only the IT world, but many other departments. When an employee leaves, the key shop needs to collect their keys; if the person was responsible for hazardous materials (gas cylinders, chemicals, etc.), someone else needs to take custody of it. In addition, separation will impact benefits such as pension and health care. One recurring problem we had seen, was when departments failed to renew employees on fixed term appointments, and essentially, their job ran out. In order to deal with this and other issues, Human Resources formed a "Separation Process" committee. One of the outcomes of that process was an automatic process to generate notification to departments of who was coming up for separation. By connecting with this process at the database level, we are able to synchronise our electronic world with the "official" employment status. Now if a department ignores these separation reports, a lot of things happen automatically. This process gave us a better employee data feed, and puts more teeth in the HR process.

Another example of cooperative projects is the management of Emeriti information mentioned above. The Provosts office agreed to maintain the lists, which feed into the ID card and directory systems. The tool they use also lets them get mailing lists back, and even the ID card photos.

One of our remaining challenges, is trying to eliminate or at least reduce the number of "Director Specials". Sometimes when a new person comes on board, "helpful" people high up in the food chain would try to grease the skids by arranging for the new person's email account to be ready when they show up. In the past, they would call their favourite director in the IT division, and get them to set up special computer account. This would sometimes result in a second account being created once the normal HR process went through, to make matters worse, this second account would be the official account, included in the department mailing lists, and so on. With wide scale use of card readers and parking passes, even if the person got their email account, they still couldn't get an ID card, get parking etc. While we might have to jump when the CIO says frog, the parking office doesn't! We are gradually convincing these directors and VPs, that the best way to get a new person on board, is to get the proper paperwork to HR, and from then on, the process is automatic and painless. We have on occasion walked the paper through HR, and the other systems, but even that extra effort is much less work than trying to unsnarl manual entries that entered the system in the middle.

Data Sources

One of our goals in the design and evolution of our system, was to let other people and departments do as much of the data entry and management as possible. Ideally, they would already be doing this work, and we would just be able to tap into their systems. Although they may have been hesitant at first, the other departments seem to like this approach, if for no other reason, is that it makes them clearly the steward of their own data and individuals are encouraged to go to the "right place" to get their records and status cleaned up. Some obsolete fields have been omitted from the table descriptions that follow.

General People Schema

The key table for handling people in our system is aptly named PEOPLE(Figure 1). It has evolved over the years, with new fields being added and others being made obsolete, or on their way to being phased out. As we refine our data model, this table is moving back to it's intended role of identifying people, and other information is being moved to other tables.


           Surveying account creation practices would help identify why no tool has evolved as superior despite many papers on this subject. We believe this is because of unrecognised differences in the requirements at each site. With all of the requirements explicitly described, it should be possible to build a universal tool.
IDNumber Primary Key used to identify a person (or entity). Referenced by many other tables.
LastnameVarchar2(60)The family name or last name.
FirstnamesVarchar2(32)First and Middle Names.
PrefixVarchar2(8)Prefix for a name - generally unused.
SuffixVarchar2(8)Formal suffix to name, such as "Jr", etc.
StudentVarchar2(1) Indicates that person gets a student account - being phased out.
EmployeeVarchar2(1) Indicates that the person gets an employee account - being phased out.
GuestVarchar2(1) Indicates that the person gets a guest account - being phased out.
Student_UidNumberThe unixuid of the student account, if any.
Employee_UidNumberUnixuid of the employee account, if any.
Guest_UidNumberUnixuid of the guest account, if any.
ISO_NumberNumberISO Format ABA card number - ID card number.
FAIMS_PIDMNumberPrimary key for person identification in Banner.
Spriden_IdVarchar2(9) University ID number (Rensselaer ID Number - RIN) - assigned in Banner.
Spriden_Activity_DateDate Date of the last activity in the person ID table in Banner.
Budget_StrVarchar2(32)Default budget number for non student charges.
SSN_StrVarchar2(9)Person's social security number.
Birth_DateDateDate of Birth.
Gendervarchar2(1)Gendor of person.
Clean_LastnameVarchar2(64) A "cleaned" version of the lastname (all lowercase, with spaces and punctuation removed) to aid in searching.
Clean_Firstnamesvarchar2(32)A cleaned version of the firstname.

Figure 1: People description.

The PEOPLE table shows it's heritage as the driver for creating computer accounts, and some of the data models of earlier systems used to feed it. One of the annoying lacking, is the the absence of a Middle_Name field. When upstream systems started providing a middle name as a unique field, it was automatically merged with the first name to be stored here. Later on, a Preferred_First_Name field was provided in another table, which can be set by the individual for use in directories and displays. There is a lot of code that know the old way of doing things, and fixing this will be a non trivial task (and is waiting for some other things to be rewritten).

Another problem we have, is with the Student and Employee flags. These are[Note 6] used to control the creation and expiration of computer accounts. There have been special cases where someone needed an account, and one of these flags have been set via other means. However, we have run into cases where staff in the field see these flags set and think that the person in question is a current employee or student. I look forward to the day when these flags are gone and we rely on the status values.

The paper.css_UID fields really don't belong here, and they do imply some policy, like restricting people to just one student and one employee account, but also to just one guest account. In practice, a student or employee should NEVER have a guest account, and there are cases where someone may need more than one guest account, or these accounts should not be re-used, such as for a contractor who works for one department, leaves, and returns to work for another department.

In cases where the person in question comes from Banner (which is all students and employees), we include their "PIDM" - this is the primary key used by Banner. The general practice is that once a person is in Banner, all of their identifying information (name, ID Number, SSN, DOB and Gender) comes from Banner. The inclusion of the social security number is disturbing to some people, but it has proven very useful at the ID desk to identify people who are returning, and are already in the system from those who are truly new to Rensselaer.

One of the ongoing challenges of maintaining the data, is dealing with people who get entered into the system twice. We have a merge tool that helps us shuffle records between the different versions of a person, and marking the "bad" record so it is not re-used.


Person_IdNumberPrimary key - matches People.Id
LastnameVarchar2(64)The last name.
FirstnamesVarchar2(32)The first and middle names.
SSNNumberThe SSN or Rensselaer ID Number if available.
Entry_Type_IdNumberIdentifies different types of entries, see Id_Entry_Types.
SponsorNumberThe People.Id of the person who "sponsored" this guest, if any.
RequestorNumberThe People.Id of the person who requested this entry.
Affiliation_IdNumberAn identifier of the department sponsoring this entry.
Expiration_DateDateDate this guest entry expires (can be renewed).
Dir_Flagvarchar2(1)Flag to control inclusion in the campus directory.

Figure 2: ID_People description.

Students, Employees

For student data, we have a process that compares the Registrar's list of students with our own copy of the student list. Along with daily runs, we can update a specific student's records via a web application. We had the added challenge, in that we actually have (or had) two registrars, one for each of our campuses, and they had different practices in marking "active" students. This difference is demonstrated in the discussion of the Person_Status view below. Student processing is fully automatic now. Student with account, status or ID card issues are referred to the Registrar. The Registrar also notifies us when they recode large groups of students, so we don't panic when 1200 students drop out suddenly (due to graduation.)

In a similar manner, employee data is checked once a day with the Human Resources information in Banner. We can also do updates of a specific individual via a web tool. The Banner data for employees isn't as clean as we would like, which required a special tool for HR to use to indicate when employees really start. Fortunately, this process has provided some benefits to HR, that they are quite willing to use the tool (described previously). We did add a safety check that stops employee processing if more than 10 percent of the employees are marked as being changed.

Non-Traditional Sources

There are some groups of people, such as emeriti and retirees who are already in the system, but no longer active employees. Since we knew that these people were already in our system, all we needed to do was maintain a list of them. As part of our directory project, we had a tool available to manage mailing lists. Some minor changes gave us a version of the tool to maintain lists of emeriti and retirees. These mailing lists were then fed back into the system to provide additional status entries.

Guests

Although we had students and employee status well in hand, that still left "none of the above." With the deployment of RFID cards and Parking transponders to provide access to campus buildings and parking facilities, the problem of maintaining status information for "ID Guests" became very real. Our original practice of creating a campus computing account (which as a side effect, would generate an ID card number for them) was not going to handle this.

One of the principles that we adopted, the 13th amendment to the Constitution notwithstanding, is that everyone had to be "owned" by an on campus person or department. For regular employees, Human Resources plays this role and for students, the Registrar handles it. But for everyone else, we needed to designate an "owner". In many cases we were able to use our existing departmental directory administrators to handle this role, and for other cases we created new "departments" and assigned administrators there. These administrators are assumed to know which guests are still with their department and can expire or renew them as needed. For some categories of visitors, they can also control if that person gets a computer account and if that person is to be included in the online directory.

The primary point of contact for guests, is our ID card office. The folks there have a tool that lets them make entries in the ID_People table (Figure 2). Entries here are automatically propagated into the People table, and are also used to provide status values. Some of the fields have been omitted for brevity including some personal ID fields (SSN, DOB, etc.), but the key ones are included.

Some entries in the ID_People table will have a Sponsor specified. The sponsor will point to another person, and this used used in the case of dependents or spouse, or a personal care worker; someone whose relationship with Rensselaer is a direct result of some other member of the community. This allows us to automatically "expire" dependents when their sponsor is no longer eligible to sponsor dependents.

If an entry doesn't have a sponsor, then it will have an Affiliation_Id. This points to a department. Originally, the department tree for the ID Guest system and the department tree for directory were different. We have since merged them and although we still have two tables, the tools keep them synchronised. One of the big benefits of merging these two trees into one, is that the directory departmental administrators can maintain ID guests as well.

The ID Card Office staff use the tool to enter a new person into the system. The application attempts to locate the new person in the existing database, searching by name and by ID number. We really want to avoid duplicating people. We can still make the guest entry, but the Person_Id will match the original record. The next step is to select what type of guest they are, which then determines the additional questions they need to answer. The choices are contained in Id_Entry_Types (Figure 3). The xxx_Required flags tell the application if it needs to prompt for those fields. In the cases of sponsor and requestor, the person must be in the existing database, and in the case of a sponsor, the sponsor's status must be one that allows them to sponsor a guest. (How this is done will be discussed later). If the entry type has an affiliation id, that tells the application the root of the departmental tree it should use for the selection list. This ensures that Incubator staff must work for an Incubator company and so on. The department administration tool will expire all of the members of that department (or other organisation) is terminated. Our current list of entry types is in Figure 4.


Entry_TypeVarchar2(24)A short name for this type of entry.
Sponsor_RequiredVarchar2(1)A flag indicating of a sponsor is required for this type of entry.
Requestor_RequiredVarchar2(1)A flag indicating of a requestor is required for this type of entry.
SSN_Requiredvarchar2(1)A flag indicating if an ID number is required.
Affiliation_IdNumberAn identifier of the department sponsoring this entry.
Expiration_DeltaNumberNumber of days from now for entries to expire.
Dir_Flagvarchar2(1)Flag to help control inclusion in the campus directory.
Display_Ranknumber(3)A rank order to be used for display purposes.

Figure 3: ID_Entry_Type description.

ExpSponsorRequestorDirDisplayEntry
Entry TypeDeltaRequiredRequiredFlagRankType Id
Special Programs60NY10091121096
Temporary Employee90NYP10091318569
Conference Guest60NYN10091114523
Off Campus Contractor120NY10091393994
On Campus Vendor120NYP10091398702
Department Vendor90NYP10091402506
RU Club Member180NY10091405098
Incubator180NYP10091068656
Tech Park180NYP10191080046
DependentYNN16091449911
SpouseYNN16091449912
Domestic PartnerYNN16091449913
Personal AidYN20091449917
Visiting Researcher120NYO30091399849
Visiting Faculty120NYO30091399850
ROTC Faculty90NYY40091397007
ROTC Staff90NYY40091397008

Figure 4: Current ID Entry types.

Other flags will determine further processing. The directory flag can put someone into the directory automatically with no choice to the departmental administrator. For example, our ROTC staff go in - no choice. Other categories such as visiting researchers or Incubator staff can go in, but that can be controlled by the departmental administrators ("O" optional, default include, "P" - optional, but default to not include) and the rest don't go into the directory at all. If there is an expiration delta, that number of days is added to the current date to get a default expiration date.

Entry Points, Delegation, Short Term Visitors

Not everyone on campus is there long enough to get their own picture ID card. While they still need one for building access and dining plans, the overhead of producing ID cards for someone who was going to be on campus for three days for a conference was simply not worth it. There are a few cases where an ID card is desirable as a keep-sake, for the most part, we have a collection of generic cards (guest1-Guest999).

While that worked for the physical access, we still had the problem of visitors to campus who needed to authenticate to access our VPN to get on campus, or to access the wireless network. This would result in the request and creation of a full computer guest account, with email access, printing allocations, disk charges, and would take a business day or two to get set up, or someone in the department would "loan" their account out. While the first option was arduous, the second could result in the staff member who "loaned" the account being terminated.



Figure 5: People Status data flow.

Instead, we developed a tool that allows departmental administrators (and each department has at least one, see the next section) to allocate a temporary account that will allow someone to access the VPN to get in, or the wireless network to get out. When they allocate an account, they can specify an expiration time from 3 hours up to 2 days (accounts can be renewed), after which time, the password will be reset automatically. They can also specify a comment for that assignment that they can later review. Once allocated, these accounts "belong" to that department. Once they have expired, they are available for re-use by that department. If the department has no "free" accounts, a new one will be drawn from a pool. The pool is monitored to ensure a ready supply of new temporary accounts. All of this happens without any involvement by the IT staff - it is entirely in the hands of the department administrators.

This gives departmental administrators the ability to satisfy some of the needs of visitors to their department quickly and easily.

Departments

We really can't talk too much about managing people, without understanding what department they belong to. One of the key points of this system, is that there is someone responsible for maintaining the status information for everyone on campus. This may be indirect like with HR maintaining employee information, or the Registrar maintaining student information, or directly with departmental administrators maintaining guest information. Although there are a few categories such as dependents or spouses that are "owned" by another person, all of the rest of the guests need to be "owned" by a department. This requires a good list of departments, and possibly the relationship between departments (all of the engineering departments belong to the school of engineering, etc.).

As part of the telephone directory project, we had to get a pretty good handle on departments. When the University went to a fund accounting system with composite account numbers, one of the elements was the ORGN (Organisation). These were arranged in a hierarchy, with every orgn rolling up to it's parent and so on to the president. Our joy was short lived however. The primary purpose of the ORGN tree, was financial accounting. This resulted in lots of extra "departments" (like copy center), departments that were not "data enterable", so these were paired with an "office of XYZ" department so charges could be rolled up. To make matters worse, the department "names" were limited to 30 characters, and there was no place, nor interest from the finance office in maintaining abbreviations.

To get around this, we created our own superset of the ORGN tree, where we could add our own "virtual" organisations, specify alternate names for real organisations as well as abbreviations. An agreement was made that we would only create ORGN codes (the primary key) that started with "V", "S" or "DH", and the rest of the name space belonged to them. New ORGNs from the controller's office are automatically added to the directory (and the telecom staff double checks them - some are not included). But this gave us a place to expand the existing university departmental hierarchy, as well as build new trees for vendors, and many other special groups.

Another aspect of the department management, was to identify one or more administrators for each department. If a department did not have one, it could inherit administrators from it's parent. Although this was originally intended for managing the telephone directory, it gave is a ready made list of people who can take on additional responsibility for guests in their department. This was a key component in ensuring that status information for EVERY person on campus was the responsibility for an identified person (or department) to maintain.

People Status

Now that we have identified all of our different data feeds, it is now time to put everything together into a uniform object (Figure 5). The first step is to create an Oracle view, Person_Status (Figure 6) that combines information from Employees, Students, ID_Guests, Directory_Aux_Entries and Hartford_Raw_Dir tables. We don't want to be querying this view all of the time, and we do want to know when someone's status changes, and maintain some history. To this end, we have a People_Status table (Figure 8). And finally, we feed this information to other systems.


Lib_Patron_Typevarchar2(3)Patron Type for Library Circulation System
Status_IdNumberIdentifier for status type.
ID_Card_Statusvarchar2(32)"Status" entry for use on the ID card.
Person_IdNumberIdentifier for the person record for this entry.
Orgn_NameVarchar2(32)Name of the department, if applicable.
Orgn_CodeVarchar2(6) Department Identification code. Used with Coas_Code to identify department.
Coas_CodeVarchar2(1) Department Chart of Accounts identifier.
Type_KeyNumberAffiliation identifier for non departmental entries.
In_Dirvarchar2(1)Flag indicating if person should be in the fac/staff directory.
End_DateDateDate when status expires, if known.

Figure 6: Person_Status description.

Status_IdNumberIdentifier for status type.
RankNumberOrders status values, highest value is returned.
ID_Card_Statusvarchar2(32)"Status" entry for use on the ID card.
Person_IdNumberIdentifier for the person record for this entry.
Status_Categoryvarchar2(8)Rough category for status types.
Source_Table_Namevarchar2(32)The name of the table we will match with.
Key_1varchar2(16) A key to match against, usage depends on category and table name.
Key_2varchar2(8)A second key (there is also a key_3 and key_4).
Nkey_1number A numeric key, usage depends on the status category and source table.
CommentsVarchar2(255)A description of this entry.

Figure 7: People_Status_Types description.

select lib_patron_Type, status_id, id_card_status, person_id,
       substr(Ftvorgn_Title,1,32), pebempl_orgn_home,'9',
       to_number(null) TYPE_KEY, pst.in_dir, e.nbrjobs_end_date
  from employees E, people_status_types pst, fimsmgr.ftvorgn f
 where Status_Category='Emp' and key_1 = NBRJOBS_STATUS
   and PEBEMPL_ECLS_Code like nvl(key_2,PEBEMPL_ECLS_Code)
   and pebempl_orgn_home = f.ftvorgn_orgn_code
Union...

Display 1: Retrieving employees.

Person_Status

The person status support was originally developed to provide a patron feed for our Library circulation system. It was later enhanced to feed our ID card system. It is now being generalised as people status management tool, however, some traces of the original library and ID card system remain in the column definitions. Most of this information is being moved to other tables that join with Person_Status based on the Status_ID column. The Person_Status view (Figure 6) is the key place where all the different inputs to the status process are brought together.

The Person_Status view is coupled tightly to People_Status_Types (Figure 7) to generate a snapshot of everyone's "status". The view and this table is where all of the site specific stuff lives. The view goes and roots around in a number of different database tables to come up with a standard set of status entries. The People_Status_Types table has columns for joining with the view to determine types, and some other columns that are used to feed the Library and some other systems. I have omitted this second set of columns, as they are gradually being phased out in favour of other tables. A subset of the entries in the People_Status_Types is available in the first appendix of the paper.

The Person_Status view is a series of joins with other tables and the People_Status_Types table, that are connected together with a SQL UNION directive. For ease of formatting, I have broken the view up into individual stanzas (Displays). This is where all the magic happens.

Get Employees

This first section looks in the employees table to get our current employees. The important distinction here is really the key 1 looking for "A" (active) employees and key 2 looking for the employee classification. It also dives into the Ftvorgn table to come up with the department name.[Note 7] See Display 1.

Get New Employees

We dive back into the employees table again to look for new employees. The employee classification doesn't appear in the employees table until the next payroll cycle, and we really don't want to wait that long to start issuing ID cards and the like. So Human Resources has a tool to set the HR_Ok and HR_Status fields appropriately, and this stanza will pick those up. In the course of normal employee record processing, these fields will get cleared when the classification information comes through so we don't have people with two employee status values. But even if something goes wrong, and that isn't cleared, since the regular employee status types have a higher rank, that entry will override this entry. See Display 2.


select lib_patron_Type, status_id, id_card_status, person_id,
       nvl(substr(Ftvorgn_Title,1,32),'Unknown'), pebempl_orgn_home, '9',
       to_number(null), pst.in_dir, to_date(null)
  from employees E, people_status_types pst, ftvorgn_coas_9 f
 where Status_Category='NewEmp'
   and hr_ok = 'Y' and hr_status = key_1
   and nvl(pebempl_orgn_home,'XXXXXXX') = f.ftvorgn_orgn_code (+)
Union...

Display 2: Select new employees.

select lib_patron_Type, status_id, id_card_status, person_id,
       Sgbstdn_Majr_Code_1, Null, Null, to_number(null), Null, To_Date(Null)
  from banner_students bs, people_status_types pst
 where Status_Category='BStu'
   and Key_1 = SgbStdn_Campus_Code
   and ( key_2 = Sgbstdn_Coll_Code_1 or key_2 is null )
   and Key_3 = Sgbstdn_Level_Code
   and Sgbstdn_Status_Code = 'AS'
Union...

Display 3: Select Troy students.

select lib_patron_Type, status_id, id_card_status, person_id,
       Sgbstdn_Majr_Code_1, Null, Null, to_number(null), Null, To_Date(Null)
  from banner_students bs, people_status_types pst
 where Status_Category='HStu'
   and Key_1 = Sgbstdn_Level_Code
   and ( Hart_Reg_This_Term = 'Y' or Registered_This_Term = 'Y' )
   and Sgbstdn_Campus_Code = 'H'
   and Key_2 = Sgbstdn_Status_Code
Union...

Display 4: Select Hartford students.

Get Troy Students

Continuing with the view, we go after our local students. We are getting the same number of columns as we did in the first stanza (a requirement of a view), but most of the department related fields we are returning as null. We look for active students Sgbstdtn_Status_Code='AS', and those in the Troy campus (Key 1), and also breaking them down by College (school) and level (Grad/Undergrad). Although this level of granularity may be overkill for most applications, the Library wanted this fine grain distinction. See Display 3.

Get Hartford Students

We have a second campus in Hartford, and they code students somewhat differently. In Troy, the Registrar aggressively recodes students who are not active. But our Hartford campus does a lot of continuing education, where students just take a course or two, so their status is left as `AS'. So to handle this case, we pull the same columns, but look for some other keys in the students table, Registered_This_ Term. We actually have a third campus which is being phased out, so I will skip that stanza of the view. See Display 4.

Mailing List Entries

Our method of handling people who are already in the system, and need some special status, like what we did for the Emeriti faculty, is handled internally by adding them to a special "department". These tools were originally developed for the phone directory, and worked well here as well. In this case, an entry is made in the Directory_Aux_Entries table (which is listed as Dir_Aux_Ent in the appendix), and we pick up the appropriate entries. We use a different table for the department name this time, this stanza was written later and I should revise the employee entries. See Display 5.

ID Guests

This is the place where we get all of our id guests. A number of the id entry types map directly to a status type. You can actually compare the Nkey_1 values in the appendix with the Entry_Type_Id values in the Entry Types listing above. We also make a check to ensure that the guest entry either does not have an expiration date, or that date is some time in the future. If there isn't one on the record, the current time will be used, but that is good enough! See Display 6.


Select lib_patron_type, status_id, id_card_status, person_id,
       substr(nvl(orgn_common_name,orgn_name),1,32), dae.orgn_code,
       dae.coas_code, title_id, dd.def_include, to_date(null)
  from Directory_Aux_Entries DAE, people_status_types pst,
       directory_departments dd
 where Status_Category = 'DirAux'
   and pst.Key_1 = DAE.Orgn_Code and pst.Key_2 = DAE.Coas_Code
   and dae.orgn_code = dd.orgn_code and dae.coas_code = dd.coas_code
 Union..

Display 5: Select mailing list entries.

select lib_patron_Type, status_id, id_card_status, person_id,
       ia.name, ia.orgn_code, ia.coas_code,
       nvl(ip.affiliation_id,ip.sponsor), ip.dir_flag,
       ip.expiration_date
  from id_people ip, people_status_types pst, simon.id_affiliates ia
 where Source_Table_Name = 'Id_People'
   and Nkey_1 = Ip.ENTRY_TYPE_ID
   and nvl(ip.expiration_date,sysdate) >= sysdate
   and Key_1 = 'CURRENT'
   and ip.affiliation_id = ia.affiliation_id (+)
Union...

Display 6: Select ID guests.

select lib_patron_Type,status_id,id_card_status,person_id,
       Slrrasg_Bldg_Code || '-' || Slrrasg_Room_Number,
       Null, Null, to_number(null), Null, Slrrasg_End_Date
  from banner_students bs, people_status_types pst
 where Status_Category='ResLife'
   and Slrrasg_Active = 'Y'
   and ( key_1 is null or key_1 = slrrasg_bldg_code )

Display 7: Select people living on campus.

People Living on Campus

We have a significant number of people who are living on campus after they graduate. One of the side effects of graduating, is losing your student status, which triggers a number of events, including cancellation of your ID card, which locks you out of your building! Although these people were no longer registered students, they did have housing contracts. We added some information about room assignments to our student table, and added this final stanza to the Person_Status view. During the normal school year, this entry will provide a second status value to all on campus students, but since it is of a lower rank, it is ignored. See Display 7.

People_Status

While the Person_Status view will give us a snapshot of the current status of everyone, it is not able to provide any historical information. A common question when checking someone with no status, is what was their status before it ended. There were also concerns about performance of this view - it is looking into a number of other database tables. For both of those reasons, we have a process that runs daily that checks the current status of everyone, with what we have saved in the People_Status table (Figure 8).[Note 8] We can also invoke this processing for a specific person. This function is used for several administrative applications when they want to ensure someone's status record is current as of this instant. Refreshing a single person's status is almost instant, it takes longer to redraw the screen.

The People_Status table has the same columns as the Person_Status view, with the addition of a start and end date, as well as some numeric columns (When_ Inserted and When_Marked_For_Delete) to assist with data propagation. Changes in a person's status are also reported in the Meta Change Queue subsystem, so other systems can easily watch for changes in a person's status.

Making Use of People Status

The original target of the people status support was to feed the circulation system for our Library. It was later expanded to feed the ID card system. It became obvious that extending the People_Status_Types table for every new application was the wrong approach. Instead, we added a set of People_Status_Aux. . . tables that allowed us to define new flags for each status type, and developed a tool to allow administrators of other systems to set their flags and not interfere with other consumers of the status information. We also use a person's status to trigger other processing.


Person_IdNumberPerson identifier.
Status_IdNumberIdentifier for status type.
Start_DateDateDate when this status was initiated.
End_DateDateDate when this status will terminate if known or was terminated.
Orgn_NameVarchar2(32)Name of the department, if applicable.
Orgn_CodeVarchar2(6) Department Identification code. Used with Coas_Code to identify department.
Coas_CodeVarchar2(1)Department Chart of Accounts identifier.
Type_KeyNumber Affiliation identifier for non departmental entries.
In_Dirvarchar2(1) Flag indicating if person should be in the fac/staff directory.
When_InsertedNumberSequence value when record was inserted.
When_Marked_For_DeleteNumber Sequence value when record was considered to be deleted.

Figure 8: People_Status description.

Stream_Namevarchar2(32)The name of the stream.
Access_Rolevarchar2(32)An Oracle role that can manage this stream.

Figure 9: People_Status_Aux_Master description.

Stream_Namevarchar2(32)The name of the stream. Must exist in the Master table.
Field_Namevarchar2(32)The name of the field.
Field_Typevarchar2(32)The data type - to assist the web tool in formatting and controlling.
Field_Lengthvarchar2(32)The maximum length of the field where applicable.
Field_Defaultvarchar2(32)The default value to use.
Field_RankNumberRank order to display fields on the web tool.

Figure 10: People_Status_Aux_Proto description.

Status_IdNumberThe People_Status_Types.Status_Id of the status getting this value.
Stream_Namevarchar2(32)The name of the stream.
Field_Namevarchar2(32)The name of the field.
Field_Valuevarchar2(32)The value for this particular field.

Figure 11: People_Status_Aux_Values description.

People Status Auxiliary

Adding columns to tables, or creating new tables, and then writing interface routines and tools to manage them can get pretty tedious, and takes a lot of time that could be better spent on other tasks. I wanted to be able to "extend" status related function by making table entries in the database, rather than writing new code.

To start, we defined the table People_Status_Aux_ Master (Figure 9) that define new "streams" of status information. Initially I was envisioning selecting people for data feeds (or streams) to other systems, and the name stuck. This defines the name of the stream and lists an Oracle role that will be used for access control by the administrative tool.

The next step was to define a prototype table, People_Status_Aux_Proto (Figure 10), to identify the possible fields, the field types and the default values. This is used by the administrative tool to automatically generate the appropriate columns and switches on the web page.

The final table holds the actual values for each stream, People_Status_Aux_Values (Figure 11) which stores a field value for each status, stream, field triple.

Once we have defined a new stream, assigned it an access role, and defined one or more fields for that stream, we are able to delegate the management of these values to the appropriate interested parties. At present we have the following streams defined and in use; see Figure 12.

Now that we have all of this information in the system, and control delegated to the appropriate offices, we need to get it out again. At the most basic level, we have a PL/SQL package with some routines, one that will give you a list of everyone with a particular Stream/Field/Value triple and another that will return true or false, if a given person has a particular Stream/Field/Value value. We have also set up Generate_File [6, 8] targets to extract list based on stream and field combinations. We have also written several "wrapper" packages to provide the appropriate streams to people and applications who need to connect directly to the database.

Other Uses

Our physical access control system (Card readers, parking gates), also uses status, or more specifically the lack of any status to automatically terminate all access to campus building, roadways and parking. We also use status to control if and how people are included in the different campus directories.


EBS List For important campus announcements, we have email lists like "All Faculty", "All Staff", "All Students" that are maintained based on current status. Our Postmaster keeps these mappings in place.
Hostmaster For our host database, there are requirements as to who can "own" or "administrate" machines on our campus network. Our Hostmaster makes this determination.
ID Access Control We have several buildings that are open to the campus "community" via card readers. This enables our Access control staff to control that definition of community.
ID Card Admin There are two controls here, one to indicate if that person's information is maintained in Banner (if not, the ID desk can make the updates directly), and also if that person is eligible to have a dependent.
Package Tracking Our campus mail room wants an address feed, but not of everything. They pick and choose.
PC Store Our Campus Computer store is limited in who they can sell to (due to contracts with vendors). This helps them identify who is ok, and who is eligible to charge their purchases to a student account.

Figure 12: Current auxiliary streams in use.

Conclusions

The people status project has been evolving over a number of years here. During that time, some of the original data sources have been replaced, and new ones have been added. In some cases the direction of data flow has changed. At one time the faculty/staff directory data drove the status information; that has been reversed, status data now drives the faculty staff directory.

The People Status project at Rensselaer has reached critical mass. It impacts enough systems that matter to people (such as Parking!) that folks are willing to play by our rules. And it does make life simpler - students enter the system via the Registrar, employees get in via Human Resources and everyone else comes in via the ID card office. Once in, the assorted special cases get picked up by the departments who care about those people and things work well and with a minimum of human intervention.

Implementation Lessons

The people status project was not rolled out as a complete package starting at nothing, but rather was built on several smaller projects such as the directory and computer account management. These systems allowed us to identify and refine the data sources and procedures, while assembling the infrastructure. It also provides time and opportunity to develop the working relationships with key players in other departments.

There are two types of people I needed to work with to pull this project together, people who had data I could use, and people who needed data. By starting with some smaller projects (they didn't seem small at the time!) like the phone directory and computer account management, I was able to put together a comprehensive enough "people" database, that I could get people who needed feeds interested. By having tools and techniques readily available to enable them to control their feeds (Generate File, Meta Change Queue, etc.), I was able to get a number of "clients" for "my data". These clients in turn made it more attractive for the owners of the data to work with me, since it would leverage their work.

I was also able to target groups that were outside of the mainstream, and so didn't have quite the level of IT support that they may have wanted. I help them, and they are willing to modify their procedures and processes to better accommodate what I need. I also find it very helpful to visit my "clients", and see what they are doing.[Note 9] I have been able to provide them with a tool that greatly simplifies their operation (often times, it was an existing tool that needed slight modification) and I strengthen a person to person relationship as a result.

One important lesson, is that your source data is often not quite what you need - It may be too early or too late (like with HR) requiring some extra tools to make it useable, or intended for a different purpose and it will need to be adapted and cleaned up (like with the departmental tree). You have to work with your sources to make it work. The solution may be with people and process and not technology.

Systems and Processes Impacted By This Project

A number of systems and business processes (see Figure 13) has been impacted and improved by this project. The most common improvement is the automatic removal (or at least notification) when people leave or their status changes.

A Note on Table Definitions

There are a number of Oracle table definitions included in this paper. In order to save space and assist with formatting, I did not include a number of columns that are in most of the Simon tables. A recurring set of columns that we see in many of the Simon tables are When_Inserted, When_Updated and When_ Marked_For_Delete columns. These are filled with an ever growing sequence, and are used to identify records that have changed since some previous point in time, and to propagate those changes to other tables and systems [4]. Many tables also have a Clerk or Clerk_Id field to indicate who touched that record last, and some also have an Activity_Date column to indicate in a more human form, of when that change took place. I have also removed references to those columns in some of the source code examples included here. The full definitions of both table and source code can be obtained via the Web. See the following section for details.


Library Circulation and Patron database - drives borrowing limits, aids with contacting via address feeds.
Access Control Automatic revocation of access when a person leaves, automatic access for community members.
Computer Accounts Automatic creation and expiration.
Human Resources HR controls when a person will be issued access, email, etc. - ensuring compliance with employment rules.
ID Card Office Single point of contact for guests, identification of departmental contacts, refinement and documentation of policies and procedures.
Directory Status drives inclusion in the online and printed directories.

Figure 13: Systems and business processes impacted by this system.

Futures

We continue to identify new groups and categories of people that need status. Adding these new types has become pretty trivial, with the tools and techniques being quickly adaptable to new situations. One offshoot of this project deals with physical access control. We mapped the people status auxiliary values into a more general demographic mapping module (along with departmental affiliation, course registration, etc.) into consumers of group information such as the access control system, and windows protection groups. Now when a student registers for a particular class, not only do they get access (via their ID card), to the room with the lab equipment, but access (via their computer account) to restricted course files and directories on Windows and AFS file servers. There might even be another paper on this topic for next year.

References and Availability

This is not a comprehensive, stand alone, product that we can package up easily for distribution. Despite efforts over the years to move our business rules out of the code, and into data table and views, there is a lot of site specific stuff in here. That being said, I feel that there is a lot here that can be used by other sites. You will have to do some code development of your own to address your own site's specific issues and requirements. Some of the existing packages we have developed may serve as models for your own work. The web tools we have developed use a pretty much standard Oracle web environment with our own custom front end to handle the authentication.

All of the PL/SQL source code for the Simon system as well as the full table and view descriptions are available via the web at https://www.rpi.edu/campus/rpi/simon/misc/Tables/simon.Index.html and https://www.rpi.edu/campus/rpi/simon/misc/Tables/SIS-index.html. If you ask nicely, I will try to answer questions and might be able to dig out some of the C and JAVA code that makes up other parts of the system.

Acknowledgements

I would like to thank Tom Perrin for his shepherding of this paper with me. I also want to thank Rob Kolstad for his excellent (as usual) job of typesetting this paper.

Author Biography

Jon Finke graduated from Rensselaer in 1983 with a BS-ECSE. After stints doing communications programming for PCs and later general networking development on the mainframe, he then inherited the Simon project, which has been his primary focus for the past 14 years. He is currently a Senior Systems Programmer in the Communication and Collaboration Technology department at Rensselaer, where he continues integrating Simon with the rest of the Institute information systems. In addition to the Simon project, Jon is also involved with the support of the Telecommunications billing system,[Note 10] and providing data and interfaces for Unity Voice Messaging and CISCO VOIP deployment projects at Rensselaer. When not playing with computers, you can often find him merging a pair of adjacent row houses into one, or inventing new methods of double entry accounting as treasurer for Habitat for Humanity of Rensselaer County. Reach him via USMail at RPI; VCC 319; 110 8th St; Troy, NY 12180-3590. Reach him electronically at finkej@rpi.edu. Find out more via https://www.rpi.edu/~finkej.

Bibliography

[1] Anderson, Eric and Dave Patterson, "A retrospective on twelve years of LISA proceedings," 13th Administration Conference (LISA 1999), pp. 95-107, USENIX, November 1999.
[2] Arnold, Bob, "Accountworks: User create account on SQL, Notes, NT and UNIX," The Twelfth Systems Administration Conference (LISA 98) Proceedings, pp. 49-61, USENIX, December, 1998.
[3] Cooper, Michael A., "Spm: System for password management," The 9th Systems Administration Conference (LISA IX) Proceedings, pp. 149-170, USENIX, September, 1995.
[4] Finke, Jon, "Data propagation between oracle tables," Proceedings of Community Workshop '92, Troy, NY, June, 1992.
[5] Finke, Jon, "Institute white pages as a system administration problem," The Tenth Systems Administration Conference (LISA 96) Proceedings, pp. 233-240, USENIX, October, 1996.
[6] Finke, Jon, "An improved approach to generating configuration files from a database," The Fourteenth Systems Administration Conference (LISA 2000), pages 29-38, USENIX, December, 2000.
[7] Finke, Jon, "Embracing and extending Windows 2000," The Sixteenth Systems Administration Conference (LISA 2002), USENIX, November, 2002.
[8] Finke, Jon, "Generating configuration files: The director's cut," The Seventeenth Systems Administration Conference (LISA 2003), pp. 195-204, USENIX, October, 2003.
[9] Finke, Jon, "Meta change queue: Tracking changes to people, places and things," The Eighteenth Large Installation Systems Administration Conference (LISA 2004), pp. 231-239, USENIX, November, 2004.
[10] Harlander, Dr. Magnus, "Central system administration in a heterogeneous unix environment: Genuadmin," USENIX Systems Administration (LISA VIII) Conference Proceedings, pp. 1-8, USENIX, September, 1994.
[11] Hughes, Doug, "User-centric account management with heterogeneous password changing," The Fourteenth Systems Administration Conference (LISA 2000), pp. 67-76, USENIX, December, 2000.
[12] Rosenstein, Mark A., Daniel E. Geer, Jr., and Peter J. Levine, "The Athena service management system," USENIX Conference Proceedings, pages 203-211, USENIX, Winter, 1988.

Appendix A: Selected People_Status_Types

ID CardStatusSourceKeyKeyNkey
RNKStatusCategoryTable121Description
750EmployeeEmpEmployeesAE% Exempt Employees
750EmployeeEmpEmployeesAX%Executives
750EmployeeEmpEmployeesAN%Non Exempt employees
750FacultyEmpEmployeesAF%Faculty
740EmployeeNewEmpEmployeesStaffNewly hired staff
740Hartford EmpHartEmpHartfordRawDirHartford Employees
740FacultyNewEmpEmployeesFacultyNewly hired faculty
730ROTC StaffN/Aid_peopleCURRENT91397008 US Military personal assigned to the ROTC detachments in a support (non teaching) assignment.
730ROTC FacultyN/Aid_peopleCURRENT91397007 US Military personal assigned to a ROTC detachment who will be teaching ROTC and other courses.
730On Leave EmployeeEmpEmployeesF On Leave with full benefits
730Employee (PT)EmpEmployeesP On Leave w/ Partial Pay and Benefits
650Visiting ResearcherN/Aid_peopleCURRENT91399849 A person doing research (but not being paid by RPI)
650Research ProfessorN/Aid_peopleCURRENT91399850 Someone who is teaching, but is not paid by RPI. Will have a memo from the Provost's office or the Dean's office.
640UndergradBStuBanner_studentsTA Undergrad, in Architecture at Troy
640GraduateBStubanner_studentsTSGraduate, in Science at Troy
640GraduateBStubanner_studentsTEGraduate, in Engineering at Troy
640PDE GradDStubanner_studentsGRGraduate Student with PDE
640HartGradHStubanner_studentsGRASGraduate Student at Hartford
640UndergradBStubanner_studentsTEUndergrad, in Engineering at Troy
640UndergradBStubanner_studentsTSUndergrad, in Science at Troy
640GraduateBStubanner_studentsTAGraduate, in Architecture at Troy
560IncubatorIncubatrid_peopleCURRENT91068656 A person affiliated with a company in the incubator center.
550Dependent/SpouseDependntid_peopleCURRENT91067788 Dependent/Spouse of existing RPI person
550Personal ServicesN/Aid_peopleCURRENT91449917 A personal aid, generally for health care of a disabled member of the Rensselaer Community.
550FamilyN/Aid_peopleCURRENT91449912 A spouse of a member of the RPI community
550FamilyN/Aid_peopleCURRENT91449913 A domestic partner of a member of the RPI Community.
550FamilyN/Aid_peopleCURRENT91449911 A dependent, spouse or domestic partner
550Temp EmployeeTempEmpid_peopleCURRENT91318569 A temporary employee, not on payroll - JJ Young, Manpower, etc.
500Emeritus FacultyDirAuxDir_Aux_EntMR61909 School of Engineering Emeritus Faculty
500Emeritus FacultyDirAuxDir_Aux_EntMR62109 Emeritus Faculty from the School of Science
500Emeritus FacultyDirAuxDir_Aux_EntMR62009 Emeritus Faculty in Architecture
300VendorVendorid_peopleCURRENT91067997A vendor
300RetireeRetireeid_peopleCURRENT91080042A retiree
300VendorN/Aid_peopleCURRENT91398702 A person affiliated with an on campus vendor
300VendorN/Aid_peopleCURRENT91402506 A vendor working with a specific department on campus.
300VendorN/Aid_peopleCURRENT91393994 Somone affiliated with an off campus vendor
300Tech ParkTechParkid_peopleCURRENT91080046 An employee at a tech park company
260RU Club MemberN/Aid_peopleCURRENT91405098 A member of a Rensselaer Union sponsored club or organization.
250Special ProgramsSpec Prgid_peopleCURRENT91121096Special Program
200Special AccessSpec Accid_peopleCURRENT91068269Special Access Card
200Conference CardConfCardid_peopleCURRENT91114523 A Generic Conference ID card.
150 Residence Hall Occupant ResLifebanner_students A person with an active housing contract. May not be a student nor employee.
55Retired FacultyDirAuxDir_Aux_EntS61609 A retired faculty member. This status is maintained by the Provost's office.

Footnotes:
Note 1: Although I am starting from a university environment, we are also a major employer, with an active HR department. I am not going to address the ISP world.
Note 2: Such as contractors, vendors, adjunct faculty, visiting scholars, temporary employees, retires, conference guests, research collaborators, emeritus faculty, consultants, Ice Hockey officials, model railroad club members, etc.
Note 3: Stake holders included Parking and Transportation, Public Safety, Human Resources, Physical Plant, Contracts and Grants, the Student Union, the Registrar, the Provost, the Library, Sodexho (food service), Purchasing, and a number of others.
Note 4: SunGard SCT - https://www.sct.comhttps://www.sct.com.
Note 5: My office is located in the "Alan M Voorhees Computing Center," which everyone refers to as the "Voorhees Computing Center" or the "VCC".
Note 6: We will be changing this, and using status values instead.
Note 7: People well versed in Oracle and/or Banner will note that column aliases are missing, and additional conditions are needed to make this work. These have been removed to aid formatting. See the end of the paper for how to view the actual, working code.
Note 8: On a recent run, this process compared 19,000 records in just over a minute, a performance level I am comfortable with.
Note 9: Is this an obvious part of customer service - Yes! Is it often overlooked, sadly, Yes.
Note 10: AXIS - Pinnacle CMS by Paetec.
?Need help?


Last changed: 11 Nov. 2005 jel