Database Reference
In-Depth Information
'800.555.1212', SYSDATE, 'SA_REP', 5000, 0.20, 147, 80);
COMMIT;
SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE EMAIL='OSADMIN.MAIL'
;
INSERT INTO hr.v_emp_mobile_nos
( employee_id,
user_id
, com_pager_no, sms_phone_no, sms_carrier_cd )
VALUES ( (
SELECT EMPLOYEE_ID
FROM EMPLOYEES WHERE EMAIL='
OSADMIN.MAIL
'),
'
OSADMIN
', '12345', '8005551212', 'Verizon' );
COMMIT;
SELECT * FROM hr.v_emp_mobile_nos WHERE user_id = 'OSADMIN'
;
The significant aspects of this code are as follows:
1.
We create an Oracle user by the same name as our new OS user ID.
We grant the new user permission to
CONNECT THROUGH appusr
so that he can
select encrypted sensitive data from the
HR
schema.
2.
We insert a record for this user in the
HR.EMPLOYEES
table and get the next
sequential value from
EMPLOYEES_SEQ
as the new
EMPLOYEE_ID.
3.
4.
We can select the new user based on
EMAIL
address, which is a unique field.
5.
Using that selection (for simplicity here), we can insert a matching record into
hr.v_emp_mobile_nos
with the
user_id
field set to the new OS user ID.
6.
We finish by selecting the record we just created from
hr.v_emp_mobile_nos.
We require the records for this user in the
EMPLOYEES
table and the
hr.v_emp_mobile_nos
view so that
we can accomplish two-factor authentication for this user. Our administrative users will have to
accomplish both SSO and two-factor authentication, just like every other user.
Create an Administrative Role for Application Verification
So far, we have allowed any successful application user to both insert and update the list of connection
strings and store them in Oracle database for the application. Let's tighten up that process by creating an
Oracle role that is required in order to administer the lists of connection strings for applications. We will
use the application verification administrator,
appver_admin
role for this duty. As
SYS
, execute these
commands.
CREATE ROLE appver_admin NOT IDENTIFIED;
Still, any user can manage her own copy of the connection strings in the local instance of the client
application (we give users that freedom), but inserting, updating, or replicating the connection strings in
Oracle database will require preapproval. We will preapprove certain persons, via the user IDs they have
in the operating system that is SSO'd to Oracle database. To those user IDs, we will grant our new
administrative role. It is a default role for that person.
GRANT appver_admin TO osadmin;
