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

Accessing HR Tables from Application Security Procedures
We are going to continue to have our application security user, appsec , run all the security procedures. In
order to accomplish our two-factor authentication, she will need to read the EMPLOYEES table from HR , as
well as the tables that we just created. We grant her access as shown in Listing 9-11.
Listing 9-11. Grant Application Security User Access to See Our Views
GRANT SELECT ON hr.v_employees_public TO appsec;
GRANT SELECT ON hr.v_sms_carrier_host TO appsec;
GRANT SELECT ON hr.v_emp_mobile_nos TO appsec;
Create the Two-Factor Codes Cache Table
Now that we have defined the tables in HR that hold the addresses and numbers where we are going to
send our two-factor authentication codes, we need to consider how we will abide the interim between
sending the code and having the user enter it in our application. It may require several minutes for the
two-factor authentication code to traverse the Internet and the cell phone system to the user's phone.
We do not want to keep our connections open to Oracle database for the whole duration of this out-of-
band communication, so we need to think about how to store the two-factor codes for later comparison,
and how to assure that the same user to whom we issued and sent the two-factor code is entering it.
With our SSO process and the mapping between login users ( user_id ) and employee_id s (in the
emp_mobile_nos table), we can save a two-factor authentication code for the specific user who logged in.
At this point, we will allow one two-factor code per user (although we will modify that in the next
Chapter), so we index by employee_id .
We create a table to cache (retain) two-factor authentication codes for a time. This is shown in
Listing 9-12. We are creating this in the appsec schema, so connect to Oracle database as appsec , if not
already, and execute SET ROLE appsec_role to get the Application Security role in order to accomplish
this step.
Listing 9-12. Create a Table to Cache Two-Factor Authentication Codes
CREATE TABLE appsec.t_two_fact_cd_cache
(
employee_id NUMBER(6) NOT NULL,
two_factor_cd VARCHAR2(24 BYTE),
ip_address VARCHAR2(45 BYTE) DEFAULT SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ),
distrib_cd NUMBER(2),
cache_ts DATE DEFAULT SYSDATE
);
Now this is pure conjecture and creative imagination, but I believe that codes in the following
format will be sufficiently complex for security and easy to enter. A series of 12 random numeric
characters in groups of 4four separated by dashes, e.g., 1234-5678-9012. Perhaps you disagree and
believe another format is better, and that is okay—you have the PL/SQL code and can change it as
desired. Just note that this table is being created with a maximum two_factor_cd length of 24 characters.