The Problem

CalNet authentication allows an application to obtain from the CalNet LDAP Directory the set of departments (ORG_NODE) and the set of job-titles (JOB_CODE) associated with the employment records a logged-in user (assuming the user is an staff member at UC Berkeley). The source of this information is HRMS, the system-of-record for Human Resources data on the UC Berkeley campus.

In addition to the department-of-record that employs a staff member, a common authorization requirement is to identify a "parent" (or more distant ancestor) level of the campus hierarchy to which that department belongs (the campus organizational hierarchy is defined in tables maintained in this public location). Employee records are associated with a Department or a Discipline/Group (Level 4 or Level 5) in the tree of nodes that define the hierarchy. It is often useful to know which Division or Control Unit (Level 3 or Level 2) the employee-associated node belongs to (e.g., to give permission to a staff-member to view data pertaining to her/his Division, but no other Divisions).

Job titles and departments held by a staff member are found in his/her multi-value LDAP attributes, ucbempdept and ucbemptitlecode (LDAP attributes are described on the Calnet for Developers site). The attributes are not a relational data set, which is not a problem when employees hold exactly one job in one department - but for staff-members who hold multiple jobs on the campus the data in LDAP is less helpful. The following example for Professor Jane Smith who holds two job titles in one department and a third in another department may clarify the circumstances in which this would be an issue for an application that needs job-titles and associated departments in order to meet authorization requirements:

It is not possible to determine from the non-relational LDAP data above which department employs Prof. Smith in each of her three job titles. The set of related pairs of ucbempdept and ucbemptitlecode values cannot be obtained from LDAP.

Does this sort of thing happen in real life? Yes, with some regularity. As of July 1, 2005, about 18.5% of staff on the Berkeley campus held at least two positions with different titles and/or in different departments.

The Solution

Human Resources data is stored in a relational database. It is accessible to authorized campus users (including client applications) through the HR-BAIRS data warehouse, on the basis of business need. The data warehouse is currently instantiated as a set of Oracle views, refreshed nightly from HRMS, the system-of-record.

Users (including client applications) granted access to the data warehouse's Employee Verification view (EMPLOYEE_VERIF_V) can determine an employee's departmental data, and her/his place in the campus organization via this view joined to lookup tables that define the organizational hierarchy.

In the following SQL samples, the parameter (?) is replaced with the employee ID of the authenticated user; this is the value of the LDAP attribute employeeNumber. Additional notes on the SQL follow the examples.

Case 1: An employee's distinct department + title sets

        select distinct EMPL_ID, JOB_CODE, ORG_NODE
          from HRMS.EMPLOYEE_VERIF_V 
          where EMPL_STATUS in ('A','L','P')
          and EMPL_ID=?
      

Case 2: Add code descriptors

        select distinct a.EMPL_ID, a.NAME, a.JOB_CODE, b.JOB_CODE_DESC, a.ORG_NODE, c.DESCR 
          from HRMS.EMPLOYEE_VERIF_V a, HRMS.JOB_CODE_LKUP_V b, HRMS.DEPT_TBL_LKUP_V c
          where a.EMPL_ID=?
            and a.EMPL_STATUS in ('A','L','P')
            and a.JOB_CODE = b.JOB_CODE
            and a.ORG_NODE = c.ORG_NODE
      

Case 3: Add Division to which employee's ORG_NODE belongs

        select distinct a.EMPL_ID, NAME, a.JOB_CODE, b.JOB_CODE_DESC,
                        a.ORG_NODE as USER_ORG_NODE,
                        c.DESCR as USER_ORG_NODE_DESCR, 
                        f.PARENT_NODE as DIVISION_ANCESTOR_NODE
        from HRMS.EMPLOYEE_VERIF_V a, 
             HRMS.JOB_CODE_LKUP_V b, 
             HRMS.DEPT_TBL_LKUP_V c, 
             HRMS.DEPT_TBL_LKUP_V d, 
             HRMS.DEPT_HIERARCHY_MAP_V e, 
             HRMS.DEPT_HIERARCHY_MAP_V f
        where a.EMPL_ID=?
          and a.EMPL_STATUS in ('A','L','P')
          and a.JOB_CODE = b.JOB_CODE
          and a.ORG_NODE = c.ORG_NODE
          and a.ORG_NODE = e.ORG_NODE
          and d.ORG_NODE = f.ORG_NODE
          and (f.LEVEL_NUM - f.DEPTH_FROM_PARENT = 3)
          and e.PARENT_NODE = f.PARENT_NODE  
          and f.LEVEL_NUM in (4,5) and f.ORG_NODE > '99999'
      

Case 4: Add Control Unit to which employee's ORG_NODE belongs

        select distinct a.EMPL_ID, NAME, a.JOB_CODE,b.JOB_CODE_DESC,
                        a.ORG_NODE as USER_ORG_NODE,
                        c.DESCR as USER_ORG_NODE_DESCR, 
                        f.PARENT_NODE as CONTROL_UNIT_ANCESTOR_NODE
        from HRMS.EMPLOYEE_VERIF_V a, 
             HRMS.JOB_CODE_LKUP_V b, 
             HRMS.DEPT_TBL_LKUP_V c, 
             HRMS.DEPT_TBL_LKUP_V d, 
             HRMS.DEPT_HIERARCHY_MAP_V e, 
             HRMS.DEPT_HIERARCHY_MAP_V f
        where a.EMPL_ID=?
          and a.EMPL_STATUS in ('A','L','P')
          and a.JOB_CODE = b.JOB_CODE
          and a.ORG_NODE = c.ORG_NODE
          and a.ORG_NODE = e.ORG_NODE
          and d.ORG_NODE = f.ORG_NODE
          and (f.LEVEL_NUM - f.DEPTH_FROM_PARENT = 2)
          and e.PARENT_NODE = f.PARENT_NODE  
          and f.LEVEL_NUM in (4,5) and f.ORG_NODE > '99999'
      

Notes on SQL:

• EMPL_STATUS: this field describes an employee's employment status. Active (A), Unpaid Leave (L), and Paid Leave (P) records are included in the sample query. Failure to constrain this field results in the inclusion of Terminated (T) and other inactive rows, which are maintained in EMPLOYEE_VERIF_V but are generally not germane to authorization rules. For more on the significance of terminated vs. unterminated employment records, cf. the Caveats section below.
• JOB_CODE_LKUP_V and DEPT_TBL_LKUP_V are lookup tables, included in the example queries in order to obtain code descriptors.
• DEPT_HIERARCHY_MAP_V describes the hierarchy of the UC Berkeley organization (i.e., maps the relationship of a node to its parent).
• Note that EMPLOYEE_VERIF_V is joined to an instance of the view DEPT_HIERARCHY_MAP_V on the ORG_NODE field; and this view is itself joined to another instance of DEPT_HIERARCHY_MAP_V, on PARENT_NODE. This mapping, combined with some "magic" constraints described below, is what identifies the appropriate ancestor node. The "magic" follows:
  • The ORG_NODE associated with each employee record in EMPLOYEE_VERIF_V is of Level 4 or 5 (this is an HRMS rule); note the clause f.LEVEL_NUM in (4,5) in the SQL that returns ancestor nodes.
  • Division is Level 3; Control Unit is Level 2
  • The arithmetic clauses in the SQL that returns ancestor nodes (e.g., for returning the Control Unit node, f.LEVEL_NUM - f.DEPTH_FROM_PARENT = 2; or for returning the Division node, f.LEVEL_NUM - f.DEPTH_FROM_PARENT = 3) assures that the required ancestor node is returned. Some examples might help here:
    • for an employee whose ORG_NODE is at Level 4:
      • The Control Unit ancestor is 2 levels up; therefore
        (ORG_NODE level 4) - (2 levels up) = 2
      • The Division ancestor is 1 level up; therefore
        (ORG_NODE level 4) - (1 levels up) = 3
    • for an employee whose ORG_NODE is at Level 5:
      • The Control Unit ancestor is 3 levels up; therefore
        (ORG_NODE level 5) - (3 levels up) = 2
      • The Division ancestor is 2 levels up; therefore
        (ORG_NODE level 5) - (2 levels up) = 3
  • Level 7, Organization Code, is numeric; all other levels are alphanumeric codes and begin with a letter; note the clause f.ORG_NODE > '99999' in the SQL that returns ancestor nodes.

Discovering relationships between nodes in the campus hierarchy

Another set of queries useful to those implementing authorization rules describes the relationship between ORG_NODEs. Such queries are germane to implementation of rules such as, "the user may view data on all Departments in her/his Division, but not those in other Divisions. There are several ways to approach this set of problems.

In the following SQL samples, the parameter (?) is replaced with the ORG_NODE of interest; in the present context, this is probably one of the departments in which a staff member is employed.

Case 1: Obtain ancestor tree for a specified ORG_NODE

The HR-BAIRS data warehouse includes a pivot-table describing the ORG_NODE hierarchy: the DEPT_HIERARCHY_LKUP_V view. The SQL that follows returns a full row of data describing a node and its ancestors (the meaning of each field in this table is defined in the data-dictionary entry for the queried view):

        select * from HRMS.DEPT_HIERARCHY_LKUP_V where ORG_NODE=?
      

Note an oddity in the view queried here: the Level 7 node data does not necessarily appear in the L7_NODE and L7_NODE_DESC columns; instead, it appears in the first otherwise-empty column. For example, if a Level 7 node is a direct descendant of a Level 4 node (i.e., there is no Level 5 or Level 6 node ancestor), the Level 7 data will appear in the Level 5 columns. Level 7 data is always recognizable, because only nodes in this level are strictly numeric. In the current context, viewing hierarchical information for ORG_NODE values associated with a staff member's employment records, this oddity is not of concern; staff employment records are associated with Level 4 or Level 5 nodes only, according to rules enforced by the HRMS system.

Case 2: Obtain sibling nodes

Sibling nodes to a given ORG_NODE - e.g., for a given node, the children of the given node's direct ancestor - may be obtained using SQL like this:

        select distinct c.ORG_NODE,c.DESCR as NODE_DESCR, e.LEVEL_NUM as NODE_LEVEL,
                        f.PARENT_NODE as ANCESTOR, 
                        f.ORG_NODE as SIBLING_NODE, d.DESCR as SIBLING_NODE_DESCR, f.LEVEL_NUM as SIBLING_NODE_LEVEL
        from HRMS.DEPT_TBL_LKUP_V c, 
             HRMS.DEPT_TBL_LKUP_V d,  
             HRMS.DEPT_HIERARCHY_MAP_V e, 
             HRMS.DEPT_HIERARCHY_MAP_V f
        where c.ORG_NODE=?
          and e.ORG_NODE = c.ORG_NODE
          and d.ORG_NODE = f.ORG_NODE
          and (f.LEVEL_NUM - f.DEPTH_FROM_PARENT) = (f.LEVEL_NUM - 1)
          and e.PARENT_NODE = f.PARENT_NODE  
          and f.LEVEL_NUM =e.LEVEL_NUM
      

The Notes on SQL in the previous section may be helpful to those who wish to understand how the preceding SQL statement works.

The Caveats

A primary concern in using HRMS records for authorization is the existence of unterminated job records for employees who are not actually employed in the job described by the unterminated record. Records that are terminated are filtered out of the query results described above by constraining the EMPL_STATUS (employee status) field. There is no elegant way to discover and filter rows that should have been terminated but were not.

The (business) requirement that UC Berkeley personnel who maintain records in HRMS take positive action to terminate records of employments that end is described here. In practice, however, a significant number of records exist in HRMS that have not been properly terminated. Because the payroll triggers that generate pay (and consequent charges to a department's payroll accounts) can be inactivated by other means than termination of the HRMS record, the motivation for deparmental personnel to clean up such records is in some cases inadequate.

The consequence in the present context is that some employees appear, using the queries described above, to have active employment where in fact they do not.

For example:

• if Professor Smith held a postion as Department Chair several years ago;
• no longer holds that position;
• is still a Professor at Berkeley; and,
• her departmental personnel administrator neglected to terminate the row of HRMS data that describes her now-expired administrative position;
• then the queries described above would validate that Professor Smith holds a position as Department Chair even though she no longer does.

One would hope that, as HRMS records are more frequently used as the basis of authorization for access to data and application priviliges, the backlog of improperly-unterminated records will be corrected. In the meantime, concerns about inappropriate access based on erroneous HRMS data can be identified to functional owners of applications that propose to use HRMS data as the basis of authorization, so that departments responsible for the erroneous data can be encouraged to correct it.