USENIX Technical Program - Paper - Proceedings of the 12th Systems Administration Conference (LISA '98)
Accountworks: Users Create Accounts on SQL, Notes, NT, and UNIX
Accountworks is a system which allows any employee at Sybase, Inc. to use a web form to create accounts for new employees. Every new hire gets a personal account in SQL, Notes, NT, and UNIX administrative domains. Accountworks also creates initial stub entries in our SQL personnel database. It allows the user to make a number of initial choices for their new employee, including access to popular applications and whether to use Notes or UNIX email. Typically all new accounts are available within four hours after the web form is submitted. The system operates 24 by 365 to support our worldwide infrastructure. When the accounts are created, it guarantees a consistent, unique login, UID (for UNIX), Firstname.Lastname record, and password across all domains. It went into full production in July 1997, and has been used to create 1900 new accounts since then. Because this paper is intended to help anyone tackling cross-domain account management problems, it describes the architecture of Accountworks, the process of building it, numerous design decisions, and future directions of the project.
An Apology, By Way Of Introduction
There are a number of itemized lists in this paper, which will, probably, make for dry reading. However, it is hoped that they will also provide a valuable reference. If, at the beginning of the Accountworks project, we had started with a comprehensive set of issues, it would have helped us enormously. As it was, we had to muddle through as we discovered more and more questions that demanded answers. Given the complexity of the problem we tackled, and the limited space to discuss its solution here, the decisions are at least as important as the technical methods of implementing them.
Hopefully, this paper will be helpful to anyone who tackles similar problems. Certainly other sites will have other needs, and would make other choices. However, it seems likely that many organizations could use similar techniques to solve cross-domain account management problems. The descriptions of the Accountworks feature set, and the reasoning behind all these decisions, should at least serve to illuminate the many questions involved.
In The Beginning, There Was Mud
By early 1997, the process of bringing a new person into the company and putting all their necessary working environment in place was widely seen as a major problem. The infrastructure to support this process had not kept pace with the rapid growth of the company. Although some parts of the process worked well, they didn't always work together. In addition to regular employees, the company brings in student interns, contractors and temps; employees of our distributors and other business partners need accounts too. Everything from getting a phone to setting up super-user privileges for a system administrator was taking far too long, sometimes as long as a month. Sometimes the hiring manager didn't begin the process until after their new person was already at work, which caused the predictable frustrations, phone calls, interrupts, emergencies, and escalations.
The Information Technology (IT) department is responsible for supporting most of this process. We have 7000 accounts in each domain, 15000 hosts, 100 locations around the world, and a WAN with links ranging from 28.8 modems to fibre to VPN. Our calltrack system receives 10000 calls per month, many of which are linked to account administration.
In January 1997, a meeting with 40 interested people was held to fix the problems with the new hire system. These stakeholders helped define the overall project goals, and the group rapidly dropped to fifteen participants and a core of ten people.
Our primary project goal was to improve the process of enabling a new employee to become productive as quickly as possible. We took a broad view of this. We knew we would eventually manage the entire account-related life cycle of an employee at the company - we had to look ahead to termination and re-hiring issues. The account creation process had to work for contractors, temps, student interns, distributors and other business partners, as well as full-fledged employees. The charter included looking at, and sometimes re-engineering, other business processes related to hiring.
For example, early on in the project, we briefly considered building a semi-manual account creation process. Hiring one or two entry level staff to do nothing but create accounts would definitely have been cheaper in the short run. Such a solution had obvious disadvantages though, in accuracy, speed, consistency, and data integrity. Furthermore it would still leave the IT organization as a potential bottleneck in the hiring process.
For a number of issues, we simply put documentation on the Accountworks web site. While short of a true one stop shopping solution, at least anybody could go to our web site to begin the hiring process. There, they would find all the necessary instructions, web links, and the Accountworks application itself.
One major change was the role of the Human Resources department in the new hire process. Our HR procedures vary from country to country, and sometimes among business units in the same country. Many of our European and a few North American business units relied on their HR staff to handle or coordinate many aspects of the new hire process, including the initial data entry. Our European IT operations depended on a fully enabled HR record to begin the account creation process. Accountworks required a fundamental business process shift, to make the hiring manager responsible for beginning the new hire process.
The other major process change affected some of the various help desks and systems administration groups around the world. Before Accountworks, half of these organizations were involved in the account creation process, occasionally in some cases and routinely in others. These processes were sometimes clearly defined, and sometimes not. Now it is crystal clear - none of these organizations have to do new hire account creation any more. The burden of the work is squarely placed in the ideal location - the person who cares about it most. And the person who cares, typically the hiring manager, has every opportunity to see to it that the job is done right - all they have to do is enter the correct data on the Accountworks web form.
In many respects the project was fortunate. We started with a number of advantages:
We had a few disadvantages too.
The above consolidation projects, and other unrelated work, competed for staffing resources with the Accountworks project. Most of the core members were stretched thin, some of them chronically.
Years of neglect of each administrative domain had left them in a predictable mess. Clean up efforts simplified the project's work, but competed for the attention of project members. (Some clean up efforts were deliberately put off because they weren't required for the success of the project.)
Scope creep was a constant danger. We kept surfacing related issues which also needed to be solved. For each of these issues, we had to decide whether to ignore it, provide instructions and/or links to relevant web sites, or tackle it. Here are a few examples; many more came up along the way:
Top management originally thought the project would be quick and easy. Significant effort was required to establish a more realistic timeline and staff allocation.
One of our core members was in Europe, a nine-hour time difference from the rest of the team in Emeryville, California. Coordinating our efforts with him was difficult. Two others, including our most important technical person, were in Ottawa; the three hour difference was more manageable. Two of these key participants had to travel to Emeryville for the roll out.
For some of our business units, HR had been responsible for the initial data entry for a new employee. HR staff naturally had concerns about making managers responsible, due to the potential for unclear process ownership and poor data entry. Management was not keen to assume new data entry duties at these locations either. With a lot of work and the backing of top management, we were able to work through these issues. Also, one of our core members from HR traveled to a number of offices around the world to address local concerns before the project rolled out.
What Accountworks does do:
What Accountworks does not do (yet):
We lumped the last three items into the "general group problem," and decided that managing groups was too hard to do within the project deadline.
We built an application with six major components:
Account Creation: A 12-Step Program
When someone wants to bring a new employee into the company, they go to the Accountworks web site. The first screen they see contains information, instructions, and a link to the Accountworks application itself. When they click on the link, the Accountworks data form comes up. Figures 1a and 1b detail the subsequent actions.
The login is used when creating accounts in SQL, NT, and UNIX. It is also stored as a "shortname" field in Notes.
Firstname.Lastname record is used to create the Notes access
key, which consists of Firstname,
Lastname, and an optional Middle_initial. The same data is stored in comment
fields in the NT SAM and the UNIX passwd map. It is also used to create
"login: Firstname.Lastname@notes-gateway" records in the UNIX aliases map
for new hires who will be using Notes as their primary email system.
There were three major problems that required solutions. Guaranteeing unique names for use by all systems was one. To solve this, we created the concept of an 'access key', which is an abstraction of the name which must be unique within a given system, and further must also be unique across all systems. Examples of 'access keys' are the UNIX logins from the NIS passwd map, email aliases from the aliases map, mailing list names in both SMTP and Notes, Notes ACL groups, NT username, and Notes login. We ended up with 34 different systems that needed to be synchronized by this concept of an 'access key.' A significant, beneficial side effect of this process was the identification of the systems and the ability to simply track (but not control) them from a single table.
Every evening, a set of scripts and stored procedures gathers access key data from the various sources, parses it into fields, and loads it into the appropriate tables in the Extraction database. Each data format, such as passwd and aliases file formats, has its own Extraction table. An hour later, we merge all the Extraction records into the "access_key" table in the Accountworks database. Each record in the "access_key" table knows its original data source and when it was first inserted.
When generating login and Firstname.Lastname guesses, Accountworks checks the "access_key" table to see if its guess is available. If so, that ends the guessing game. Otherwise, it moves on to the next guess. This is how we guarantee that any access key we generate is unique.
For example, our Extraction "passwd" table has four data sources. We gather /etc/passwd files from three important and representative UNIX hosts. These files only contain the typical system accounts like "root," "bin," etc. The fourth source is the flat file for our NIS passwd map, which contains 7000 records. It includes personal accounts for most of our employees, some generic accounts, but no "root" account.
Thus, the Extraction "passwd" table has three "root" records. All three "root" records are merged into the Accountworks "access_key" table. The "access_key" table also has a "root" record from the NIS aliases map (to forward mail from "root" to "postmaster"). A simple query against the "access_key" table will show us four data sources which use the "root" access key. If we ever hire a "Jennifer Root" or "Robert Oot," any one of these records is sufficient to keep us from creating a "root" login for them.
The second problem was collecting and modeling the data required to correctly map people to the correct login domains and home servers. It turned out that much of the information required to do this mapping, such as home server names/domain, office locations, and city to country mappings, existed in various databases, spreadsheets, and in many cases just a person's head. Often the information was incomplete or inconsistent, and there was not a known master copy of the data. At one extreme, some offices have no home servers of any sort. At the other extreme, our Emeryville headquarters has perhaps 50 UNIX home servers, and numerous NT and Notes home servers too. Also, our personnel database has records for inactive locations as well as active ones, and we discovered that the locations data had not been well maintained. Once again, a significant side effect of automating the account creation process was the consolidation and cleanup of this required mapping data.
Figure 2: Extraction Database, Administrative tools.
For each active location, we mapped three home servers: Notes, NT, and UNIX. A small office might have only a few PCs, or a few Suns. If a real local home server could not be identified, we picked a home server in a more central office. The WAN topology dictated this choice, so we had to get accurate maps and information about this too. For example, our Dallas office has no UNIX boxes, so UNIX accounts for Dallas new hires were mapped to a Sun server in Chicago, the nearest WAN hub. Ditto for Notes. But the Dallas office does have an NT home server, which we used for the Dallas NT mapping.
Somewhat larger offices might have a few home servers, owned by various organizations. This forced us to create an organization pick list. For example, the technical support staff might be on one server, and everyone else might be on another. We created a "Tech Support" organization, and mapped new hires for that location to their server; all other new hires would go on the other server.
Large offices might have many home servers, and even some departments are split between various servers. "IT Systems Administration" and "IT DBA" are on different UNIX home servers in our Emeryville headquarters. So we had to add a second level to the department tables. Appropriate rollups are done for sub-departments if someone chooses a department which doesn't have a specific home server at that location.
Although locations are hardwired to our personnel database, Accountworks "organizations," surprisingly, are not. Trying to track all the re-organizations and changes in department names and numbers had already doomed an earlier project to failure. Two of our core members had worked on that project, and kept us from making the same mistake. We decided that no matter what the official name of the department was, people could always identify with departments like "Sales" and "Engineering." This has proved a successful strategy.
It was a big help to know that the general direction had recently changed from splitting off new UNIX home servers to consolidating them. Even so, it took a surprising amount of time to come up with a mapping that would work. There were a number of reasons for this. One major factor was all the required research on the WAN topology and which locations were active or coming on line. Another was that it was hard to explain, or even remember, that we only needed to know where new hires would go now, not where everybody had been put in the past, and the new application would not move anybody's old home directory. In a few cases, we had non-UNIX machines providing NFS home services. But mostly, we had a delicate balancing act between adequately modeling the real world, and keeping the organization picklist small. We discovered the problem was complex enough that it was easier to interview key local sysadmins than request data via email. Our development centers, most of which have multiple buildings and a long history of creating a UNIX home server for every new department, were the hardest to model.
The third problem was the design of the request web form. Because someone might use it only once, we tried to make it as easy as possible to fill out. We minimized the amount of required information, and provided defaults, auto-populated fields, radio buttons, and pick lists wherever we could. We have only 16 input fields:
To make the web form easier to maintain, we drive pick list and checkbox creation with tables; these are tagged with plus signs (+) above. The fields marked with asterisks (*) are used for account creation; the others are necessary for personnel, contact, and equipment installation purposes.
What's In A Name?
A tremendous amount of time was spent on design issues surrounding names. Some of these decisions were easy, but others were not. Here are our choices, as they stand today:
We had to address a number of security issues, of course. Other security choices could have been made - there are tradeoffs for all of them. All of these security design decisions were implemented in the initial roll out; only two of them were changed based on our real world experience.
One major security dilemma centers around Notes. Access to a Notes database requires a Notes ID. This consists of a Firstname record, Lastname record, an optional Middle_initial record, a password, and a Notes ID file which contains the name records. Unfortunately, in the real world, people do forget their passwords. For many security systems, the standard fix is to have support staff reset the password, give the forgetful party their new password, and then tell them (or force them) to change it. Unfortunately, this has an ugly side effect in Notes. If the user has used Notes-encryption on any of their files, they can't decrypt those files any more, because resetting the password makes the Notes ID file out of sync with the database. Thus, Notes forces organizations to either a) abandon all encrypted files with forgetful owners, or b) store all Notes passwords so sysadmins can help forgetful owners retrieve their files. Long before Accountworks came along, our Notes administrators were storing the original Notes ID file, but not in the Notes default location. The project chose to continue that practice.
We are aware that complex systems are very hard to secure, and that a system's security is only as strong as the security of its weakest subsystem. Clearly, Accountworks is complex, and has many subsystems. The security implications are obvious.
Trouble In Paradise
There were, of course, a number of problems with the system when it was first launched, in spite of a lot of testing prior to going into production.
Testing is a tricky business. Using an isolated test environment is great for protecting the production systems, but sometimes it's hard or even impossible to recreate a realistic copy of a production system in a test domain. We used various hybrids of test and production environments, which caused various problems.
For the UNIX domain, much of the testing was done against production systems. We got complaints from the user community about "Micky Mouse" and other silly passwd map entries created by the test data.
This wasn't a problem for the Notes domain, because we were unable to totally automate the creation of accounts by rollout, partly because of difficulties with the C language API for Notes. One person still had to press a few buttons to get the accounts created, and they exercised good judgement, so Notes users never saw the "Micky Mouse" accounts. On the other hand, the Notes process was slower than the others because human intervention was required.
For NT, most testing was done against an isolated test domain. But our production system has three NT security domains, and we realized shortly after we went live that we were only able to create accounts successfully in one of them. It took some time to get this fixed, so a number of NT sysadmins found themselves creating these accounts by hand. This didn't win the project team any brownie points.
Shortly after the rollout, it was realized that someone could create an account, get its password, use the first new account to create a second new account, and so on. Even worse, this method would allow someone who was leaving the company to create permanent dial-in access for themselves. So, we restricted Accountworks to accounts with fully activated HR records, and implemented a time hold before the password is released.
We initially designed the system to remove the password as soon as it had been viewed by the requestor, hiring manager, or the optional third contact. This caused more headaches than it was worth after rollout - too many users didn't actually remember the password they had seen, which meant phone calls to get the password reset, by hand, for each domain. We now have an automated routine which deletes the password after a period of time.
We had decided that the table of UNIX home servers would be validated against a comment field in our NIS hosts map, which had historically been maintained by our sysadmin staff. This turned out to be a bad idea, because responsibility for maintaining the validation data was too diffused. Each UNIX sysadmin is responsible for certain home servers, but because they didn't set one up very often, they sometimes didn't put the correct home server information in the NIS database. In such cases, the UNIX account creation script would refuse to create the account. We decided to turn off this validation, and focus the responsibility for maintaining the table of UNIX home servers on the Accountworks administrators.
We could have saved ourselves a lot of trouble if we had rolled out the initial version with a confirmation screen. Our internal marketing efforts focused on the automated account creation benefits, not on the need for accurate data. Under the circumstances, some people entered test data just to see how well it worked. Other people entered real new hires, but they weren't particularly careful since it was just for system accounts, and they didn't know how much work it would be to fix the problems by hand. We added a confirmation screen to remind the user that they were creating real HR records, and to check their work before submitting the request. This helped a lot, but typos and incorrect data are still an occasional problem.
The various problems we had with the system at rollout had a domino effect. Some requestors would check the status, see that there were problems, and enter their new hire again. Even in the best case, we had to decide which records to delete from all systems. Other times, a sysadmin would fix a problem in one domain, without coordinating with other sysadmins or the Accountworks team. Also, the second request would often fail because the application could not create a unique Firstname.Lastname record. It doesn't matter how unusual or uncommon someone's name is - once their name is entered into the system, it's taken.
Since Accountworks creates initial stub records in our HR database, this has relieved HR from some data entry work. But it has also created problems. HR staff has to delete records for prospective employees who never actually end up working at the company; this happens more often than it used to. HR staff has to correct bad data, such as typos in names, and delete records entered by people "just trying the system." This problem was particularly bad before we added the confirmation screen. Finally, HR staff have to delete test records entered by Accountworks application development and maintenance staff. Through all of this, our HR staff have been unusually patient and understanding.
Although the core technologies are SQL and web-based, many tools were used, particularly in the account creation and extraction scripts. Some of these are publicly available, including Perl , Sybperl , CVS , and the Systems Administration Environment . Others are commercial: PowerBuilder, Web.PB, Transact-SQL, Adaptive Server Enterprise, Replication Server, and Open Server (Sybase, Inc.), FINAL (FastLane Technologies Inc.), Notes and NotesPump (Lotus Development Corp.), Netscape Enterprise Server (Netscape Communications Corp.). A third group comes with other products: Bourne shell and friends (with UNIX), and isql (with Adaptive Server Enterprise). The diversity of the domains required a very diverse toolset.
The staff required to support Accountworks is small. Occasional operational problems can often be solved by junior support staff. Maintenance of organization, home server, and application tables requires minimal effort by trained staff. However, improvements and occasional problem debugging still require a diverse set of high skill levels. As of this writing, we have half a dozen more or less irritating bugs. None of them are critical, but most of them require a high skill level to fix.
When architecting the Accountworks application, our primary concern was data integrity. We knew all too well how messy our account domains were. If there was a way to foul up our namespaces, we had done it. We had been through numerous "final cleanups" before, but these heroic efforts were largely wasted without an automated system to keep the account domains in sync.
Therefore, we actively resisted statements like "We'll never hire a Robert Oot" or "That problem will never happen." Murphy's Law had struck far too often. The Accountworks database is highly normalized, with many integrity constraints. Wherever possible, we have tightly coupled our personnel database with Accountworks, using direct replication of tables. Entity relationships were rigorously defined with a conceptual modeling tool, which was then used to autogenerate the physical database structure. The web form is designed to minimize the possibility of bad data entry. Although we initially had a number of troubles around the edges of the application, the core database structure is clean and rock solid.
The SQL strategy has been a major win, because it enabled us to do this. In combination with several other projects, SQL is becoming the glue that ties our various management systems together.
Although Accountworks does not provide an automated system to keep accounts in sync, it is still a major step forward. New hires had been the major source of inconsistent account data. (The other three sources are rehire accounts, generic/system/test accounts, and human error.)
One concept which has been difficult to communicate to our user community, and even to our immediate coworkers, is that we still have no authoritative place to go to find out what someone's login or Firstname.Lastname record should be across all domains. Even the project architects didn't realize this problem until shortly before rollout, and we are a long way from having it completely fixed.
New account data is guaranteed to be consistent and unique only at the time it is created. The primary domains are still separately administered. Accountworks does not manage any of them. Thus, Accountworks is merely a multi-domain account creation tool, a glorious "adduser," if you will. Nothing prevents authorized personnel from changing someone's SQL login, or the Firstname.Lastname record we keep in the NT SAM, or giving them a second personal UNIX account, or several entries in the aliases map. When someone changes their name, when they marry/divorce for example, every system has to be changed accordingly, by hand. One consideration is that access to old encrypted Notes documents is impossible for someone who gets a different Notes ID cut for them with their new Firstname.Lastname record.
The Extraction database is downstream from the personnel, SQL, Notes, NT, and UNIX account management systems (see Figure 2). Because of this, it can determine which domains are using which access keys, but it can't manage the account domains in any way. Except for the personnel data, it can't even tell, programmatically, which human beings (if any) are attached to which records. It can't tell if someone has an account or what its access key is. The lack of an automatically enforced authoritative account data system has proven to be a major headache.
Our ultimate goal is what we are now calling "Datamart." This project will define a set of authoritative data sources. To enforce that authority, we will automatically copy data from the authoritative sources to all downstream systems, including SQL, Notes, NT, and UNIX. When the Datamart project is complete, Accountworks will still be a front end to the various authoritative data sources.
Oh, Happy Day!
Everyone is quite happy with the progress to date, in spite of the initial rollout problems and remaining work. Our user community seems to have forgotten how far we have come - Accountworks is just part of the common toolset now. Sysadmins and help desk staff still have rehire, termination, and generic account issues to deal with, but these are much less disruptive and time consuming than our old new hire crises used to be. Naturally, many people can see ways to improve the system, but overall it functions smoothly and in many cases problems are fixed before the user even notices.
Finally, we have learned a lot. We have surfaced hidden problems, identified poorly designed systems, and examined dirty data sources. We are tackling them with various strategies. Although we still have a long way to go, we know where we are going and have a pretty good idea of how to get there.
Other Account Management Systems
Because of the need to integrate the administration of the four primary administrative domains (SQL, Notes, NT, and UNIX) with our personnel system, on a global basis, we were sure that no commercial product or public domain tools would meet our needs. An in-depth examination of one commercial product, and technical meetings with other a few other vendors did not turn up anything we could use.
Account management solutions have been frequently published in the Large Installation and Systems Administration (LISA) conference proceedings. Eighteen papers were published on this topic in the first four years, and twenty-three total so far. Their requirements and methods, not surprisingly, were mirrored in many ways by our later work. A few quotes will illustrate what we have in common. The very first of these papers says: "The solution at Athena was to create a central database of user information. The database is implemented in RTI Ingres and contains data on our users, courses and projects, clusters, the local systems, such as password files and mail aliases, are propagated from the master system several times a day. [...] For security reasons, the database resides on a restricted machine and can only be accessed directly by privileged users. Users and administrators access and modify the data through various utility programs." 
A centralized, secure, master SQL database, modelling our user community's needs, and accessible via external utilities - this summarizes some of our basic ideas nicely.
From the second LISA conference: "We have (1) established a centralized Network Information Registry, (2) established ... policies ... and (3) designed a relational database to integrate the various administrative databases (including several Yellow Pages maps) and to reduce duplication of information. ... [W]hen a new account is created, the loginname and uid are checked for uniqueness in the NIR as well as in the YP passwd map and /etc/passwd file entries." 
The requirement for unique logins and uids, compared with multiple sources of this data, was critical to our own success. Again, we are following in other footsteps.
Two years later, the LISA proceedings contained this
quote, which we could have taken almost word for word:
"The system selected had to meet several criteria, including:
Finally, the AGUS system , had we been aware of it, might have formed a foundation for some of our work. Here is the key quote: "We wanted to use the same system to create accounts on UNIX, VMS, and Novell based networks. The system should also be designed in such a way that it is simple to add additional system types to the configuration. For example, if the University decides to support user accounts on HP MPE systems, it should be relatively easy to extend AGUS to handle account creation under MPE." 
Here we have an extensible architecture which supports multiple non-UNIX operating systems. AGUS also embodies many of the design elements of earlier systems. For better or worse, it simply never crossed our minds that anything might already exist which came close to meeting our requirements, or which could be tailored to meet our needs with less work than building something from the ground up.
And, in the end, that is still true. The major differences between AGUS and Accountworks are:
For Accountworks, AGUS might have been able to help with the tools to build the UNIX accounts, although that was one of the easiest parts of the project. However, we still would have had to build the user interface; the database of logins, UNIX UIDs, and Firstname.Lastname records to guarantee uniqueness; and the intelligence necessary to configure accounts properly for each location and department.
Accountworks is not freely available. The company is interested in deriving value from this project. Please feel free to contact the author at <email@example.com> for the current status of this effort or any related questions.
Roll Those Credits
Thanks to Paul Riddle, Paul Danckaert, Jack Seuss and Rob Banz for their email and conversations about AGUS. They provided useful information on the current status of the AGUS system.
Because of the complexity of the business processes and computer systems we were changing, many skill sets were required. Sixty or more people were involved in the implementation of Accountworks in one way or another. This core group was deeply involved with the design decisions and implementation:
The first four references have been cited in the paper. A few others
of interest are also listed; references  and  are interesting because
they see account management as part of a larger problem set.
This paper was originally published in the
Proceedings of the 12th Systems Administration Conference (LISA '98), December 6-11, 1998, Boston, Massachusetts, USA
Last changed: 3 April 2002 ml