Single Sign-On - Expert Oracle and Java Security - page 153

allows us to call on views and procedures in the HR schema without prefixing each call with “HR.” The
code in Listing 8-6 does these things.

Listing 8-6. Prepare to Access HR Data

stmt.execute("CALL appsec.p_check_hrview_access()");
stmt.execute("ALTER SESSION SET CURRENT_SCHEMA =hr");
rs = stmt.executeQuery( "SELECT COUNT(*) FROM v_employees_public " );
Notice there is no “HR.” prefix on the view name, v_employees_public .

Update p_check_hrview_access Procedure, Non-Proxy Sessions
We will be making a couple sweeping changes to the appsec.p_check_hrview_access procedure: one to
handle regular connections, and one to handle proxy sessions. Once you decide which approach you will
implement, you may comment or remove one or the other blocks of code. In the body of
appsec.p_check_hrview_access , we will place this code, Listing 8-7 for non-proxy sessions.

Listing 8-7. Verify Non-Proxy Sessions

IF( ( SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) LIKE '192.168.%' OR
SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) = '127.0.0.1' )
AND TO_CHAR( SYSDATE, 'HH24' ) BETWEEN 7 AND 18
AND SYS_CONTEXT( 'USERENV', 'SESSION_USER' ) = 'APPUSR'
AND SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) = just_os_user )
THEN
-- DBMS_SESSION.SET_ROLE ('hrview_role');
EXECUTE IMMEDIATE ' SET ROLE hrview_role';
END IF;

 Note You can find this script in the file named Chapter8/AppSec.sql .

The first two tests in the if statement, regarding IP_ADDRESS and SYSDATE time constraints, are the
same as what we implemented in Chapter 2. The third test assures that the SESSION_USER is 'APPUSR';
that is, that the appusr user is connected to Oracle database. Prior to this point, we have limited the
execution privilege for appsec.p_check_hrview_access to appusr , but now with proxy sessions, we need
to permit any Oracle user to execute our procedure, and assure that they are connected as appusr after
the fact. We grant execute to PUBLIC (everybody) like this:
GRANT EXECUTE ON appsec.p_check_hrview_access TO PUBLIC;
Remember, the goal of all these checks is to eventually set the role to hrview_role , if everything
checks out. There are at least the following two ways to set the role:

Call to DBMS_SESSION.SET_ROLE.

•

Immediately execute the SET ROLE command. •