Banner MDUU Process
Background Information
With the creation of the MDUU Process, Ellucian used a "single" schema (Owner) of tables that are used by departments/users to handle their MDUU Processes. Many of the users in these departments have expressed the need to be able to have SELECT (read) on these tables.
Since this process is not controlled by UTS, users can name the tables in any fashion or format. As with all UTS / Banner processes, there must be some type of security, naming conventions and standards. The first user of the MDUU Process was Brian Long from Financial Aid. The AR department showed interest in using the MDUU Process, along with Alumni/Advancement.
With their data containing sensitive data, the Data Stewards that have monetary or other sensitive data stored in their MDUU Tables insisted that their be some way to control who sees specific tables. Additionally, since they can create the tables on demand, this process needs to be fixed or dynamic as to automatically apply to new tables. Lastly, Ellucian initially stated or intended all departments to use a single repository for the data files (csv's). After speaking with Ellucian and upgrades, they put in the means to have each area/department to use specific directories. Since UTS already has Data Load Directories in place, their usage was easy.
As previously noted, with all departments/areas tables being all in the same Schema (PRGNREP), without some enforced naming convention, it would quickly become unmanageable and a mess. Therefore, the normal Banner Product Convention will be used. If other departments/areas use MDUU, additional codes will need to be established for them.
Below, I've listed some of the Data Load Directories on the Banjobs Servers. These also have an Oracle Directory already established.
Data Load Directories and Roles
To allow departments/areas to control the users that can Select / Read their MDUU Tables, and allow a dynamic access, set Roles will need to be used. A list of Roles for each Department is listed along side the MDUU DL.
alumni (ALUMNI_DL) ALUMNI_MDUU_EXT_R A_table_name arsys (ARSYS_DL) ARSYS_MDUU_EXT_R T_table_name finaid (FINAID_DL) FINAID_MDUU_EXT_R R_table_name finance (FINANCE_DL)* FINANCE_MDUU_EXT_R F_table_name gradadm (GRADADM_DL)* UGRADADM_MDUU_EXT_R G_table_name payroll (PAYROLL_DL) PAYROLL_MDUU_EXT_R P_table_name registrar (REGISTRAR_DL)* STUDENT_MDUU_EXT_R S_table_name student (STUDENT_DL) STUDENT_MDUU_EXT_R S_table_name ugradadm (UGRADADM_DL)* UGRADADM_MDUU_EXT_R G_table_name uts (UTS_DL) UTS_MDUU_EXT_R U_table_name
This List of Roles will need to be added to one of each of the Departments Access Request Forms. If / When other areas request use of MDUU (non-Data Steward areas), thought will need to be given as to Access Request Forms and Roles. This may never be needed.
CREATE OR REPLACE DIRECTORY xxxxx_DL AS '/banner/TEST/dataload/xxxxx'; GRANT READ ON DIRECTORY xxxxx_DL TO BAN_DEFAULT_M; GRANT READ ON DIRECTORY xxxxx_DL TO BAN_DEFAULT_Q; GRANT READ, WRITE ON DIRECTORY xxxxx_DL TO PUBLIC; GRANT READ, WRITE ON DIRECTORY xxxxx_DL TO SYSTEM WITH GRANT OPTION;
Dynamic Role Creation
The following SQL will need to run as part of a script to dynamically GRANT SELECT on all MDUU Tables. I do not expect a ton of tables, but every time a department/area creates a new table, we do not want them to have to create a ticket and have UTS Staff have to perform GRANTs in TEST and PROD. This Job can be setup in UC4 to run once a day as well as setup in Banner Job Submission to allow a list of Users to submit the job from Banner Job Control.
-- List of MDUU Tables and Roles with Grant Statements --
SELECT OWNER
, TABLE_NAME
, CASE SUBSTR(TABLE_NAME,1, 1)
WHEN 'A' THEN 'USR_ALUMNI_MDUU_EXT_R'
WHEN 'F' THEN 'USR_FINANCE_MDUU_EXT_R'
WHEN 'G' THEN 'USR_UGRADADM_MDUU_EXT_R'
WHEN 'P' THEN 'USR_PAYROLL_MDUU_EXT_R'
WHEN 'R' THEN 'USR_FINAID_MDUU_EXT_R'
WHEN 'S' THEN 'USR_STUDENT_MDUU_EXT_R'
WHEN 'T' THEN 'USR_ARSYS_MDUU_EXT_R'
WHEN 'U' THEN 'USR_UTS_MDUU_EXT_R'
END MDUU_ROLE
, CASE SUBSTR(TABLE_NAME,1, 1)
WHEN 'A' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_ALUMNI_MDUU_EXT_R;'
WHEN 'F' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_FINANCE_MDUU_EXT_R;'
WHEN 'G' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_UGRADADM_MDUU_EXT_R;'
WHEN 'P' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_PAYROLL_MDUU_EXT_R;'
WHEN 'R' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_FINAID_MDUU_EXT_R;'
WHEN 'S' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_STUDENT_MDUU_EXT_R;'
WHEN 'T' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_ARSYS_MDUU_EXT_R;'
WHEN 'U' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_UTS_MDUU_EXT_R;'
END GRANT_STATEMENT
FROM SYS.ALL_TABLES
WHERE OWNER = 'PRGNREP'
AND SUBSTR(TABLE_NAME,2, 1) = '_'
AND SUBSTR(TABLE_NAME,1, 1) IN ('A','F','G','P','R','S','T','U')
;
-- List of MDUU Tables and Roles with Grant Statements --
-- The NOT Exists can be switched to Exists to show Roles --
-- that exist or need to be Granted --
SELECT *
FROM
(
SELECT OWNER
, TABLE_NAME
, CASE SUBSTR(TABLE_NAME,1, 1)
WHEN 'A' THEN 'USR_ALUMNI_MDUU_EXT_R'
WHEN 'F' THEN 'USR_FINANCE_MDUU_EXT_R'
WHEN 'G' THEN 'USR_UGRADADM_MDUU_EXT_R'
WHEN 'P' THEN 'USR_PAYROLL_MDUU_EXT_R'
WHEN 'R' THEN 'USR_FINAID_MDUU_EXT_R'
WHEN 'S' THEN 'USR_STUDENT_MDUU_EXT_R'
WHEN 'T' THEN 'USR_ARSYS_MDUU_EXT_R'
WHEN 'U' THEN 'USR_UTS_MDUU_EXT_R'
END MDUU_ROLE
, CASE SUBSTR(TABLE_NAME,1, 1)
WHEN 'A' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_ALUMNI_MDUU_EXT_R;'
WHEN 'F' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_FINANCE_MDUU_EXT_R;'
WHEN 'G' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_UGRADADM_MDUU_EXT_R;'
WHEN 'P' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_PAYROLL_MDUU_EXT_R;'
WHEN 'R' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_FINAID_MDUU_EXT_R;'
WHEN 'S' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_STUDENT_MDUU_EXT_R;'
WHEN 'T' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_ARSYS_MDUU_EXT_R;'
WHEN 'U' THEN 'GRANT SELECT ON ' || owner || '.' || table_name || ' TO USR_UTS_MDUU_EXT_R;'
END GRANT_STATEMENT
FROM SYS.ALL_TABLES
WHERE OWNER = 'PRGNREP'
AND SUBSTR(TABLE_NAME,2, 1) = '_'
AND SUBSTR(TABLE_NAME,1, 1) IN ('A','F','G','P','R','S','T','U')
) T
WHERE 1=1
AND NOT EXISTS (SELECT R.ROLE
, P.GRANTEE
, P.OWNER
, P.TABLE_NAME
, P.GRANTOR
, P.PRIVILEGE
FROM SYS.DBA_ROLES R
, SYS.DBA_TAB_PRIVS P
WHERE R.ROLE = P.GRANTEE
AND R.ROLE = T.MDUU_ROLE
AND P.OWNER = T.OWNER
AND P.TABLE_NAME = T.TABLE_NAME
)
;
CREATE ROLE USR_FINAID_MDUU_R NOT IDENTIFIED;
SELECT *
FROM SYS.DBA_TAB_PRIVS
WHERE GRANTEE = 'USR_FINAID_MDUU_R'
;
SELECT *
FROM SYS.DBA_ROLES
WHERE ROLE = 'USR_FINAID_MDUU_R'
;
SELECT R.ROLE
, P.GRANTEE
, P.OWNER
, P.TABLE_NAME
, P.GRANTOR
, P.PRIVILEGE
FROM SYS.DBA_ROLES R
, SYS.DBA_TAB_PRIVS P
WHERE R.ROLE = P.GRANTEE
AND R.ROLE = 'USR_FINAID_REPORT_ADM_R'
;
Banner and UC4 Jobs
A Banner Job Submission job (85857) (mzupgsr.shl and mzupgsr.sql) was created to update permissions on new tables as suggested in previous section. A UC4/Automic Banner job (109868) (MZUPGSR) was set-up to run the Banner Job Submission job on a schedule of 8am to 8pm once an hour.
Please refer to https://kb.oakland.edu/uts/MassDataUpdateUtility on how user is to set-up an MDUU job within Banner.
- DBHowTo