Highlight

Multi-LDAP User Management Tool Utilizing Oracle Application Express and PL/SQL

Achievement

The Center for Infrastructure Security Analysis (CISA) project recently required system changes to support multiple Lightweight Directory Access Protocol (LDAP) directories for varied user communities. The LDAP directories were all based upon different technologies, i.e., Microsoft Active Directory (AD), Microsoft Active Directory Lightweight Directory Services (ADLDS), and Oracle Internet Directory (OID), depending on system setup, security requirements, and legacy software. Research by Data System Sciences and Engineering (DSSE) staff, including Adam Bengston, Brad Nance, and Cindy Terry, revealed that PL/SQL provided the capability to execute dynamic PL/SQL and SQL blocks. The dynamic capabilities were utilized along with several configuration tables to provide an object-oriented, polymorphic-like behavior. This architecture was chosen so that a top-level application programming interface (API) could be defined for all calls, no matter what type of LDAP was being manipulated, as each LDAP type has slightly different attributes and requirements. The dynamic nature of the user management system allowed for easy setup and utilization of any new LDAP directories, minimizing the time and effort expended on system changes.

Significance and Impact

The Oracle PL/SQL programming language provides a flexible platform for developing core application logic that CISA researchers were able to utilize to create an application used by the client for more than 10 years. Over the years, CISA has added new LDAP directory configurations to the production environment as client requirements and resources have changed. The dynamic nature of the user management system allowed for easy setup and utilization of any new LDAP directories, minimizing the time and effort expended on system changes.

Research Details

PL/SQL

The CISA client project is completely Oracle-based, so, from the onset, CISA researchers sought a solution that involved utilizing Oracle PL/SQL language for writing code within the database. Research revealed that PL/SQL provided the capability to execute dynamic PL/SQL and SQL blocks. The dynamic capabilities were utilized along with several configuration tables to provide an object-oriented, polymorphic-like behavior. This architecture was chosen so that a top-level API could be defined for all calls, no matter what type of LDAP was being manipulated, as each LDAP type has slightly different attributes and requirements.

Oracle also provides a core package, DBMS_LDAP, to interact with LDAP directories. This package was used at the core of all the PL/SQL that was written for this application.

TABLES

At the root of this package are a set of configuration tables that were created to store information on the connected LDAP directories, as well as information identifying the applications that would be utilizing each LDAP. The LDAP_SERVER_CONFIG table contains metadata for each LDAP the user management tool was required to support. The APPLICATION_REGISTRY table contains a list of applications and the corresponding LDAP that should be used for authentication and authorization.

API

Using native dynamic PL/SQL capabilities and configuration tables, CISA researchers developed a common top-level API with PL/SQL packages to which applications can make calls. The generic application API functions using a specific application ID that is tied to an LDAP via the APPLICATION_REGISTRY table. A package also was developed for the user management application API so that it could function using a specific LDAP ID instead of an application ID. 

OPS_UM_LDAP is the base package utilized for retrieving LDAP metadata, as well as for establishing connections to an LDAP. The OPS_UM_LDAP_GROUP package is used to administer groups within an LDAP based on interaction with a specific application ID that is listed in the APPLICATION_REGISTRY table. The OPS_UM_LDAP_USER package is used to administer users within an LDAP, based upon interaction with a specific application ID that is listed in the APPLICATION_REGISTRY table. The OUMA_FACADE package is utilized by the user management application as its interface to an LDAP directory by specifying an LDAP ID instead of an application ID. 

LDAP–SPECIFIC PACKAGES

Below the top-level API, packages that are specific to each LDAP directory technology supported are provided. CISA researchers utilized the object-oriented, polymorphic-like behavior of dynamic PL/SQL, along with the configuration data stored in the database, to call these specific LDAP implementations. For each LDAP implementation, three packages (i.e., user, group, and session management packages) are provided. Each of these packages must implement the top-level interface defined by the OPS_UM_LDAP_* API packages.

UTILIZING THE API IN THE USER MANAGEMENT APPLICATION

The user management application was developed in Oracle’s Application Express (APEX) platform and was built on the OUMA_FACADE API. Leveraging APEX, CISA researchers were able to tightly integrate the application to the API. The interface provides user management functionalities, such as creating users and groups, modifying existing users, managing group membership, and an administrative area. The administrative area is used to view logs and to manage application and LDAP configuration data. 

The key component to making this a flexible application is an LDAP selection utility that allows the application to manage the selected LDAP simply by applying the selected LDAP parameters to the application’s metadata. With this capability, application users can efficiently maneuver between the LDAPs required by the client system.
The inclusion of the management utilities to the application also allows a user with administrative privileges to add additional LDAP servers to the configuration. If the client requirements change, or if a new technology is utilized, the additional LDAP servers can be added and managed by this single application.