Database Reference
In-Depth Information
Rebuild Two-Factor Cache Table for Multiple Applications
It is possible that a user will want to use more than one application at a time, and we will want them to
operate independently. These applications will have grants to diverse data and will use diverse sets of
Oracle instances. They may also be started at the client within the ten-minute, two-factor time to live off
one another.
To provide for the independent operation of these applications, we will add the
application_id
column as a part of the primary key for the
t_two_fact_cd_cache
table. See Listing 10-2. In this way, we
can create and assign a two-factor authentication code for each application that a user uses. This makes
obvious the fact that two-factor authentication for applications is already a step away from single sign
on. If your corporate computing environment enforces two-factor authentication for the initial logon
(e.g., Windows password and a Secure ID token), then there is no need for two-factor authentication to
applications. However, two-factor authentication for basic computer access, whether secure tokens,
biometric scanners, or electronic badges, still seems more prevalent in movies than in the corporate
setting. If you have a computer with biometric interface (fingerprint or facial recognition) or a secure
card slot, it is not two-factor authentication as a substitute for entering a password. Only when you use it
in addition to entering a password is it two-factor authentication.
So we will build two-factor authentication, independent for each application. We start by dropping
the previous
t_two_fact_cd_cache
table, and creating a new one with an
application_id
column.
Listing 10-2.
Redo Two-Factor Code Cache Table
DROP TABLE appsec.t_two_fact_cd_cache CASCADE CONSTRAINTS;
CREATE TABLE appsec.t_two_fact_cd_cache
(
employee_id NUMBER(6) NOT NULL,
application_id VARCHAR2(24 BYTE) NOT NULL,
two_factor_cd VARCHAR2(24 BYTE),
ip_address VARCHAR2(45 BYTE) DEFAULT SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ),
distrib_cd NUMBER(1),
cache_ts DATE DEFAULT SYSDATE
);
CREATE UNIQUE INDEX two_fact_cd_emp_id_pk ON appsec.t_two_fact_cd_cache
(employee_id,application_id);
We also recreate a view (code not shown) of this table for general references. There is an example
insert and some cache aging test code in the file
Chapter10/AppSec.sql
.
Update Two-Factor Code Functions to Use Application ID
Please refer to the full listing of the code in AppSec.sql file. We modify our existing
f_is_cur_cached_cd
function to take the
application_id
as an argument and to select from
v_two_fact_cd_cache
based on the
application_id
. We also update our existing
f_send_2_factor
function to take the
application_id
as an argument, and to pass it to the
distribute2Factor()
method.
