Two-Factor Authentication - Expert Oracle and Java Security - page 196

What Routes Are Available for the User?
The next step in the distribute2Factor() method is to query the HR data to see what devices the user
has: pagers, cell-phones, etc., to which we might send the two-factor authentication code. While we're at
it, in one fell swoop, we will get the cached two-factor code, if any, the timestamp when it was cached
and the IP Address from which it was requested.
Notice in our query (Listing 9-22) that we are using the Oracle TO_CHAR method to get the timestamp
and using the format string which we pass from Java.
Listing 9-22. Query for Distribution Routes Available
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery (
"SELECT m.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, '" + oraFmtSt + "' ) , c.ip_address " +
"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 = '" + osUser + "' " +
"AND e.employee_id = m.employee_id " +
"AND s.sms_carrier_cd (+)= m.sms_carrier_cd " +
"AND c.employee_id (+)= m.employee_id " );
if ( rs.next() ) {
String empID = rs.getString( 1 );
String pagerNo = rs.getString( 2 );
String smsNo = rs.getString( 3 );
String smsURL = rs.getString( 4 );
String eMail = rs.getString( 5 );
String ipAddress = rs.getString( 6 );
There is a bit of fault tolerance built into this query. Do you see the (+)= symbols? Those indicate
what is called an outer join . Notice we are getting the sms_carrier_url from the v_sms_carrier_host view
where the user's sms_carrier_cd matches what's in v_sms_carrier_host . Now, what if the user doesn't
have a cell phone or sms_carrier_cd ? If we left this query as a straight join ( s.sms_carrier_cd =
m.sms_carrier_cd ), we would not get a record for that user. However, by adding the outer join indicator,
(+) we are asking the query to return a result with the primary data even if this secondary data does not
exist. We do another outer join on the two-factor cache data, because the user may not already have a
two-factor authentication code in the cache.
I want to reiterate that if the user does not have an entry in the t_emp_mobile_nos table, he will not
be able to do two-factor authentication. He does not necessarily need to have a cell phone or pager, but
the association of SSO user ID ( user_id ) and employee_id requires that there be an entry in
t_emp_mobile_nos for the user. If the user's record in t_emp_mobile_nos has no SMS phone number or
pager number, then the distribute2Factor() method sends the message to the user's e-mail.