Enhancing Security - Expert Oracle and Java Security - page 324

For this new user, there will be a new entry in the appsec.v_two_fact_cd_cache view. This new entry
will be designated for the new employee_id , 304 in this example. Here is an example selection from
appsec.v_two_fact_cd_cache .
SQL> select * from appsec.v_two_fact_cd_cache;
EMPLOYEE_ID APPLICATION_ID TWO_FACTOR_CD IP_ADDRESS DISTRIB_CD CACHE_TS
----------- -------------- -------------- ---------- ---------- ---------
304 HRVIEW 2747-4367-3056 127.0.0.1 1 12-JUN-11
300 HRVIEW 3471-8557-5210 127.0.0.1 3 12-JUN-11
When we finally return to execute TestOracleJavaSecure with the correct two-factor authentication
code, we are able to print out many aspects regarding our proxy connection to Oracle for data queries,
connected through the appusr user, using the hrview_role . We also select and decrypt data from our
familiar p_select_employees_sensitive procedure.
Chapter11>java testojs.TestOracleJavaSecure 1234-5678-9012
Domain: ORGDOMAIN, Name: OSADMIN
Domain: ORGDOMAIN, Name: OSADMIN
osadmin
APPUSR
127.0.0.1
OSADMIN
OSADMIN
HRVIEW_ROLE
Oracle success 2)
100, Steven, King, SKING, 515.123.4567, 2003-06-17 00:00:00, AD_PRES, 24000, null, null, 90
Run as Non-Administrative User, OSUSER
We are going to test our limitation of application connection string updates to just users who have been
granted the appver_admin role. To do this, you need to log in as an OS user who corresponds to an Oracle
user without the appver_admin role, your equivalent of OSUSER in our examples. Run
TestOracleJavaSecure again. You will see that when the application calls the method
putAppConnections() an exception is thrown with regard to the appsec_admin_pkg package. This user
does not have privileges to execute the Oracle function, f_set_decrypt_conns that is called from that
method.
Chapter11>java testojs/TestOracleJavaSecure
Domain: ORGDOMAIN, Name: OSUSER
Please rerun with 2-Factor Auth Code!
java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00201: identifier 'APPSEC.APPSEC_ADMIN_PKG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
We want to demonstrate other, non-administrative functions using the non-administrative user. To
do that, edit TestOracleJavaSecure.java and comment the line calling the putAppConnections() method.
Then recompile and rerun the application. You will see the following successful results:
Chapter11>javac testojs/TestOracleJavaSecure.java
Chapter11>java testojs.TestOracleJavaSecure