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

prop.setProperty( OracleConnection. PROXY_USER_NAME , userName ) . Perhaps it would have been
better to delineate these players with the names “proxy host user” and “proxy client user,” or “proxy
connect user” and “proxy session user.”Despite the confusion, we will need to keep them straight. One
user initially connects to Oracle database with his password (called the proxy user), and another user
proxies through (connects through) that user. That second user owns the session that will do all the work
and we will see him in our audit logs.
Update p_check_hrview_access Procedure, Proxy Sessions
Our secure application role procedure, appsec.p_check_hrview_access must be updated again to verify
proxy sessions and grant the hrview_role , as appropriate. We have added the code shown in Listing 8-10
to the body of the procedure for that purpose (find this in the file AppSec.sql ).
Listing 8-10. Verify Proxy Session
IF( SYS_CONTEXT( 'USERENV', 'PROXY_USER' ) = 'APPUSR'
AND ( 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' ) =
SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )
AND SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' ) = just_os_user )
THEN
EXECUTE IMMEDIATE 'SET ROLE hrview_role';
END IF;
The first test within the if statement assures us that we are dealing with a proxy session and that the
proxy user is appusr . If you recall, we initially only allowed appusr to execute this procedure, but now we
have granted execute to PUBLIC . However, we are still permitting access only to appusr by assuring that
either the SESSION_USER is appusr (when only setting client identifier) or that the PROXY_USER is appusr (for
proxy sessions).
Next we have the standard tests for IP Address and SYSDATE time constraints. Then we have two
more tests that basically assure that these three identity traits are identical:
SESSION_USER = CLIENT_IDENTIFIER = OS_USER
The user proxying this session is the same as the OS user we got from NTSystem or UnixSystem and is
the same as the OS user that is presented by JDBC to Oracle Database. If all that is true, then we set the
secure application role, hrview_role .
Audit Proxy Sessions
We want to audit activity specific to proxy sessions. We can configure that with these commands:
AUDIT UPDATE TABLE, INSERT TABLE BY appusr ON BEHALF OF ANY;
-- This would be nice, but every java class gets audited with this command
--AUDIT EXECUTE PROCEDURE BY appusr ON BEHALF OF ANY;
NOAUDIT EXECUTE PROCEDURE BY appusr ON BEHALF OF ANY;
Because appusr is the proxy user, we can audit whatever he does on behalf of others. Here we are
auditing all update and insert queries. We decided against auditing all calls to execute procedures.