The Roles Database
at the
Massachusetts Institute of Technology

Jim Repa

EDUCAUSE Conference
Long Beach, California
October 29, 1999

Additional information available at

MIT has implemented a system, called the Roles Database, to centrally manage people's authorizations for computer-based applications enterprise-wide. Roles or authorizations are centrally defined in understandable business terms, and then converted to the native representation of each application to which they apply. An authorization is a 3-part entity consisting of a Person, a business Function, and a Qualifier. The hierarchy-based Qualifier defines the scope of the authorization, narrowly, as an individual account number, or broadly, as a department, school, or the entire organization. This system supports an environment where many people are authorized to perform similar tasks, but for different departments or fiscal areas.

The presentation will summarize the design of the Roles Database and describe how it is being used to manage authorizations for MIT's data warehouse, SAP financial system and other applications.


  1. Why a Roles Database?
  2. Main Principles behind the Roles Database
  3. What is an Authorization?
  4. The Qualifier component of an Authorization
  5. Who can create, update, or delete authorizations?
  6. The software - how is it implemented?
  7. Data feeds into and out of the Roles Database
  8. Where are we today?
  9. Plans for the future

Why a Roles Database?

In a large educational and research institute such as MIT, the job of maintaining people's permissions to perform specific tasks on multiple computer systems can be complex and time-consuming. There are rules that permit people to spend or approve purchases on certain account numbers, report on financial or personnel information, decide on a prospective student's admission to a specific school, etc.. The responsibility to decide who should be allowed, for example, to spend on an account number is held by the department or individual within the department who "owns" the account. But the knowledge required to set up the permission in the financial system to allow a person to spend on an account number is held by a system administrator for the financial system. Even if there were an interface for "general users" to enter authorization information in individual systems, it would be different for each system, and general users would not want to learn all of these interfaces.

The departmental administrators with knowledge about their departmental resources and who needs access to them do not have an easy way of looking up or changing these authorizations in the various systems that give access to their departmental resources. Because it is hard to report on authorizations for all of these systems, the authorizations are often not cleaned up when a person leaves the department or changes responsibilities. And there is the potential for mistakes when a departmental officer makes an authorization change request in business terminology and an IT person must translate that into the arcane technobabble of each system.

We wanted to find a better way to maintain people's authorizations for various computer-based systems in order to make this maintenance easier, less time-consuming, and less error-prone.

Main Principles behind the Roles Database

We reasoned that we could improve the process of maintaining enterprise wide authorizations if we had an authorization system based on these four principles:

  1. Maintain information on people's roles or authorizations for computer-based systems in a central database, then disseminate the data to the various systems where these authorizations are enforced.
  2. Define authorizations or roles in understandable business terminology, then have the system automatically convert them to the arcane format required by each application.
  3. Take advantage of the fact that people in different departments perform similar business tasks, but with different sets of financial or departmental resources. Define authorizations as three-part entities:
    Person can doFunction forQualifier
    (who) (what) (where)
    Here, the qualifier represents a department, an account number, or any other object that limits or qualifies the scope of the person's ability to perform the business function. Qualifiers are maintained in hierarchies, so an authorization can be defined to apply to a "leaf" of a tree (e.g., an account number) or a whole "branch" (all account numbers in the School of Science).
  4. Have the departmental administrators who know their department's resources and personnel be the ones to maintain authorizations, not a central person who cannot be familiar with all the people and resources in every department.
The notion of a centralized authorization system is not entirely new, but we could not find any existing systems that would meet our specifications. At MIT, there is a list-maintenance system called Moira that is used for access control of some resources within project Athena at MIT. And, the University of Michigan has developed an authorization system based on LDAP. However, neither of these follow the three-part model we prefer (Person + Function + Qualifier) or easily support the hierarchies that are a natural part of Qualifiers. So, we designed and developed our own system, which we call the Roles Database.

What is an Authorization?

In the Roles Database, the main object that defines a person's authority to perform a business task is an authorization. As mentioned above, an authorization is a 3-part entity, consisting of a person + function + qualifier.

When creating an authorization, each of the three components must be selected from a previously-defined list of possibilities, reducing the likelihood of mistakes. Person, function, and qualifier are the most important fields in an authorization; there are some other minor fields that will also be discussed below.

The term authorization implies that a person is permitted to do something on a given system. In many cases, that is exactly what it represents: the permission for a person to perform a business function in a computer-based system. However, an "authorization" can also represent a role, a responsibility or position in a workflow-based system. A person can have many authorizations applying to the same or different systems. It is also possible that a single authorization for a person could affect more than one system; for example, an authorization to do financial reporting for an account number might apply to both the SAP financial system and the data warehouse.

Let's look at each of the three main components of an authorization.

The person is represented by a username, or more specifically, a Kerberos principal within the domain.

A function is one of the tasks that a user can be authorized to perform on a given system. Functions are grouped by application (known as a "function category" in Roles jargon). Each function has associated with it a specific qualifier type; for example the "Financial reporting" function is associated with a qualifier type of "Account number". When an authorization is created, the qualifier chosen must be of the right type to match the function. Some functions apply to more than one system, such as the financial reporting function that gives a person authority to report on a given account number in both the SAP financial system and the data warehouse.

A qualifier can be an account number, organization number, budget group, etc.. Since qualifiers of each type are organized into a hierarchy, a qualifier can also be a branch of the tree of account numbers, a branch of the tree of organizations, etc. Qualifiers are generally extracted from other systems as part of a nightly feed. Some functions are either "all or nothing" and do not require a qualifier; in these cases a placeholder qualifier of NULL is included in the authorization.

Here are some examples of authorizations:

FredFlynCreate Requisitions F2283900 (Bioengineering PhD Program)
JaneDoeApprove Requisitions SG_BIOLOGY (Spending group for dept. of Biology)
SueSmithFinancial Report PC152000 (Profit Center for dept. of Chemistry)
JonClerkAssign employee ID numbers NULL (no qualifier needed)

There are additional fields that can be set at the time an authorization is created: effective_date, expiration_date, grant, and do_function. Effective_date specifies the date on which the authorization will first go into effect, and expiration_date specifies the date on which the authorization will cease to be effective. The grant and do_function fields are related to the ability to create authorizations and will be explained in a subsequent section.

Two additional fields are automatically stored for each authorization: the date last modified and the person who made the modification. In addition, a complete audit trail of all authorization changes is kept in another table.

The Qualifier Component of an Authorization

We think it is worthwhile to reiterate the reasons why an authorization has a separate function and qualifier. Often, people are authorized to do a function only within an organizational unit or a financial area. People perform similar functions in different departments, but are limited to their own resources or areas. By having a qualifier, the number of functions can be kept small, and the logic that will be needed to process them in the systems supported by the Roles DB can be kept relatively simple. As mentioned above, if a function does not require a qualifier, the function can be defined so that the associated qualifier type is NULL.

One might ask, if qualifiers are good, why not have more than one qualifier per authorization? We've found that one qualifier is a good compromise between simplicity and understandability vs. versatility. In defining the sort of roles that people have in a business sense, one qualifier is generally enough.

A case in point would be financial reporting. There could be lots of options for generating reports about activity on an account. One could imagine using two qualifiers for such authorizations, the first for the account number and the second for a list of possible activities that a person could report on. But we haven't found the need to have many different options here; so far only two options have been specified (with and without salary subtotals), and these have been handled by defining two different functions.

By trying to think in terms of a few simple roles or business functions with a single qualifier, we've so far been able to keep the authorizations relatively simple to explain and maintain. In the translation program that converts the Roles-style authorizations into the native format of the target application, in some cases the simple authorization must be translated into a list of complicated options. But this is hidden from the end user, and we've kept the Roles function definitions simple and understandable for the people using the Roles Database.

As we mentioned earlier, qualifiers of each type are organized into a hierarchy. For example, organizational units are hierarchical:

When an authorization is created to allow someone to do, for example, personnel reporting, the qualifier selected could be for a lab, a department, or a whole school. This simplifies the process of maintaining authorizations. If the qualifier chosen for an authorization is a branch of a tree and the "leaves" change, the person's authorization automatically extends to the new leaves under the tree.

Account numbers are also hierarchically grouped. Their structure is a "web" rather than a strict hierarchy, ie., an account number can be a member of more than one group.

Who can create, update, or delete authorizations?

The ability to create an authorization is restricted by function and qualifier. An administrative officer in a department will be allowed to grant authorizations for people to perform certain business functions for that department's resources. For example, an administrative officer (or in our terminology, a Primary Authorizor) within the department of Biology would be able to grant requisitioning authority for any account number within the department of Biology, but not for account numbers in the department of Chemistry. Note that the Primary Authorizor in Biology can grant such an authorization to any person at MIT who has a Kerberos username, not just to people who officially work within the department of Biology.

When granting an authorization, the officer in Biology can decide whether or not the newly-authorized person can further delegate that authorization to others. In this way, a pyramid of responsibility can be defined. A central administrator of the Roles Database needs to know who the Primary Authorizors are for each department, and sets up these people with the authority to create authorizations for resources within their departments. Each of the Primary Authorizors can decide to delegate the authority to grant certain authorizations for the resources of their department or a subset of these resources. Every authorization change is recorded in an audit trail that can be viewed by department heads, auditors, or others to verify that authorizations are being granted according to departmental or institute guidelines.

In the Roles Database, there are two mechanisms for permitting users to create authorizations. Central Roles Database administrators have a "meta-authorization", an authorization about authorizations, that allows them to create any authorization related to a given system or "function category". For example, the following authorization would allow JoeRoles to create any authorization within the SAP category.

JoeRolesCreate Authorizations Category SAP (SAP financial system)

Notice that the authority to create an authorization implies the authority to delete or modify the same sort of authorization. In this case, JoeRoles would also be able to delete or modify any authorization within the category SAP.

The second and more common mechanism for permitting users to create, delete, or update authorizations involves the grant flag in an individual authorization. Each authorization has a "grant" flag that determines whether the person named in that authorization can grant it to others.

If the grant flag is set to Y, the owner of the authorization can grant a new authorization, or delete or change an existing authorization where:

There is also a do_function flag in each authorization that states whether or not the authorization will be effect in the target application. If the do_function flag is set to N and the grant flag is set to Y, then the person can grant the authorization but not perform the business function.

The creation of authorizations with the grant flag equal to Y is the mechanism that would be used to allow a school or department administrative officer, for example an official in the School of Engineering, to set up authorizations for others granting them access to departmental resources. For example, suppose Smith has the following authorization:

Original authorization:
SmithSpend Funds Fund Ctr 100012 (School of Engineering)Y

This would allow Smith to grant an authorization to "Spend Funds" for all of Fund Ctr 100012, or a subset of it, i.e., any fund or fund center within the School of Engineering. Smith would, for example, be able to grant the following authorizations:

Examples of authorizations Smith could grant:
JonesSpend Funds Fund Ctr 100012 (School of Engineering)Y
BrownSpend Funds Fund Ctr 100056 (Chemical Engineering)N

But Smith would not be permitted to grant the following authorization, because Anthropology is not a subset of the School of Engineering:

Smith can't grant this:
RiceSpend Funds Fund Ctr 100084 (Anthropology)N

The Software - How Is It Implemented?

The Roles Database system consists of the following software components

  1. An Oracle database running on a DEC Alpha platform.
    The database stores authorizations, people, functions, and qualifiers in their respective tables. It has additional tables to keep track of the hierarchical relations between qualifiers, and other miscellaneous information.

  2. A PowerBuilder application, running on client Macintoshes and Windows machines.
    The PowerBuilder application is used to

  3. A set of stored procedures and functions written in PL/SQL.
    The stored procedures are used in conjunction with the PowerBuilder application. Security-related processes, such as the checking of meta-authorizations, are implemented in the stored procedures, not in the PowerBuilder front-end.

  4. A set of Perl scripts (using the DBI module for access to Oracle) running on the DEC Alpha.
    There are scripts that populate the Person and Qualifier tables each night using data from the Data Warehouse. There are also some scripts involved with transforming Roles-style authorizations into formats usable by SAP. However, for most systems, the transformations of authorization formats are handled by the target system. (See the section on data feeds.)

  5. An Apache web server using Perl CGI scripts running on a separate Sun SPARC station.
    The web server was built after the PowerBuilder application, and it provides an easy interface for viewing authorization and other data in the Roles Database. Users access the web interface using Netscape browsers. For web pages that are not "public", authentication is done using x.509 certificates issued from MIT's own certificate authority. Any user at MIT with a Kerberos principal can get an MIT-issued certificate that contains the person's Kerberos principal.

    The web interface can be used to

    In the future, we plan to enhance to web interface to allow people to maintain authorizations as well, so that the PowerBuilder application will not be needed for anyone except "power-users".

Data feeds into and out of the Roles Database

The following diagram shows the main areas where data flows into and out of the Roles Database.

The main paths where data flow into or out of the Roles Database are the following:

  1. Supporting information is fed nightly from data warehouse to Roles DB.
    These data include a list of people at MIT with their Kerberos usernames, and various types of qualifiers, such as financial objects and organization units. The data are used to populate the Person and Qualifier tables.

  2. The PowerBuilder front-end application is used to create authorizations in the Roles Database, and also to maintain the list of pickable functions.

  3. Authorization information is converted, and transported to various applications.

There are two main ways that authorization data are extracted from the Roles Database for use in target systems:

  1. Target application "pulls" authorization data from the Roles Database.
    This is the preferred scenario.

    Authorization data are "pulled" from the Roles Database using an SQL select statement against a view on the Authorization table.

    In this way, a target system can extract a list of authorizations that pertain to it. Where an authorization pertains to a branch of a qualifier tree, the view automatically "expands" it to show each individual leaf of the tree. Then the target system is responsible for enforcing the authorizations - once authorizations are extracted, the Roles Database is out of the picture.

  2. Roles Database "pushes" authorization data to target system

    Authorization data are periodically processed by a Perl script on the Roles machine to convert them to an external format and then sent to the target application.

    This model was used for authorizations for the SAP financial system. We found that we could write the conversion programs more quickly in Perl on the Roles machine than the SAP developers could write them in ABAP/4. There still is an ABAP/4 component, however, which takes flat files of SAP-authorization and SAP-profile changes and applies them to the SAP system. Again, once the authorization information has been pushed to SAP, the Roles Database is out of the picture and has nothing else to do with enforcing the authorizations.

On each of the target systems, users either log on and are authenticated using the Kerberos usernames, or for web-based systems, they are authenticated based on the username contained in their x.509 certificate. Each system checks authority to perform each transaction by comparing the username and desired function and qualifier against its locally stored table of authorizations.

Where are we today?

The Roles Database is used or soon will be used to maintain authorizations for several systems at MIT. So far, only part of the task of the maintaining authorizations has been distributed to the various departments, but the task of defining guidelines and writing documentation is proceeding, as is a phased roll-out of the Roles application to one department at a time. Also at this time, new systems at MIT are adopting the Roles Database for maintaining their authorizations, so the use of the Roles Database is expanding in this dimension also.

Many users across the campus use the web interface for viewing trees of financial objects and related authorizations, and the easy access to the data have made it easier for departments to identify mistakes in their department's authorizations. Even in the cases where the department is not yet using the Roles application to directly input their authorization changes into the system, the simplified reports of authorizations available via the web interface has made it easier for them to send requests for changes to central system administrators. The next phase of our roll-out will allow more departments to make these changes themselves, and we will see more time savings.

The following systems are using or soon will be using the Roles Database for maintaining authorizations at MIT:

Plans for the future

We've already mentioned the ongoing work (a) to support the authorizations for more systems at MIT and (b) to continue distributing the job of maintaining authorizations to local departments. In addition, we're planning the following technical enhancements.

  1. Hierarchicalize functions

    Currently, the third component of an authorization, the qualifier, is part of a hierarchy. An authorization for a node in a tree applies to all the leaves as well.

    It makes sense to do something similar for function component. A good example comes from some common financial tasks. There currently are four separate functions for "requisition approver," "invoice approver," "travel documents approver," and "financial reporting," but in many cases the same person does all four things. So it would make sense to have a parent function, "general financial approver," that would have the other four functions as children in a tree of functions. Then a person authorized as "general financial approver" for the department of biology would automatically have the equivalent of four authorizations, as "requisition approver," "invoice approver," "travel documents approver," and "financial reporting," all for the department of biology.

    When this enhancement is put into place, extracts of authorizations from the Roles Database will not only expand the qualifiers to show all the leaf-level qualifiers for each authorization, but expand the functions as well.

  2. Create a consolidated tree that includes different types of qualifiers

    As a result of the history of stovepipe systems at MIT, there are several versions of a department hierarchy. Personnel organization units, financial profit centers, and financial fund centers all are organized into similar but different hierarchies that represent someone's view of MIT's organization structure.

    We want to be able to define departmental Primary Authorizors, people who are responsible for setting up authorizations for departmental resources. But there is no single integrated department hierarchy that is linked to qualifiers of various types. To set up a Primary Authorizor for the department of Biology, one needs to define meta-authorizations for SG_BIOLOGY (the top node of Biology's spending groups), FC100108 (the top node of Biology's fund centers), 0HPC0000501 (the top node of Biology's profit centers), and 151000 (Biology's organizational unit).

    Once there is general agreement on a single departmental hierarchy at MIT, with a commitment to maintain links to the various objects associated with each department, it will be possible to create a single Primary Authorizor meta-authorization that covers the creation of any authorization related to a department's resources. In addition, it will be easy to display on a single report all authorizations related to a department's resources.

    The work involved in developing and maintaining a single overarching department hierarchy at MIT will involve as much political effort as it will technical effort.

  3. Enhance the web interface

    We plan to enhance the web interface so that people with the proper "meta-authorizations" will be able to create authorizations using their Netscape browser. At a later date, we will decide whether to keep maintaining our existing PowerBuilder application or to roll all of its functionality into a more comprehensive web interface.

Written by on October 1, 1999