Database Reference
In-Depth Information
Execute the following command a couple times. It will show the time elapsed since the timestamp
was set, and it should count up as you run it.
SELECT (SYSDATE-cache_ts)*24*60
FROM appsec.v_two_fact_cd_cache WHERE employee_id=300;
That command uses date arithmetic like what we've seen before. We subtract the
cache_ts
from
SYSDATE
. Normally what you see when adding and subtracting from
SYSDATE
is some number of days.
Always behind the scene is more precision, which is expressed in fractions of days. In a 10-minute span,
there is only a small fraction of a day. We multiply it by 24 to get the fraction of an hour. And we multiply
it by 60 to get the number of minutes.
Verifying Current Cached Two-Factor Pass Code
A specific user (determined by SSO) will hand a two-factor authentication code to this procedure. What
questions do we need to ask about the code to determine if it is acceptable? First we ask if there is an
existing code for the user. Second we ask if the existing code was requested from the same address that
the current user is using. And third we ask if the existing code is less than 10 minutes old. We ask all
those questions in the
SELECT
query that we have in the function
f_is_cur_cached_cd
, shown in Listing 9-
13. If a code that meets those requirements exists, it is returned in the
cached_two_factor_cd
variable.
Listing 9-13.
Test User-Entered Two-Factor Code Against Cached Version,
f_is_cur_cached_cd
CREATE OR REPLACE FUNCTION appsec.f_is_cur_cached_cd( just_os_user VARCHAR2,
two_factor_cd t_two_fact_cd_cache.two_factor_cd%TYPE )
RETURN VARCHAR2
AS
cache_timeout_mins NUMBER :=
10
;
return_char VARCHAR2(1) := 'N'
;
cached_two_factor_cd v_two_fact_cd_cache.two_factor_cd%TYPE;
BEGIN
SELECT
c.two_factor_cd
INTO cached_two_factor_cd
FROM v_two_fact_cd_cache c, hr.v_emp_mobile_nos m
WHERE m.employee_id = c.employee_id
AND m.user_id = just_os_user
AND
c.ip_address
= SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' )
AND ( SYSDATE - c.cache_ts )*24*60
< cache_timeout_mins
;
IF cached_two_factor_cd = two_factor_cd
THEN
return_char := 'Y'
;
END IF;
RETURN return_char;
END f_is_cur_cached_cd;
/
