Overview

The process of adding and removing role members from departments in the Rice UI can be a time consuming process. To support the Time And Reporting System (TRS) Rice client application, there are often times when many role members must be added or removed from departments. Since it is not practical to make such mass updates via the Rice UI, these updates are currently managed though the Kuali Rice Data Set Migration Process. While this process does accomplish the task, it results in the proliferation of JIRA tickets and an increase in the workload for the DBA group who must execute the scripts. The scripts which are currently used to manage the department additions and removals consist of two procedures, one to add a role member to a department (add_dta_role_member), and one to remove a role member from a department (remove_dta_role_member). The script creates the procedures, adds or removes the role members, and then drops the procedures. This process is repeated with each Jira ticket which is created for the purpose of adding or removing role members. The procedures used each time are identical from one Jira ticket to the next, with the only different being the role members which are to be added or removed.

Because the procedures themselves do not change, it was determined that a permanently installed Oracle package which contained the needed procedures would be the most efficient way accomplish the task. Execution rights to the package is given the KFS_<SCHEMA> account to manage the roles. The KFS administrator would then use KFS_<SCHEMA> account to execute an Oracle PL/SQL script which utilizes the package. In this way the KFS administrator would be in control of when the user role updates were made, rather than creating Jira tickets and scripts for the DBA group to retrieve and execute.

Oracle KFS_KIM_ROLE_MAINT Package

The kfs_kim_role_maint was created to manage the addition and removal of user roles from a department. The package definition along with the procedure definitions are shown below:

CREATE OR REPLACE PACKAGE kfs_kim_role_maint AS  -- spec
PROCEDURE add_dta_role_member( PrincipalName IN VARCHAR2, HomeDepartmentCode IN VARCHAR2 );
PROCEDURE remove_dta_role_member( PrincipalName IN VARCHAR2, HomeDepartmentCode IN VARCHAR2, EndDate IN DATE DEFAULT SYSDATE );
END kfs_kim_role_maint;

• The PL/SQL code for the kfs_kim_role_maint package is under version control in SVN: https://svn.ucdavis.edu/svn/kuali/RiceDBProcedures/trunk/KIM_Role_Maintenance_Package/kim_role_maint_package.sql
• The responsibility for maintaining the package lies with the Rice team. Any additions or changes to the package should be made in the form of a Jira request assigned to the Rice team.
• The entire package definition is available here for reference.

Example Utilization

An example script making use of the kfs_kim_role_maint package is shown below:

---------------Opening Section-------------------
COL DT NOPRINT NEW_VALUE db_date;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD-HH24MISS') DT FROM DUAL;
COL db NOPRINT NEW_VALUE db_name;
SELECT SYS_CONTEXT ('RICE', 'MOTHRA') DB FROM DUAL;
----- Name the spool file -----
SPOOL ./KFS_ROLE_MAINT--&db_date-&db_name.log;
SET ECHO ON;
SET TERMOUT ON;
SET VERIFY OFF;
SET TRIMSPOOL ON;
SET SERVEROUTPUT ON SIZE 1000000;
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
WHENEVER OSERROR EXIT SQL.SQLCODE ROLLBACK;

SET ECHO ON;

SHOW USER;
BEGIN
    kfs_kim_role_maint.remove_dta_role_member( 'rvmartin', '061300' );
    kfs_kim_role_maint.remove_dta_role_member( 'soconner', '061095' );
    kfs_kim_role_maint.add_dta_role_member( 'licruz', '062007' );
    kfs_kim_role_maint.add_dta_role_member( 'licruz', '062015' );

END;
/
SPOOL OFF;