Database Reference
In-Depth Information
m_sms_carrier_url OUT hr.v_sms_carrier_host.sms_carrier_url%TYPE,
m_email OUT hr.v_employees_public.email%TYPE,
m_ip_address OUT v_two_fact_cd_cache.ip_address%TYPE,
m_cache_ts OUT VARCHAR2,
m_cache_addr OUT v_two_fact_cd_cache.ip_address%TYPE,
m_application_id
v_two_fact_cd_cache.application_id%TYPE,
m_err_no OUT NUMBER,
m_err_txt OUT VARCHAR2 )
IS BEGIN
m_err_no := 0;
SELECT e.employee_id, m.com_pager_no, m.sms_phone_no, s.sms_carrier_url,
e.email, SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ),
TO_CHAR( c.cache_ts, fmt_string ), c.ip_address
INTO m_employee_id, m_com_pager_no, m_sms_phone_no, m_sms_carrier_url,
m_email, m_ip_address, m_cache_ts, m_cache_addr
FROM hr.v_emp_mobile_nos m, hr.v_employees_public e,
hr.v_sms_carrier_host s, v_two_fact_cd_cache c
WHERE m.user_id = os_user
AND e.employee_id = m.employee_id
AND s.sms_carrier_cd (+)= m.sms_carrier_cd
AND c.employee_id (+)= m.employee_id
AND c.application_id (+)= m_application_id;
EXCEPTION
-- User must exist in HR.V_EMP_MOBILE_NOS to send 2Factor, even to email
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_get_emp_2fact_nos' );
END p_get_emp_2fact_nos;
The query in Listing 10-8's procedure is almost identical to the select query that we had in the
previous version of the
distribute2Factor()
method. The only difference is the addition of the last line:
AND c.application_id (+)= m_application_id
. With that addition, we are selecting from the two-factor
cache for this user, and only for the specific application ID. Again, the
(+)
symbol indicates an outer join,
so we will return the primary data (e.g., pager number), even if no two-factor code already exists in the
cache for this user and this application.
Stored Procedure to Update Two-Factor Code Cache
The second Oracle statement that we are moving out of Java as dynamic SQL into an Oracle stored
procedure was what we used in
distribute2factor()
for updating the two-factor cache. Our parameters
for this procedure are primarily
IN
parameters. We pass in the user ID and the application ID for which
the two-factor code was generated. We also pass in the two-factor code and the distribution code—a
numeric value indicating which routes were used for distribution of the two-factor code. Listing 10-9
shows this procedure. We will place
p_update_2fact_cache
in the
appsec_only_pkg
package.
