Application Authorization - Expert Oracle and Java Security - page 220

Application Verification Logon Procedure
The procedure for our appver logon trigger, p_appver_logon is shown in Listing 10-14. It would have been
nice if we could have done a complete SSO check during the logon trigger, but alas, the proxy session
and the CLIENT_IDENTIFIER setting in the USERENV are not available at logon time. However, we can still
assure that the session user is appver (who else could it be?), and that our IP Address is acceptable. We
also call a function, f_is_user to assure that the os_user is also a database user. That may be the most
significant test since it is also what we will assure for our proxy login.
Listing 10-14. Application Verification Logon Procedure
CREATE OR REPLACE PROCEDURE appsec.p_appver_logon
AUTHID CURRENT_USER
AS
just_os_user VARCHAR2(40);
backslash_place NUMBER;
BEGIN
just_os_user := UPPER( SYS_CONTEXT( 'USERENV', 'OS_USER' ) );
backslash_place := INSTR( just_os_user, '\', -1 );
IF( backslash_place > 0 )
THEN
just_os_user := SUBSTR( just_os_user, backslash_place + 1 );
END IF;
-- For logon trigger - limited SSO , no PROXY_USER and no CLIENT_IDENTIFIER
IF( SYS_CONTEXT( 'USERENV', 'SESSION_USER' ) = 'APPVER'
AND( SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) LIKE '192.168.%' OR
SYS_CONTEXT( 'USERENV', 'IP_ADDRESS' ) = '127.0.0.1' )
-- Requirements must be applicable to all applications - time may not be
--AND TO_CHAR( SYSDATE, 'HH24' ) BETWEEN 7 AND 18
-- Assure that OS_USER is a database user
AND( appsec_only_pkg. f_is_user( just_os_user ) = 'Y' ) )
THEN
app_sec_pkg.p_log_error( 0, 'Success APPVER logon, ' || just_os_user );
ELSE
app_sec_pkg.p_log_error( 0, 'Problem getting APPVER logon, ' || just_os_user );
-- just_os_user := sys.f_get_off ;
-- This causes logon trigger to fail -- so not connected to Oracle
RAISE_APPLICATION_ERROR (-20003,'You are not allowed to connect to the database');
END IF;
END p_appver_logon;
/
We run this logon trigger procedure as AUTHID CURRENT_USER ; that is with invoker's rights. That's the
only way we can accurately gauge the user's identity—similar to the way it is for secure application role
procedures. So, we need to grant execute on this procedure to PUBLIC :
GRANT EXECUTE ON appsec.p_appver_logon TO PUBLIC;
Get Off Function
There is always a longing to have complete control. It would be wonderful to be able to spot a problem in
our logon trigger and immediately kill the session. Do you see the commented line saying just_os_user