ID Badge Access

The ID Badge Application will access our database to get a list of active employees (both staff and students).  This will be used to cross reference if an employee can be issued an ID Badge.

Since ID Badge uses MySQL and the ITS Admin Application uses Oracle, the simplest way to allow access from ID Badge to ITS Admin is to setup credentials.

Access:

• Read-only access
• Read-only database user created called "rolistview" (see SDBA-2367)
• access to a single "view" that will list all active staff and student employees.

Data:

The query will pull data from a view table that has the following data:

• Lastname - the employee's last name
• Firstname - the employee's first name
• Username - the employee's UH username
• Sectname - the Section an employee belongs to and works with
• Supervisor - the employee's supervisor
• Emptype - the employee's type is either "staff" or "student"

The SQL View:

-- SQL
SELECT * FROM (
SELECT * FROM
--STAFF ONLY
(SELECT pers.perslastname as lastname,
pers.persfirstname as firstname,
pers.persitsusername as username,
sect.sectname,
ppos.pposmanager as supervisor,
'staff' as emptype
FROM personnel pers,
persposition ppos,
section sect
WHERE pers.persid = ppos.persid
AND ppos.pposstatus = 'A'
AND ppos.sectid = sect.sectid
) staff
UNION ALL
SELECT * FROM
--STUDENT ONLY
(SELECT upper(stdlink.stdlastname) as lastname,
upper(stdlink.stdfirstname) as firstname,
personnel.persitsusername as username,
section.sectname,
CONLINK.contactfirstname || ' ' || CONLINK.contactlastname as supervisor,
'student' as emptype
FROM SECEADM.placement@secedb_link PLCLINK,
SECEADM.student@secedb_link STDLINK,
SECEADM.jobcontacts@secedb_link SUPLINK,
SECEADM.contact@secedb_link CONLINK,
personnel,
persposition,
section
WHERE PLCLINK.plcStatus = 'A'
AND PLCLINK.stdId = STDLINK.stdId
AND PLCLINK.plcId = PERSPOSITION.empId
AND PLCLINK.plcjobid = SUPLINK.jobid
AND SUPLINK.aprid = 2
AND SUPLINK.contactid = CONLINK.contactid
AND STDLINK.stdUhuuid = PERSONNEL.persUhuuid
AND PERSONNEL.persId = PERSPOSITION.persId
AND PERSPOSITION.sectId = SECTION.sectId
) std
)
ORDER BY sectname, lastname, firstname;