wiki:scriptHell
Last modified 3 years ago Last modified on 06/19/17 16:24:38

Creating, Modifying, or Deleting ZUAUSR Processing Scripts

Overview

Several steps are taken to add new script functions.

  • Add Script data to ~/users/zu/zuausr/appworx/SCRIPT.pm
  • Create appropriate SQL script in /ZUAUSR/product/PROD/bin to perform the work
  • Create ZUAUSR Base Class
  • Associate Base Class to SUPER class

Add Script data to ~/users/zu/zuausr/appworx/SCRIPT.pm

The following template is repeated through SCRIPT.pm. To include a new script, add a section with the following format substituting the new script information for:

  • GRANT_BANPROXY : name of the ZUAUSR base class
  • zua_grant_banproxy : name associated to the SQL script under /ZUAUSR/product/PROD/bin
        # process GRANT_BANPROXY "script"
        elsif ( $process_ind eq $::process_ind_add and $class_name eq 'GRANT_BANPROXY' ) {
    
            # verify no pending BANNER processing
            # (procedure says process GRANT_BANPROXY only if BANNER processing completed)
    
            my $continue = &::verify_banner( @data );
            if ( $continue == $::failure ) {
                    $process_status = $::skipped;
                    $status_msg = "BANNER processing pending";
                    return ( $process_status, $status_msg );
            }
    
            $script_command = "BEGIN\n:result := zua_grant_banproxy(\'$userid\');\nEND;";
            $status_msg = &execute_db_procedure ( $script_command, $class_inst );
            if ( $status_msg =~ m/Success/ ) { return ( $::status_ind_completed, $status_msg ) }
            else { return ( $::status_ind_processing_failure, $status_msg ) }
        }
    
    

Create Appropriate SQL Script

The reference in SCRIPT.pm, $script_command = "BEGIN\n:result := zua_grant_banproxy(\'$userid\');\nEND;";, refers to a SQL script located at /ZUAUSR/product/PROD/bin. Actions that effect change to Banner or ORACLE are encoded into scripts at the PROD location. PROD is the only instance of ZUAUSR running, even if other instances contain copies of some relevant files.

SQL scripts that are of type SCRIPT follow a pattern. The scripts follow clear checking to be sure the user is not terminated, that the user has basic pre-requisite roles granted, and that the user does not already have the role set.

Script Comparison for Similar Functionality

The following excerpts show the places in which the scripts differ where the functions are essentially the same. Each of these files are 171 lines; most of the lines in the two scripts are identical.

< rem TITLE:            zua_grant_banproxy.sql
< rem DESCRIPTION:      script to create the zua_grant_banproxy function to 
< rem                     grant connect through BANPROXY to users for Banner9
---
> rem TITLE:            zua_grant_banjsproxy.sql
> rem DESCRIPTION:      script to create the zua_grant_banjsproxy function to 
> rem                      grant connect through BANJSPROXY to users for Banner9

< Program: grant_banproxy
< Purpose:  Grant connect through BANPROXY to users for Banner9 access
---
> Program: grant_banjsproxy
> Purpose:  Grant connect through BANJSPROXY to users for Banner9 access
< create or replace function zua_grant_banproxy ( parm1 varchar2, debug_flag IN boolean := FALSE )
---
> create or replace function zua_grant_banjsproxy ( parm1 varchar2, debug_flag IN boolean := FALSE )
<   execute immediate 'alter user ' || v_uname || ' grant connect through banproxy';
<
<   rc_msg := success_msg||': '||'CONNECT through BANPROXY granted';
---
>   execute immediate 'alter user ' || v_uname || ' grant connect through banjsproxy';
>
>   rc_msg := success_msg||': '||'CONNECT through BANJSPROXY granted';

Script Comparison for Different Functionality

The following exemplifies the fact that the script's function ultimately defines it's contents. Yet, the difference in line number is 171 to 208. The bulk of the SQL code is the same.

< rem TITLE:            zua_grant_banproxy.sql
< rem DESCRIPTION:      script to create the zua_grant_banproxy function to 
< rem                     grant connect through BANPROXY to users for Banner9
---
> rem TITLE:            zua_insert_gurusri.sql
> rem DESCRIPTION:      script to create the zua_insert_gurusri function to 
> rem                     insert a row into BANSECR.GURUSRI for MEP
17,18c17,18
< Program: grant_banproxy
< Purpose:  Grant connect through BANPROXY to users for Banner9 access
---
> Program: insert_gurusri
> Purpose: Insert a row into GURUSRI to give users a default campus for MEP 
22c22
< create or replace function zua_grant_banproxy ( parm1 varchar2, debug_flag IN boolean := FALSE )
---
> create or replace function zua_insert_gurusri ( parm1 varchar2, debug_flag IN boolean := FALSE )
25a26,27
>   v_uinit varchar2(2);
>   v_vpdi  varchar2(6);
45c47,49
<         WHERE USERNAME LIKE user_in;
---
>         WHERE USERNAME LIKE user_in
>         and not exists (select 'y' from bansecr.gurusri
>         where gurusri_vpdi_user_id = username);
137c141,144
<   close sel_username_cursor;
---
>   LOOP
>     BEGIN
>     v_uinit := substr(v_uname,1,2);
>     v_vpdi := 'UAA';
139c146,172
<   execute immediate 'alter user ' || v_uname || ' grant connect through banproxy';
---
>     if v_uinit in ('AF','AH','AN','AY','VN','VF','DN','DF','IN','IF','IH','PN','PF','PY') or
>        v_uname in('VRSX10','VRSX20','VRSX1','VRSX2') or
>        v_uname like('OPS$AXRE%') or 
>        v_uname like('KIKEN%') or
>        v_uname like('KIANC%') then
>           v_vpdi := 'UAA';
>     elsif v_uinit in ('EF','EN','FF','FH','FN','FY','LF','LN','NN','NF',
>                    'RN','RF','ZN','ZF') or
>        v_uname in('VRSX30','VRSX40','VRSX3','VRSX4') or
>        v_uname like('OPS$FXRE%') or
>        v_uname like('KIFBK%') then
>           v_vpdi := 'UAF';
>     elsif v_uinit in ('JY','JN','JF','JH','TF','TN','KN','KF') or
>        v_uname in('VRSX50','VRSX60','VRSX5','VRSX6') or
>        v_uname like('OPS$JXRE%') or
>        v_uname like('KIJUN%') then
>           v_vpdi := 'UAS';
>     else 
>           v_vpdi := 'UAA';
>     end if;
>     insert into bansecr.gurusri (gurusri_vpdi_user_id, gurusri_vpdi_code, gurusri_user_def_inst_ind, 
>                                  gurusri_activity_date,gurusri_user_id,gurusri_data_origin)
>     values(v_uname,v_vpdi,'Y',sysdate,'ZUAUSR',NULL);
>    END;
>    FETCH SEL_USERNAME_CURSOR into v_uname;
>    EXIT WHEN sel_username_cursor%notfound;
>   END LOOP;
141c174,178
<   rc_msg := success_msg||': '||'CONNECT through BANPROXY granted';
---
>   IF sel_username_cursor%isopen
>   THEN close sel_username_cursor;
>   END IF;
>   
>   rc_msg := success_msg||': '||'ID inserted into BANSECR.GURUSRI';