Application Authorization - Expert Oracle and Java Security - page 215

Listing 10-9. Procedure to Update Two-Factor Code Cache
PROCEDURE p_update_2fact_cache(
m_employee_id v_two_fact_cd_cache.employee_id%TYPE,
m_application_id v_two_fact_cd_cache.application_id%TYPE,
m_two_factor_cd v_two_fact_cd_cache.two_factor_cd%TYPE,
m_distrib_cd v_two_fact_cd_cache.distrib_cd%TYPE,
m_err_no OUT NUMBER,
m_err_txt OUT VARCHAR2 )
IS
v_count INTEGER;
BEGIN
m_err_no := 0;
SELECT COUNT (*) INTO v_count
FROM v_two_fact_cd_cache
WHERE employee_id = m_employee_id
AND application_id = m_application_id;
IF v_count = 0 THEN
INSERT INTO v_two_fact_cd_cache( employee_id, application_id,
two_factor_cd, distrib_cd ) VALUES
( m_employee_id, m_application_id, m_two_factor_cd, m_distrib_cd );
ELSE
UPDATE v_two_fact_cd_cache SET two_factor_cd = m_two_factor_cd,
ip_address = SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ),
distrib_cd = m_distrib_cd, cache_ts=SYSDATE
WHERE employee_id = m_employee_id
AND application_id = m_application_id;
END IF;
EXCEPTION
WHEN OTHERS THEN
m_err_no := SQLCODE;
m_err_txt := SQLERRM;
appsec.app_sec_pkg.p_log_error( m_err_no, m_err_txt,
'app_sec_pkg.p_update_2fact_cache' );
END p_update_2fact_cache;
You can see that we have three Oracle statements in the body of this procedure: a SELECT , an
INSERT ,and an UPDATE . If you recall, in the distribute2factor() method in the last chapter, we only
required two statements. There we attempted an update and read the value of a returned integer. If the
return value was 0, then no records were affected by the update, and we did an insert. Here, however, we
must do the SELECT COUNT manually to see if there are any records to update. If the count is 0, we do the
INSERT , or else we do the UPDATE .
In each of these statements, you can see that we are dealing with records associated with both the
user (employee) id and the application ID. A user may have multiple two-factor codes in the cache for
multiple applications.
Changes to the Method to Distribute Two-Factor Codes
The changes in the OracleJavaSecure.distribute2Factor() method have mostly to do with calling and
receiving output parameters from p_get_emp_2fact_nos and p_update_2fact_cache . One other addition