Database Reference
In-Depth Information
INSERT INTO hr.sms_carrier_host
( sms_carrier_cd, sms_carrier_url ) VALUES
( 'Sprint', 'messaging.sprintpcs.com' );
INSERT INTO hr.sms_carrier_host
( sms_carrier_cd, sms_carrier_url ) VALUES
( 'Verizon', 'vtext.com' );
Creating a Table of Employee Mobile Numbers
We already have a table in the HR schema that includes the e-mail address for personnel: the EMPLOYEES
table. However, we also need to store pager numbers and cell-phone numbers for employees, so we will
create a table to hold those data values, the emp_mobile_nos table. We need one more data value to tie all
this together: when a user log's in, and we identify her through our SSO process, we need a way to
associate the HR EMPLOYEES and emp_mobile_nos data with the user. We will add a column named user_id
to the emp_mobile_nos table to make that association. See Listing 9-7 for the creation command.
The HR.EMPLOYEES table includes a primary key index on EMPLOYEE_ID , which is an independent
numeric value: each employee that is hired is assigned the next numeric value, sequentially. Whatever
that value is, we will use to assign mobile numbers to that same employee. We are further associating
that numeric EMPLOYEE_ID with an SSO logged-in user, the user_id column.
Listing 9-7. Create a Table for Employee Mobile Phone Numbers
-- Adjust length of Pager_No and Phone_No as needed
CREATE TABLE hr.emp_mobile_nos
(
employee_id NUMBER (6) NOT NULL ,
user_id VARCHAR2(20 BYTE) NOT NULL ,
com_pager_no VARCHAR2(32 BYTE),
sms_phone_no VARCHAR2(32 BYTE),
sms_carrier_cd VARCHAR2(32 BYTE)
);
At this point, I will point out an important fact that you need to consider. As we go forward in our
security structures and procedures, we become dependent on the security that we established
previously. Our two-factor authentication depends on SSO; we need to identify the user who is
requesting two-factor authentication in order to send the two-factor code to the correct devices.
Additionally, we need to know what EMPLOYEE_ID is associated with a user_id . Two-factor
authentication can only succeed for employees who have an entry in the emp_mobile_nos table , or an
alternative. Consider the possibility that we might have added these columns directly to the
HR.EMPLOYEES table. It would have been correct to do so as far as data normalization standards dictate;
unless it can be proved that not all employees need access to our applications.
It may seem a bit backward to have the computer user_id field in a table of mobile phone numbers
instead of in the primary EMPLOYEES table, but we are adding this functionality onto the existing HR
structures, and we decided to do it all in one place, the emp_mobile_nos table.
 
Search WWH ::




Custom Search