Database Reference
In-Depth Information
Notice also that we are sizing a column for
ip_address
to be 45 characters. I may have over-specified
it a bit, but while an IPv4 address is limited to 15 characters, an IPv6 address can be up to 39.
Furthermore, an IPv4 address, mapped to IPv6 can be represented by up to 45 characters.
We also have a field in the
t_two_factor_cd_cache
table for the timestamp,
cache_ts
. It is set to
default to
SYSDATE
, but that only has effect when we insert. When we update, we will “manually” set it to
SYSDATE
. Why do we need a cache timestamp? Here is some more creative imagination: you will find in
the code that we consider a two-factor authentication code to be good for 10 minutes. We will not send
another two-factor authentication code to the same user within that time period, and after 10 minutes,
the code we sent is no longer valid.
The last column that we need to explore in this table,
distrib_cd
, is a numeric value that indicates
how the two-factor code was distributed. Table 9-1 shows the potential values.
Table 9-1.
Two-Factor Code Distribution Values
Value
Distribution
0
Not Distributed
1
To Pager
2
To Cell Phone
3
To Pager and Cell Phone
4
To E-Mail
5
To Pager and E-Mail
6
To Cell Phone and E-Mail
7
To Pager, Cell Phone and E-Mail
I won't show it here, but on this table the
employee_id
is both a unique index and the primary key.
We also create the view,
v_two_fact_cd_cache
. Code for creating the indexes can be found in the file,
Chapter9/AppSec.sql
.
Testing Cache Aging
Let's insert a record and play with the aging algorithm we are going to use.
INSERT INTO appsec.v_two_fact_cd_cache
( employee_id ,two_factor_cd )
VALUES
(300,'FAKE');
Now, select the record to see that the timestamp is set.
SELECT * FROM appsec.v_two_fact_cd_cache;
