SunGard Higher
Education Solutions
Banner General
Technical Training
2
www.sungardhe.com
Introductions
Instructor
Participants
Name
Organization
Title/function
Banner experience
Relational database experience
Expectations
3
www.sungardhe.com
Course Objectives
Performance Objective
Learn about the forms,
tables, and processes necessary to perform daily technical support tasks
in the Banner General System
4
www.sungardhe.com
Course Objectives
Task Objectives
Understand Banner ID relationships
Follow key General System
processes
Work with General Person
tables
Identify tables and fields
for data conversion
5
www.sungardhe.com
Course Objectives
Task Objectives (cont.)
Identify tables and fields
for migration to the production database
Identify and read reports,
processes, procedures, and scripts in Banner General
Use Job Submission and
sleep/wake
Secure your Banner environment
6
www.sungardhe.com
Overview
Banner basics
General System features
Job Submission
Migration and conversion
strategies
Security
Resources
Banner General Technical
Training
General System Overview
8
www.sungardhe.com
The Banner Systems
Student
Finance
Advancement
Financial
Aid
Payroll
General
Position
Control
Accounts
Receivable
9
www.sungardhe.com
Banner General System
Job
Sub
Population
Selection
Letter
Generation
General
Intl Mgt
Sys Funct/
Admin
Event
Mgmt
Web
Mgmt
Banner
General
VR
Mgmt
Business
Rule Builder
10
www.sungardhe.com
Banner General System (cont.)
Job Submission
Facilitates the background
processing of reports and processes
Population Selection
Provides processes with
the ability to identify groups for reporting purposes or for facilitating
Letter Generation
Letter Generation
Constructs letters or merge
files for mailing to selected populations
11
www.sungardhe.com
Banner General System (cont.)
General International Management
Tracks international information
for United States Visa and SEVIS support
System functions/administration
Manages the functions common
to all Banner Systems
Event Management
Manages the scheduling of
events, such as alumni fundraising activities
12
www.sungardhe.com
Banner General System (cont.)
General Web Management
Enables the customization
of Banner web pages for any Banner Self Service product installed
Allows the generation of dynamic
menus which are accessible by web users
Establishes security for Banner
web pages for any Banner Self-Service product installed
13
www.sungardhe.com
Banner General System (cont.)
General VR Management
Supports touch-tone telephone
data entry and inquiry for student information
Business Rule Builder
Create Business Rules to manage
your Banner data
Primarily for SEVIS reporting
14
www.sungardhe.com
Naming conventions
All Banner objects adhere
to naming conventions
Objects include forms, tables,
processes, etc.
For more information, refer
to the Banner Getting Started Guide
15
www.sungardhe.com
Form, process, and
table naming
The names of all Banner forms (except
menu forms), reports, processes, and tables are seven characters, with
each character representing a position location:
Example
G L R V R B L
- - - - - - -
Position Locations 1 2 3 4 5 6 7
16
www.sungardhe.com
Position Location
1 Assignments
Advancement
Property
Tax
Courts
Cash Drawer
Finance
General
Information Access
Work Management
Occupational Tax/
License
Position Control
Customer Contact
HR/Payroll/Personnel
Electronic Work Queue
Financial Aid
Student
Accounts Receivable
Utilities
Voice
Response
Records
Indexing
A
B
C
D
F
G
I
K
L
N
O
P
Q
R
S
T
U
V
X
17
www.sungardhe.com
Position Location
1
Position 1 identifies
the primary System that owns the form, report, job, or table
Letters W, Y, and Z
are reserved for client applications that co-exist with Banner
18
www.sungardhe.com
General Position Location
2
Identifies the module that
owns the form, process, or table
If Position 1 is G,
the character in Position 2 is:
Event
Management
Job Submission
Letter Generation
Overall
Purge
Security
Validation form/table
Utility
Cross product
E
J
L
O
P
S
T
U
X
19
www.sungardhe.com
General Position Location
3
Identifies the type of
form, report, process,
or table
Application form
Base table or Batch COBOL process
Inquiry form
Online COBOL process
Query form
Rule table/form, repeating table, or report/process
General maintenance temporary table
Validation form/table or view
A
B
I
O
Q
R
T
V
20
www.sungardhe.com
Positions 4, 5, 6,
7
Identify a unique four-character
name for the form, report, process, or table
21
www.sungardhe.com
GUAIDEN
G
General
U
Utility
A
Application
IDEN Identification
22
www.sungardhe.com
GJRRPTS
G
General
J
Job Submission
R
Report
RPTS Report and Parameter Information
23
www.sungardhe.com
GTVZIPC
G
General
T
Table
V
Validation
ZIPC
ZIP/Postal Code
24
www.sungardhe.com
SPAIDEN
SStudent/Shared
PPerson
AApplication
IDENIdentification
25
www.sungardhe.com
Column naming conventions
Columns always start with
the table name
Columns having the keyword
“CODE” in position 14-17, reference a validation table
Columns ending in “_IND”
are indicators
Columns labeled 'ACTIVITY_DATE'
are date stamps indicating the last update
26
www.sungardhe.com
More Naming Conventions
GB_SPRADDR_VBS
SB_ADMISSIONS_PII
AS_ADDRESS
F_GENERATE_PIDM
GB_COMMON
UK1_GUBOUTP_JOB
More naming conventions
will be explained during training.
Banner General Technical
Training
Banner Documentation
28
www.sungardhe.com
Banner Documentation
Banner Bookshelf
Repository for all Banner
documentation
Add index files for greater
search capabilities
Banner Getting Started Guide
Navigation
Features
Tips and Tricks
29
www.sungardhe.com
Banner Documentation (cont.)
User Guides
Handbooks
Object: Access Reporting
Guides
Release Guides
Technical Reference Manuals
Upgrade Manuals
30
www.sungardhe.com
Additional Technical
Documentation
Middle Tier Implementation Guide
Configuration information
for setting up Banner
Banner Security Technical Reference
Manual
Configuration information
for setting up Banner security
Not available within the
Banner Bookshelf
Obtain documentation from
the Customer Support Center web site
31
www.sungardhe.com
Banner Documentation (cont.)
Online Help
Contains form and field descriptions
for online navigation
Unavailable if Online Help
is disabled
Not designed to be customized
Banner General Technical
Training
System Functions/Administration
33
www.sungardhe.com
System Functions/Administration
Banking and Currency Menu
Electronic Data Interchange
Menu
Desktop Tools Menu
Electronic Documents Menu
Event Queue Maintenance
Menu
QuickFlow Process Menu
Value Based Security Menu
34
www.sungardhe.com
System Functions/Administration (cont.)
Menu/Preference Maintenance
GUAUPRF – General User Preference
Maintenance
GUAPSWD – Oracle Password
Change
GUAGMNU – Menu Maintenance
– GUAGMNU
GUAPMNU – My Banner Maintenance
– GUAPMNU
35
www.sungardhe.com
System Functions/Administration (cont.)
Menu/Preference Maintenance (cont.)
GUTPMNU – My Banner Maintenance
and Copy
GUAINIT – System Initialization
GUAGMNU – General Menu
xOQMENU – System Menus
Object Maintenance
GUAOBJS – Object Maintenance
36
www.sungardhe.com
System Functions/Administration (cont.)
Miscellaneous General Forms
GUAINST – Installation
Control
GUAMESG – General Message
GUASYST – System Identification
37
www.sungardhe.com
GUASYST – Banner
Relationship & Tables
General PersonSATURN.SPRIDEN
Recruit/ProspectSATURN.SRBRECR
Admissions ApplicantSATURN.SARADAP
Student/LearnerSATURN.SGBSTDN
Faculty/InstructorSATURN.SIBINST
Alumni ConstituentALUMNI.APBCONS
Alumni OrganizationALUMNI.AOBORGN
EmployeePAYROLL.PEBEMPL
Finance VendorsFIMSMGR.FTVVEND
Banner General Technical
Training
General Person
39
www.sungardhe.com
General Person - Overview
Capture biographic/demographic
information for all persons/non-persons associated with the institution
SPAIDEN/*PAIDEN forms
SPRIDEN
SPRADDR
SPRTELE
SPBPERS
SPRHOLD
40
www.sungardhe.com
SPRIDEN description
SQL> describe spriden
Name
Null? Type
-------------------------- --------
----
SPRIDEN_PIDM
NOT NULL NUMBER(8)
SPRIDEN_ID
NOT NULL VARCHAR2(9)
SPRIDEN_LAST_NAME
NOT NULL VARCHAR2(60)
SPRIDEN_FIRST_NAME
VARCHAR2(15)
SPRIDEN_MI
VARCHAR2(15)
SPRIDEN_CHANGE_IND
VARCHAR2(1)
SPRIDEN_ENTITY_IND
VARCHAR2(1)
SPRIDEN_ACTIVITY_DATE
NOT NULL DATE
41
www.sungardhe.com
SPRIDEN description (cont.)
Name
Null? Type
-------------------------- --------
----
SPRIDEN_USER
VARCHAR2(30)
SPRIDEN_ORIGIN
VARCHAR2(30)
SPRIDEN_SEARCH_LAST_NAME
VARCHAR2(60)
SPRIDEN_SEARCH_FIRST_NAME
VARCHAR2(15)
SPRIDEN_SEARCH_MI
VARCHAR2(15)
SPRIDEN_SOUNDEX_LAST_NAME
CHAR(4)
SPRIDEN_SOUNDEX_FIRST_NAM
CHAR(4)
SPRIDEN_NTYP_CODE
VARCHAR2(4)
SPRIDEN_CREATE_USER
VARCHAR2(30)
SPRIDEN_CREATE_DATE
DATE
SPRIDEN_CREATE_FDMN_CODE
VARCHAR2(30)
42
www.sungardhe.com
Personal Identification
Master - PIDM
SPRIDEN and related person
tables are joined by PIDM
Unique number for each
person/non-person
An Oracle sequence is used
to generate one-up numbers for PIDM creation
gb_common API
f_generate_pidm
pidm_sequence
43
www.sungardhe.com
Banner ID Numbers
Unique value to identify
entities in the Banner system
Types:
Manual ID
Generated ID
Previous ID
44
www.sungardhe.com
Generated Banner ID
Numbers
System-generated Banner
ID for each entity
Prefix pulled from SOBSEQN
gb_common API
f_generate_id
id_sequence
A00000001 = Generated Banner
ID
45
www.sungardhe.com
SOBSEQN description
SQL> describe sobseqn
Name
Null? Type
-------------------------- --------
----
SOBSEQN_FUNCTION
NOT NULL VARCHAR2(30)
SOBSEQN_SEQNO_PREFIX
VARCHAR2(1)
SOBSEQN_MAXSEQNO
NOT NULL NUMBER(8)
SOBSEQN_ACTIVITY_DATE
NOT NULL DATE
46
www.sungardhe.com
SOBSEQN select
1 select sobseqn_function
function,
2 sobseqn_seqno_prefix
pfx,
3 sobseqn_maxseqno
maxseqno,
4 sobseqn_activity_date
act_date
5* from sobseqn
SQL> /
47
www.sungardhe.com
SOBSEQN contents
FUNCTION
PFX MAXSEQNO ACT_DATE
------------------ --- ---------
---------
RECEIPT
195 06-OCT-95
ID
A 16 07-APR-95
PIDM
490 15-NOV-95
ALUMNIGIFT
40 23-MAY-95
ALUMNIPLEDGE
22 23-MAY-95
EDIREQUESTID
1 25-APR-95
EDI_DCMT_SEQNO
1 04-JAN-96
48
www.sungardhe.com
PIDM and ID Relationships
SPRIDEN_ID
...
SPRIDEN_PIDM
SPRADDR_ATYP_CODE
...
SPRADDR_PIDM
SPRTELE_TELE_CODE
...
SPRTELE_PIDM
SPBPERS_BIRTH_DATE
SPBPERS_SSN
...
SPBPERS_PIDM
f_generate_pidm
...
PIDM_SEQUENCE
f_generate_id
...
ID_SEQUENCE
49
www.sungardhe.com
SPRADDR description
SQL> describe spraddr
Name
Null? Type
--------------------------- --------
----
SPRADDR_PIDM
NOT NULL NUMBER(8)
SPRADDR_ATYP_CODE
NOT NULL VARCHAR2(2)
SPRADDR_SEQNO
NOT NULL NUMBER(2)
SPRADDR_FROM_DATE
DATE
SPRADDR_TO_DATE
DATE
SPRADDR_STREET_LINE1
VARCHAR2(30)
SPRADDR_STREET_LINE2
VARCHAR2(30)
SPRADDR_STREET_LINE3
VARCHAR2(30)
SPRADDR_CITY
NOT NULL VARCHAR2(20)
50
www.sungardhe.com
SPRADDR description (cont.)
Name
Null? Type
---------------------------- --------
----
SPRADDR_STAT_CODE
VARCHAR2(3)
SPRADDR_ZIP
VARCHAR2(10)
SPRADDR_CNTY_CODE
VARCHAR2(5)
SPRADDR_NATN_CODE
VARCHAR2(5)
SPRADDR_PHONE_AREA
VARCHAR2(3)
SPRADDR_PHONE_NUMBER
VARCHAR2(7)
SPRADDR_PHONE_EXT
VARCHAR2(4)
SPRADDR_STATUS_IND
VARCHAR2(1)
51
www.sungardhe.com
SPRADDR description (cont.)
Name
Null? Type
---------------------------- --------
----
SPRADDR_ACTIVITY_DATE
NOT NULL DATE
SPRADDR_USER
VARCHAR2(30)
SPRADDR_ASRC_CODE
VARCHAR2(4)
SPRADDR_DELIVERY_POINT
NUMBER(2)
SPRADDR_CORRECTION_DIGI
NUMBER(1)
SPRADDR_CARRIER_ROUTE
VARCHAR2(4)
SPRADDR_GST_TAX_ID
VARCHAR2(15)
SPRADDR_REVIEWED_IND
VARCHAR(1)
52
www.sungardhe.com
SPRADDR description (cont.)
Name
Null? Type
---------------------------- --------
----
SPRADDR_REVIEWED_USER
VARCHAR2(30)
SPRADDR_DATA_ORIGIN
VARCHAR2(30)
53
www.sungardhe.com
SPRTELE description
SQL> describe sprtele
Name
Null? Type
---------------------------- --------
----
SPRTELE_PIDM
NOT NULL NUMBER(8)
SPRTELE_SEQNO
NOT NULL NUMBER(3)
SPRTELE_TELE_CODE
NOT NULL VARCHAR2(4)
SPRTELE_ACTIVITY_DATE
NOT NULL DATE
SPRTELE_PHONE_AREA
VARCHAR2(3)
SPRTELE_PHONE_NUMBER
VARCHAR2(7)
SPRTELE_PHONE_EXT
VARCHAR2(4)
SPRTELE_STATUS_IND
VARCHAR2(1)
54
www.sungardhe.com
SPRTELE description (cont.)
Name
Null? Type
-----------------------------
-------- ----
SPRTELE_ATYP_CODE
VARCHAR2(2)
SPRTELE_ADDR_SEQNO
NUMBER(2)
SPRTELE_PRIMARY_IND
VARCHAR2(1)
SPRTELE_UNLIST_IND
VARCHAR2(1)
SPRTELE_COMMENT
VARCHAR2(60)
SPRTELE_INTL_ACCESS
VARCHAR2(16)
SPRTELE_DATA_ORIGIN
VARCHAR2(30)
SPRTELE_USER_IDVARCHAR2(30)
55
www.sungardhe.com
SPBPERS description
SQL> describe spbpers
Name
Null? Type
-----------------------------
-------- ----
SPBPERS_PIDM
NOT NULL NUMBER(8)
SPBPERS_SSN
VARCHAR2(9)
SPBPERS_BIRTH_DATE
DATE
SPBPERS_LGCY_CODE
VARCHAR2(1)
SPBPERS_ETHN_CODE
VARCHAR2(2)
SPBPERS_MRTL_CODE
VARCHAR2(1)
SPBPERS_RELG_CODE
VARCHAR2(2)
SPBPERS_SEX
VARCHAR2(1)
56
www.sungardhe.com
SPBPERS description (cont.)
Name
Null? Type
-----------------------------
-------- ----
SPBPERS_CONFID_IND
VARCHAR2(1)
SPBPERS_DEAD_IND
VARCHAR2(1)
SPBPERS_VETC_FILE_NUMBER
VARCHAR2(10)
SPBPERS_LEGAL_NAME
VARCHAR2(60)
SPBPERS_PREF_FIRST_NAME
VARCHAR2(15)
SPBPERS_NAME_PREFIX
VARCHAR2(20)
SPBPERS_NAME_SUFFIX
VARCHAR2(20)
SPBPERS_ACTIVITY_DATE
NOT NULL DATE
SPBPERS_VERA_IND
VARCHAR2(1)
57
www.sungardhe.com
SPBPERS description (cont.)
Name
Null? Type
---------------------------- --------
----
SPBPERS_CITZ_IND
VARCHAR2(1)
SPBPERS_DEAD_DATE
DATE
SPBPERS_PIN
VARCHAR2(6)
SPBPERS_CITZ_CODE
VARCHAR2(2)
SPBPERS_HAIR_CODE
VARCHAR2(2)
SPBPERS_EYES_CODE
VARCHAR2(2)
SPBPERS_CITY_BIRTH
VARCHAR2(20)
SPBPERS_STAT_CODE_BIRTH
VARCHAR2(3)
SPBPERS_DRIVER_LICENSE
VARCHAR2(20)
SPBPERS_STAT_CODE_DRIVER
VARCHAR2(3)
58
www.sungardhe.com
SPBPERS description (cont.)
Name
Null? Type
-----------------------------
-------- ----
SPBPERS_NATN_CODE_DRIVER
VARCHAR2(5)
SPBPERS_UOMS_CODE_HEIGHT
VARCHAR2(4)
SPBPERS_HEIGHT
NUMBER(2)
SPBPERS_UOMS_CODE_WEIGHT
VARCHAR2(4)
SPBPERS_WEIGHT
NUMBER(4)
SPBPERS_SDVET_IND
VARCHAR2(1)
SPBPERS_LICENSE_ISSUED_DATE
DATE
SPBPERS_LICENSE_EXPIRES_DATE
DATE
SPBPERS_INCAR_IND
VARCHAR2(1)
59
www.sungardhe.com
SPBPERS description (cont.)
Name
Null? Type
-----------------------------
-------- ----
SPBPERS_WEBID
RAW(1)
SPBPERS_WEB_LAST_ACCESS
RAW(1)
SPBPERS_PIN_DISABLED_IND
RAW(1)
SPBPERS_ITIN
NUMBER(9)
SPBPERS_ACTIVE_DUTY_SEPR_DATE
DATE
SPBPERS_DATA_ORIGIN
VARCHAR2(30)
SPBPERS_USER_ID
VARCHAR2(30)
60
www.sungardhe.com
SPRHOLD
Describe SPRHOLD
Write a query to retrieve
the data that you entered in SPRHOLD
Notice:
sprhold_hldd_code
sprhold_user
sprhold_from_date
sprhold_to_date
Banner General Technical
Training
Common Matching
62
www.sungardhe.com
Common Matching -
Overview
Helps prevent the accidental
creation of multiple IDs (PIDMs)
Rule-driven process to
determine whether an entity (person or non-person) is truly new
Unlimited rules can be
created
Matching can be turned
on and off system-wide or per user
Used in batch data loads
63
www.sungardhe.com
Basic Common Matching
Procedure
When a user attempts to
generate a new ID they are taken automatically to the GOAMTCH form where
they enter critical data
The Common Matching process
searches the database according to the source rule used to determine
if the Person/Non-Person already is in the database
The user can then review
the results and select the ID, update an existing ID or create a new
ID for the entity
64
www.sungardhe.com
Common Matching -
Set Up
Step 1: GORCMDD – Define data elements
for matching
Step 2: GTVCMSC – Create source
code
Step 3: GORCMSC – Define source
rules
Step 4: GORCMRL – Create matching
rules
Step 5: GORNAME – Define name translations
Step 6: GORCMUS – Setup user defaults
Step 7: GUAINST – Turn on Common
Matching
65
www.sungardhe.com
Using Common Matching
Banner General Technical
Training
Referential Integrity
67
www.sungardhe.com
Referential Integrity
Primary key constraints
Foreign key constraints
Check constraints
Unique constraints
68
www.sungardhe.com
Referential Integrity
SPRADDR_ATYP_CODE
...
SPRADDR_PIDM
STVATYP_DESC
...
STVATYP_CODE
Address
validation table
“Parent”
Address
repeating table
“Child”
69
www.sungardhe.com
Referential Integrity (cont.)
PR
123456789
Permanent
Address
PR
Address
validation table
“Parent”
Address
repeating table
“Child”
Primary key
STVATYP_CODE
Foreign key
SPRADDR_ATYP_CODE
70
www.sungardhe.com
Referential Integrity (cont.)
Oracle enforces relationships
between tables through the use of Primary and Foreign key constraints
Banner uses naming conventions
for these constraints
71
www.sungardhe.com
Primary keys
PK_ppppppp is the Banner
convention
PK is for Primary key
ppppppp is the Primary key
table name
Example:
PK_STVATYP
72
www.sungardhe.com
Foreign key
FK + n + ”_” + fffffff
+ ”_INV_” + ppppppp + ”_CODE”
FK for Foreign key
n is a one-up number that
distinguishes potential duplicate Foreign key names in a given table
fffffff is the Foreign key
table name
ppppppp is the Primary key
table name
Example:
FK1_SPRADDR_INV_STVATYP_CODE
73
www.sungardhe.com
Application Hierarchy - Unique Constraints
GUROUTP_USER_ID
GUROUTP_ONE_UP_NO
GUROUTP_FILE_NO
GUROUTP_SEQ_NO
GUROUTP_LINE
GUBOUTP_USER_ID
GUBOUTP_ONE_UP_NO
GUBOUTP_FILE_NO
GUBOUTP_USER_NAME
Base
table
“Parent”
Repeating
table
“Child”
74
www.sungardhe.com
Application Hierarchy
- Referential Integrity (cont.)
PK_GUBOUTP
FK1_GUROUTP_INV_GUBOUTP_KEY
FOREIGN KEY
UK1_GUBOUTP_JOB
Delete from GUBOUTP
What Happened?
Review GURRDDL for GUBOUTP
Banner General Technical
Training
Banner Directory Structure
76
www.sungardhe.com
Banner directories
$BANNER_HOME is the Unix
environment variable for /u0x/home/banner
BAN_HOME is the VMS logical
for d01:[home.banner]
Each product has its own
directory
Other directories at $BANNER_HOME
admin, common, links, upgrade,
install
77
www.sungardhe.com
OS Banner directories
General
Student
Finance
...
Admin
Common
Links
/u01/home/banner
78
www.sungardhe.com
Banner Operating System
Directories
79
www.sungardhe.com
Directory Structure
for
Client-developed Items
80
www.sungardhe.com
dbproc Trigger Naming
Conventions
abcdddde.sql is the Banner
convention for triggers
a – Product Identifier
b – Module
c – (T) rigger
dddd – Table identifier
such as PERS, IDEN, EMPL, etc
e – Numbers 0 – 9, letters
a through z
81
www.sungardhe.com
Database server environment
Banner environment variables
$BANNER_HOME
$ORACLE_HOME
$BANNER_LINKS
Banner General Technical
Training
Conversion and Implementation
Strategies
83
www.sungardhe.com
Product Table Owners
Banner System
Oracle User Product Owner
General
GENERAL
General Person
SATURN
Finance
FIMSMGR
Accounts Receivable
TAISMGR
Position Control
POSNCTL
Payroll
PAYROLL
Student
SATURN
Financial Aid
FAISMGR
Advancement
ALUMNI
Security
BANSECR
All Banner Views
BANINST1
84
www.sungardhe.com
Conversion
Data entry standards
Validation tables
Rule tables
Method for conversion
Shared tables
85
www.sungardhe.com
SQL*Loader and Import
When loading or importing
data, the constraints are checked as each row is inserted into the database
Although it may speed up
data, it is not recommended to disable constraints
Consider creating the indexes
after data is loaded to speed up the load
86
www.sungardhe.com
Create site forms
First Approach:
Using the skeleton Form
Start with GUASKEL.fmb
to gain access to the global variables and common triggers
This form has the bare
minimum needed for a Banner Form to run
Add the fields and other
functionality as needed
Save, Compile and implement
the new on-site form in Banner
87
www.sungardhe.com
Create site forms
Second approach:
Using an existing Banner
Form
Identify a Banner form
that suits closest to your needs
Copy the source code
to a different form file
Remove unwanted functionality
Add the functionality
needed
Compile and implement
the new on-site form in Banner
88
www.sungardhe.com
Create site forms
Some Considerations:
Keep the source code for
your forms separate from the forms directories
Do not modify existing
Banner Forms
It is recommended that
you first take Oracle Forms training
89
www.sungardhe.com
Programs and Reporting
Delivered tools
Pro*C, Pro*COBOL, Oracle
tools
Pro*C Program Template
– GURSKEL.PC
Other options
MS Access, Crystal Reports,
Brio Query,Toad,etc
Methodologies
Web, Object Access, ODS
Reports and processes grid
90
www.sungardhe.com
Utilities
GURRDDL
GURPDED
GURLSID
GURDLID
indexes.sql
GJRRPTS
91
www.sungardhe.com
Utilities (cont.)
GURHELP
GURTABL
GURSKEL
GURRDOC
GURSTOP
More - General Technical
Reference Manual
general/plus directory
Banner General Technical
Training
SunGard Support Center
93
www.sungardhe.com
SunGard Support Center
Browse open and resolved
contacts of your organization in detail
Browse all product defects
reported by your organization
Review the product calendar
for future product enhancements and defect resolutions
Request electronic distribution
download of software modifications and other updates
94
www.sungardhe.com
SunGard Support Center (cont.)
Download Banner documentation
Browse the Frequently Asked
Questions (FAQs)
Browse Known Issues Reports
for defect descriptions, corrections, and workarounds
Request for Product Enhancements
(RPEs)
95
www.sungardhe.com
SunGard Support Center (cont.)
Contacting SunGard Support
Center
Object name
Object version number
Oracle version
Detailed description of
issue
Steps used to recreate
96
www.sungardhe.com
SunGard Support Center (cont.)
TCPNet Listservers
Accessed through http://lists.sungardhe.com
For listservers, email listserv@sungardhe.com
Type Lists in
body of message and send
You will receive a list of
available listservers
To subscribe, email listserv@sungardhe.com
Type:
subscribe LISTNAME
First_name Last_name
You will receive a confirmation
email
containing further instructions
Banner General Technical
Training
Job Submission
98
www.sungardhe.com
How does Job Submission
work?
Defines the parameters
used to execute any given process
Communicates with the database
server environment to schedule the process
Communicates with the database
server environment to print the output of the process
100
www.sungardhe.com
Use Job Submission
Start Job Submission Sleep/Wake
Establish printers
Define processes
Define process parameters
Submit job
Review results
101
www.sungardhe.com
Start Job Submission
GURJOBS is a Pro*C program
created to handle requests in a client-server configuration
GURJOBS must be running
in Sleep/Wake mode prior to Job Submission
The user ID jobsub
or banjobs should start this process
All output from Job Submission
is owned by jobsub/banjobs
GURJOBS is dependent upon
Oracle database pipe (dbmspipe.sql)
102
www.sungardhe.com
Establish printers
Printer Validation Form
(GTVPRNT)
Defines the printers that
are accessible to users
Printers must be accessible
from the network
The database server must know
about the printer and the print queue
NOPRINT and DATABASE may be
defined as printer codes, but do not route to a printer
103
www.sungardhe.com
Define processes
Process Maintenance Form
(GJAJOBS)
Allows for maintenance of
overall process attributes
Process name
Process description
Process type (Pro*C, Pro*Cobol,
etc.)
104
www.sungardhe.com
Define process parameters
Parameters for any given
process can be maintained dynamically through Banner forms
Parameter Definition Form
(GJAPDEF)
Parameter Value Validation
Form (GJAPVAL)
Default Parameter Value Validation
Form (GJAPDFT)
105
www.sungardhe.com
Submit job
Process Submission Control
Form (GJAPCTL)
Creates a one-up number to
uniquely identify the job
Passes the user-entered parameters
and the one-up number to the table GJBPRUN
At run time, all processes
access GJBPRUN for their parameters
106
www.sungardhe.com
Review results
Process Results Form (GJARSLT)
Allows for the review of the
process log
Output files
All process output (.lis files)
reside in the jobsub home directory
Log files (.log) also reside
in the jobsub home directory
107
www.sungardhe.com
Sleep/Wake Processing
Method of running a process
in a cyclical manner
Process is submitted by
a user and runs through Job Submission
The process then resubmits
itself for a specified length of time (sleeps)
When the resubmit time
approaches, the process starts back up and re-runs (wakes)
This process continues
until a user stops the process from GJASWPT
108
www.sungardhe.com
Job Submission at
the functional level
109
www.sungardhe.com
Job Submission at
the database level
Banner General Technical
Training
Population Selection
111
www.sungardhe.com
Population Selection
Extracts a subset of IDs
for use in Banner reports and letters
Allows processes to be
run using that subset of IDs, such as people, vendors, or organizations
Used for batch processes,
reporting purposes, and facilitating Letter Generation
112
www.sungardhe.com
How does Population
Selection work?
Selects the PIDM* for individuals
who meet the selection criteria
SELECT DISTINCT Pidm
FROM Table
WHERE
Criteria
*Primary Identification Master
113
www.sungardhe.com
Four basic steps
Identify an application
Define the Population Selection
criteria
Extract the IDs that match
the criteria
Review the Population Selection
114
www.sungardhe.com
Step 1: Identify an
application
An application owns a grouping
of Population Selections
The rules established by
the parent application will be inherited by the child objects
Examples: General, Recruiting,
Alumni
Although the application
must be defined in the Application Rules table (GLRAPPL), the rules
(criteria) at this level are optional
115
www.sungardhe.com
Step 2: Define
criteria
Example: test_female
SELECT DISTINCT spbpers_pidm
FROM spbpers, spraddr
WHERE spbpers_pidm = spraddr_pidm
**
AND spraddr_stat_code
= 'CA'
AND spbpers_sex
= 'F'
** PIDM joins are performed
automatically
116
www.sungardhe.com
Define criteria (cont.)
Population Selection Definition
Rules table (GLRSLCT)
Allows for entry of the specific
criteria which distinguishes one population from another
If the tables are joined by
PIDM, the PIDM join is automatic
If the tables are joined by
additional columns, the conditions must be explicitly defined
As the form is exited,
the process Parameter Selection Process (GLBPARM) compiles the select
statement
117
www.sungardhe.com
Step 3: Extract the
IDs
Run the Population Selection
Extract Process (GLBDATA) from Job Submission
Execute GLBDATA from the Job
Submission Form (GJAPCTL)
Or enter GLBDATA in Direct
Access
118
www.sungardhe.com
Extract the IDs (cont.)
Population Selection Extract Process (GLBDATA) parameters
Selection Identifier 1: Required
Selection Identifier 2:*
New Selection Identifier*
Enter description of Selection*
Enter (U)nion, (I)ntersect,
(M)inus*
Application :Required
Creator ID : Required
Detailed Execution Report
* Used only for Union, Intersect, or Minus functionality
119
www.sungardhe.com
Step 4: Review Population
Selection
Population Selection Extract
Data Form (GLAEXTR)
Review the results of running
Population Selection Extract Process (GLBDATA)
Add other ID's not processed
by GLBDATA
Remove ID's processed by GLBDATA
120
www.sungardhe.com
Population Selection
Application definition
GLIAPPL
GLRAPPL Objects
GLIOBJC
GLIOBJT
Variable usage
Variable definitions
GLRVRBL
SQL Statement in
GLRCMPL
Population Selection rules
GLRSLCT
Manual population creation
GLAEXTR
View population
GLIEXTR
Population Stored in
GLBEXTR / GLVEXTR
Banner General Technical
Training
Letter Generation
122
www.sungardhe.com
What Is Letter Generation?
A way to extract data from the Banner database and merge that data with
text, then print the results and log what letter was created and for
whom the letter was created
123
www.sungardhe.com
Letter Generation
Extracts data from Banner based on a given Population Selection
Merges extracted data with text
Prints the results
Maintains a log of printed letters
124
www.sungardhe.com
How does Letter Generation
work?
It extracts specific data
SELECTspriden_first_name,
spriden_last_name
FROMspriden
Only from those PIDMs extracted
during a Population selection
WHERE
pop_sel criteria
125
www.sungardhe.com
How does Letter Generation
work? (cont.)
Next, it either merges
the extracted data with paragraphs customized for your implementation
or creates a delimited extract data file to be used with a desktop application
126
www.sungardhe.com
Example letter
April 20, 2000
John Doe
123 Street
Malvern, PA 19355
Dear John,
The University is in the process of
verifying student information in
order to prepare for registration
using Banner WEB. Please verify
that the following information is
current and correct:
ID: 123456789
Birth date: 01/01/80
If the information is incorrect, notify
our office immediately. Thank
you for your assistance.
Sincerely,
127
www.sungardhe.com
Example letter (cont.)
April 20, 2000
John Doe
123 Street
Malvern, PA 19355
Dear John,
The University is in the process of
verifying student information in
order to prepare for registration
using Banner WEB. Please verify
that the following information is
current and correct:
ID: 123456789
Birth date: 01/01/80
If the information is incorrect, notify
our office immediately. Thank
you for your assistance.
Sincerely,
Heading
New
Paragraph
128
www.sungardhe.com
Steps to create a
letter
Define variables to be
used
Construct paragraphs
Combine paragraphs into
letters
Extract population
Extract letter variables
Print letter or create
merge file
129
www.sungardhe.com
Letter Generation
Letter Extract Process (GLBLSEL)
Execute GLBLSEL from the
Process Submission Control Form (GJAPCTL)
Or enter GLBLSEL in Direct
Access
Extracts data from the
database for selected PIDMs and stores it in a collector table
130
www.sungardhe.com
Letter Generation (cont.)
Letter Generation Report (GLRLETR)
Removes the selected variables
from the Collector table
Merges that data with text
specified in a letter
Prints the letter or creates
the appropriate word processing file
Banner General Technical
Training
Summary Review
132
www.sungardhe.com
Summary
You should know:
Banner naming convention
for tables, forms, and processes
Major forms and processes
in Banner General
The structure of directories
and their contents
How Job Submission, Population
Selection, and Letter Generation work
Where to get needed data.
Check researching directories, documentation, or consultants
133
www.sungardhe.com
Additional resources
Other Technical/Functional
training sessions
Documentation
Discussion lists
SunGard Higher Education
Support Center
http://education.sungardhe.com/edcenter
- Ed Center
Computer Based Training
(CBT)
Professional Services staff
Banner General Technical
Training
Security Administration
135
www.sungardhe.com
Oracle
Role
Class
User
level Security
USR_NAMED_ROLE
Directly grants
permissions to user on specific objects. Will affect sql tools and ODBC
BAN_DEFAULT_CONNECT.
Create session
only.
Tab
Level Security
Forms
assigned
To
Class
BAN_DEFAULT_M
BAN_DEFAULT_Q
Banner
Security
Bansecr
Assign forms
directly.
136
www.sungardhe.com
VBS
FGAC
Fine
Grained Access Control
Protection
of sensitive data
Restrict
users based on values. Example :
Restrict users to
allow maintenance only on address type codes associated with their
office area.
Restrict users
based on (Pidm ). Ex : Finance can have access on vendors
pidm records only, HR on employees and Registrar’s office on students.
Masking, Hiding
of specific fields in a form. Can be done to affect all users, any user
or a business profile. Example :
Hiding
or masking SSN for a specific user.
PII
137
www.sungardhe.com
What does
Banner Security do?
Protects underlying database structures while giving appropriate groups
of people appropriate access based upon job responsibilities
138
www.sungardhe.com
How does Banner Security
work?
Prevents the user from
using their Banner-required Oracle privileges in a non-Banner (third-party)
application
Provides object authentication
to prevent obsolete or user-developed objects from accessing the database
139
www.sungardhe.com
How is security implemented?
Uses Oracle's concept of
role-level privileges
Groups job responsibilities
into 'classes'
Grants appropriate access
to objects under the guise of 'classes'
140
www.sungardhe.com
rather than administration
of database objects!
The Banner approach
to security
Administration of people
according to their jobs ...
141
www.sungardhe.com
Banner database security
Oracle privileges
Oracle roles in Banner
Banner classes
The Banner Security process
142
www.sungardhe.com
Oracle privileges
Access can be granted to
one OBJECT
at a time:
GRANT SELECT
ON SPRIDEN TO JOHN;
143
www.sungardhe.com
Oracle privileges (cont.)
Access can be granted one
USER at a time:
GRANT SELECT
ANY TABLE TO JOHN;
144
www.sungardhe.com
Oracle privileges (cont.)
Access can be granted one
ROLE at a time:
CREATE ROLE
CLERKS;
GRANT SELECT ANY TABLE TO CLERKS;
A better solution, but
how do you control the non-Banner environments?
145
www.sungardhe.com
Oracle roles in Banner
Roles are groups of Oracle
privileges
The role maintenance function
provides a front end to manage Oracle roles
When used in conjunction
with Banner classes, these roles are dynamically invoked and revoked
as access is requested
The use of roles plays
a vital part in securing your Banner application
146
www.sungardhe.com
Oracle roles in Banner (cont.)
You should use Banner pre-built
roles
BAN_DEFAULT_CONNECT
GRANT CONNECT
TO BAN_DEFAULT_CONNECT
BAN_DEFAULT_CONNECT privileges are limited
Users can connect to a database
Users cannot access tables
Note:
Banner Security discourages direct grants of any kind.
147
www.sungardhe.com
The role
BAN_DEFAULT_Q gives the user
privileges to execute all objects and select on all tables in the database
GRANT SELECT
ANY TABLE TO
BAN_DEFAULT_Q
GRANT EXECUTE
ANY OBJECT TO
BAN_DEFAULT_Q
Oracle roles in Banner (cont.)
148
www.sungardhe.com
The role BAN_DEFAULT_M gives the user DBA privileges to all tables
and all objects in the database
INSERT
ANY,
UPDATE
ANY,
DELETE
ANY,
SELECT
ANY, and
EXECUTE
ANY
Oracle roles in Banner (cont.)
149
www.sungardhe.com
Oracle roles in Banner (cont.)
Each role is password protected
Each password is encrypted
Example:
SET ROLE
BAN_DEFAULT_M IDENTIFIED BY 123;
You cannot invoke a role
from SQL*Plus without knowing the password even though the role is granted
to a user
Tables are protected from
direct access as long as role is not invoked from SQL*Plus
150
www.sungardhe.com
Oracle roles in Banner (cont.)
If no default is assigned,
ALL granted roles are invoked at log in
Example:
GRANT BAN_DEFAULT_CONNECT
TO JOHN;
GRANT BAN_DEFAULT_M TO JOHN;
Note:
Because John was not assigned a default role, he will be able to perform
DBA activities in SQL*Plus
151
www.sungardhe.com
Oracle roles in Banner (cont.)
If a default role was assigned,
the default role is automatically invoked at log in
No password is needed to
invoke an object
Example:
GRANT BAN_DEFAULT_CONNECT
TO JOHN;
GRANT
BAN_DEFAULT_M TO JOHN;
ALTER
USER JOHN DEFAULT ROLE
BAN_DEFAULT_CONNECT
Note:Upon log in, John's default role, BAN_DEFAULT_CONNECT,
is invoked
152
www.sungardhe.com
Oracle roles in Banner (cont.)
Every user must be assigned
a default role to prevent Oracle from automatically invoking all granted
roles
This default role should
be BAN_DEFAULT_CONNECT
Users connecting to the
database through non-Banner applications will only be able to connect
153
www.sungardhe.com
Banner classes
Classes are to Banner what
roles are to Oracle except classes control Banner objects (not Oracle
tables)
Banner forms and processes
are organized into groups (classes)
Classes organize Banner
processes into job responsibilities unique to your organization
154
www.sungardhe.com
Banner classes (cont.)
Processes are organized
additionally by the type of access desired for the class
Use the pre-determined
Banner roles exclusively
155
www.sungardhe.com
Banner classes (cont.)
Banner Processes / Access
Banner Class
SOATERMBAN_DEFAULT_M
SFAESTSBAN_DEFAULT_M
SFARSTSBAN_DEFAULT_MRegistration
Term
STVESTSBAN_DEFAULT_MSetup
STVRSTSBAN_DEFAULT_M
156
www.sungardhe.com
Banner classes (cont.)
Banner Processes / AccessBanner
Class
SOATERMBAN_DEFAULT_Q
SFAESTSBAN_DEFAULT_Q
SFARSTSBAN_DEFAULT_Q
STVESTSBAN_DEFAULT_Q
STVRSTSBAN_DEFAULT_Q
Student Registration
SFAREGSBAN_DEFAULT_M
SFRSCHDBAN_DEFAULT_M
SFASLSTBAN_DEFAULT_M
157
www.sungardhe.com
Banner classes (cont.)
Classes are then assigned to users
Janet
Sally
George
Jane
Registration Term
Setup
Student
Registration
158
www.sungardhe.com
Banner classes (cont.)
Changes to your environment
are managed through the maintenance of classes
You are not managing users
when classes are changed
You are not managing database
objects
159
www.sungardhe.com
Class considerations
Make your classes granular
(Fit the lowest common denominator)
A user with access to two
classes containing the same object will ALWAYS be granted the class
with the higher role privileges for the object
160
www.sungardhe.com
The Banner Security
process
Encrypted passwords in
roles protect database objects
Classes organize forms
and processes
Classes decide how a user
accesses the forms and processes
161
www.sungardhe.com
The Banner Security
process (cont.)
Every Banner form, report,
or process executes a multi-phase security check
When the form, report,
or process finishes, all grants are revoked
162
www.sungardhe.com
The Banner Security
process (cont.)
Request access
to form
What class does
user belong to?
Do any of those
classes have access to this object?
What role should
be set for this user to access this object?
163
www.sungardhe.com
The Banner Security
process (cont.)
ROLE
Unencrypt the
password for this role
Set ROLE IDENTIFIED
BY password
Execute form
164
www.sungardhe.com
The Banner Security
process (cont.)
Processes invoke roles
and passwords at the time of execution
Roles cannot be manually
invoked because users know nothing about these passwords
Passwords are unencrypted
through a three-phase Pro*C process and embedded into the forms and
reports
165
www.sungardhe.com
Security can be administered
using any
Oracle ID that begins with the text
BANSECR
Note:
For instructions on setting up additional
BANSECR accounts, see the Distributed
Security section in the General Security
Technical Reference Manual
The master security account
(BANSECR) owns all security objects
All security is administered
through the Security Maintenance Form (GSASECR)
The Banner Security
process (cont.)
166
www.sungardhe.com
The Security Maintenance
Form (GSASECR)
Provides the following
maintenance capabilities:
Security profile maintenance
Oracle user maintenance
Role maintenance
Class maintenance
Security violation tracking
167
www.sungardhe.com
Steps for Security
Administration
1.Establish a profile
2.Verify objects
3.Set up roles
4.Combine classes with roles
5.Assign users to classes
6.Review security violations
168
www.sungardhe.com
Step 1: Establish
a profile
Profile maintenance
Security mode
Encryption keys
Version checking
169
www.sungardhe.com
Step 1: Establish
a profile
Security modes
None
Relies on basic Oracle security
Allows direct grants for tables
and roles
Process Level
Relies on basic Oracle security
Uses Banner 2.0 Forms level
security
Role Level
Involves an encrypted role
password for each process
170
www.sungardhe.com
Step 1: Establish
a profile
Encryption
keys
Three seed numbers used
as the basis for the password encryption routines
Changing these numbers
necessitates the regeneration of EVERY process, form, and report
Pro*C, Pro*Cobol, Forms,
include files, etc.
171
www.sungardhe.com
Step 1: Establish
a profile
Version checking
Objects and their versions
are included in the database
Version information is
updated during the upgrade process
When enabled, version checking
forces Banner to verify that the form being used is the current version,
as recorded in the database
172
www.sungardhe.com
Step 1: Establish
a profile
Version checking (cont.)
If you maintain multiple
versions of Banner on multiple servers, version checking is helpful
for updating source code
If the proper version of
the source is not replicated correctly, version checking will prohibit
access to the form
173
www.sungardhe.com
Step 1: Establish
a profile
Call Query
Impacts any form using
a query role
ends in _Q
When Call Query is enabled,
the user is informed he is in Query mode when attempting to change or
enter a value (Otherwise, user is informed when he tries to save)
Upon entry to the form,
the user knows he is in Query mode
174
www.sungardhe.com
Step 1: Establish
a profile
Encrypt No
Pass
Assigns an encrypted password
to every Banner role that does not currently have a password
Roles with BAN_ prefix only
Used to synchronize the
new roles with the delivered roles when you create Banner roles via
SQL*Plus
175
www.sungardhe.com
Step 1: Establish
a profile
Encrypt All
Reassigns all the Banner
roles the appropriate encrypted passwords
Roles with BAN_ prefix
only
Used when seed numbers
have changed
Note:
If seed numbers have not changed, only the
BAN_ roles that have been created will be
impacted
176
www.sungardhe.com
Step 2: Verify objects
Processes, forms, and reports
are objects to which a Banner user may request access
Current version
Sys code (Banner module)
Default role
Used by (Classes)
177
www.sungardhe.com
Step 2: Verify objects
Current version
The object name and current
version are found in the Title Bar of every Banner form
If version checking is
activated, object name and current version are verified before user
can access a form
Distribution of source code
178
www.sungardhe.com
Step 2: Verify objects
Sys code (Banner
module)
Sys code indicates the
Banner module that the object belongs to
Use Banner naming conventions
A=Advancement
G=General
S=Student
F=Finance
R=Financial Aid
etc.
179
www.sungardhe.com
Step 2: Verify objects
Default role
This role will be invoked
by default should a different role not be specified
Object privileges within
classes will specify roles to be invoked
BAN_DEFAULT_M is recommended for objects outside of classes
since it ensures access to all appropriate tables
This can be changed to
a site-specific role or BAN_DEFAULT_Q
180
www.sungardhe.com
Step 2: Verify objects
Used by (Classes)
Provides a list of classes
and users which have been granted access to the object
Object can be specified
in a direct grant
User ID given the access
is listed
Object can be specified
in a class
Class name given the access
is listed
Form shows how each object
is granted including class and/or direct to user grants with role being
invoked for each
181
www.sungardhe.com
Step 3: Set
up roles
Role maintenance
Creating roles
Copying roles
Privileges
Used by and Granted to
182
www.sungardhe.com
Step 3: Set
up roles
Create roles
All roles should begin
with either
BAN_ or USR_
BAN_ roles will automatically be password
protected
USR_ roles are not password protected
and can be given as a default Oracle role
If a role does not follow
this naming convention:
You WILL NOT be able to assign
that role using the Security Maintenance Form (GSASECR)
Banner password encryption
will not be utilized
183
www.sungardhe.com
Step 3: Set
up roles
Copy roles
Recommended for cloning
the delivered Banner roles and making site-specific changes
Clone of BAN_DEFAULT_CONNECT should be used (Individual Oracle objects
with greater privileges can be added to a custom role)
184
www.sungardhe.com
Step 3: Set
up roles
Privileges
Assigning specific objects
to a role allows for site-specific grants rather than System level grants
Objects used in roles refer
to Oracle objects only, not Banner objects
185
www.sungardhe.com
Step 3: Set
up roles
Privileges (cont.)
If a DBA grants an object
to a role through SQL*Plus, this access will not show up on the Security
Maintenance Form (GSASECR)
All access should be managed
through the
Security Maintenance Form
(GSASECR)!
186
www.sungardhe.com
Step 3: Set
up roles
Privileges (cont.)
Use the SYS PRIV
button to assign, revoke, and view System level privileges granted to
a role
187
www.sungardhe.com
Step 3: Set
up roles
Used by and Granted to
The USED
BY OBJECTS button shows the
users or classes associated with the Banner object
The GRANTED
TO button:
Lists all users who have
been granted this role
Indicates if current role
is the default role
Indicates whether a user has
ADMIN permission
188
www.sungardhe.com
Step 4: Combine
classes with roles
Class maintenance
Class codes
Objects
Users
Synchronization
189
www.sungardhe.com
Step 4: Combine
classes with roles (cont.)
Each object lists the role
that is invoked at the time of execution
User access to an object
is determined by an object's role within a class
Sys code identifies the
Banner module
190
www.sungardhe.com
Step 4: Combine
classes with roles
Class objects
The role chosen when an
object is assigned to a class overrides the default role
Use the wildcard %
to group objects into a class
Role list of values can
be accessed for viewing by clicking the ROLE LOV button
191
www.sungardhe.com
Step 4: Combine
classes with roles
Class design
Design classes carefully
Use the Banner naming convention
BAN_name_C to indicate Banner class
Example:
Modular approach: BAN_FINANCE_C has access to all objects that begin with
F
More granular approach:
BAN_AP_C,
BAN_CASHIER_C, BAN_FIXED_ASSET_C
192
www.sungardhe.com
Step 4: Combine
classes with roles
Users
Users are not enrolled
in classes through the Class Maintenance window
You can view (but not change)
the classes to which the user is enrolled
To enroll a user in a class,
use the USER
MAINTENANCE button on the
Security Maintenance Form (GSASECR)
193
www.sungardhe.com
Step 4: Combine
classes with roles
Synchronization
When a user is assigned
to a class, the objects and associated roles are dynamically granted
to the user
A user may be granted many
roles at this time without synchronization
194
www.sungardhe.com
Step 4: Combine
classes with roles
Synchronization (cont.)
If the security administrator
later changes class attributes, granted roles are not necessarily revoked
and re-granted to the user
When changes are made to
classes, the
Out of Sync message appears in the Messages column
in the Class Maintenance window
195
www.sungardhe.com
Step 4: Combine
classes with roles
Synchronization (cont.)
To force the grants to
be revoked and appropriately re-granted, the security administrator
can simply click the SYNCHRONIZE button
The date listed as the
Last Sync Date is updated automatically
196
www.sungardhe.com
Step 5: Assign
users to classes
User maintenance
Oracle user data
Banner permissions for objects
197
www.sungardhe.com
Enter all Banner users
using the Security Maintenance Form (GSASECR)
Manage all appropriate
grants and permissions using the User Maintenance section of GSASECR
Step 5: Assign
users to classes (cont.)
198
www.sungardhe.com
Step 5: Assign
users to classes
Oracle user data
Manage data that is normally
entered through SQL commands with the User Maintenance section of GSASECR
Username
Password
Temp tablespace
Default tablespace
Default role
BAN_DEFAULT_CONNECT (Strongly
recommended!)
199
www.sungardhe.com
Step 5: Assign
users to classes
Permissions
Directly grant users access
to any object (Oracle or Banner)
You can choose to bypass classes,
but this will create more work in the long run
Apply the appropriate grants
instantly by dynamically enrolling a user in a class
200
www.sungardhe.com
Step 6: Review security
violations
The Security Violation
Review and Maintenance window reports all invalid attempts to access
the Banner database
Each violation is assigned
a severity of Level 1 to Level 3
You can review which process
the user tried to run, when the user tried to run it, and the reason
for the failure
Note: See the
Banner General Security TRM for specific information on how these error
messages are flagged
201
www.sungardhe.com
The table storing these
violations may fill rapidly
To avoid exceeding the
table's maximum capacity, the security administrator should occasionally
truncate this table
Step 6: Review security
violations (cont.)
202
www.sungardhe.com
Oracle access for
third-party products
Third-party applications
need access to Oracle tables rather than Banner objects
Developers need maintenance
access to Oracle tables for conversion support and to create local processes
203
www.sungardhe.com
Oracle access for
third-party products (cont.)
Functional users needing select-only
access to Oracle tables:
Create Oracle roles beginning
with USR_ and grant table access to the roles
Oracle table access should
not exceed the select access granted through Banner
Assign USR_ roles to the
functional user's Banner account as default Oracle roles
204
www.sungardhe.com
Oracle access for
third-party products (cont.)
IT staff and users needing maintenance
access to Oracle tables:
Create a second Oracle
account for all users needing Oracle table access greater than select-only
Create Oracle roles beginning
with USR_ and grant table access to the roles
Assign USR_ roles to the
technical user's developer Oracle account as default Oracle roles
205
www.sungardhe.com
Oracle access for
third-party products (cont.)
The primary Oracle account
will have access to all the Banner objects with no access to Oracle
tables
The development Oracle
account will have access to all the Oracle tables with no Banner object
access
206
www.sungardhe.com
Security Administration
Overview
Define
Objects
Group
Privileges
into
Roles
Group
Objects
and
Roles
into Classes
Define
Users
207
www.sungardhe.com
Security Administration
Overview (cont.)
GURUOBJ
GUBROLE
GUVRPRV
GTVCLAS
GURUCLS
GURSQLL
Banner General Technical
Training
Value Based Security
209
www.sungardhe.com
FGAC Definition
Fine-Grained Access Control
A means of providing row
level security based upon existing columns and tables in your database
Not a Banner invention
– ORACLE functionality
210
www.sungardhe.com
VBS Definition
Value Based Security
Interface between Banner
and Oracle
Defined for individual
users as needed
211
www.sungardhe.com
How VBS Works
Where spraddr_atyp_code
= 'MA'
4. Row inserted
or Oracle error message displayed
3. FGAC
appends Predicate to SQL statement
2. FGAC executes
GOKFGAC predicate function and retrieves predicate
1. User
builds a SQL statement
Insert into spraddr
(spraddr_pidm, …)
Values (1234, …)
212
www.sungardhe.com
Banner VBS Process
Flow
Define
VBS Restrictions
Setup
VBS Domains
Create
the Rules and Assign Users
Test
Restrictions
Migrate
Rules to Production
Functional User Tasks
Combined Tasks
Technical User Tasks
Verify
Restrictions
Identify
and Setup Users
213
www.sungardhe.com
Analysis Worksheet
Performing this analysis will help
you to:
fill in the pieces that
are required for setting up the VBS group rule
trace ramifications of
the rule
analyze restrictions and
possible outcomes
214
www.sungardhe.com
Establishing the Business
Profiles
Enter a business profile
for specific users using the FGAC Business Profile Validation Form (GTVFBPR)
Assign user IDs to the
business profile using the FGAC Business Profile Assignments Form (GOAFBPR)
215
www.sungardhe.com
Defining the Domain
Define predicate statements
Define/identify the domain
codes using GTVFDMN
Create/identify the driver
rules using GORFDMN
Enter the domain tables
using GORFDPL
Run gfvbsaddpol.sql to
create the policies
Run gfgacdroppol.sql to
drop the policies
216
www.sungardhe.com
Process Flow Diagram
Domain and driver (GTVFDMN
and GORFDMN)
Domain Table 1 (