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

Both are valid, but we will continue to use the second form. It is more generic, and we will use the
EXECUTE IMMEDIATE syntax in additional situations.
Assure Client Identifier and OS_USER
The fourth test in our procedure, shown Listing 8-7, assures that the OS user identity that we are passing
to Oracle database as the client identifier is equal to the OS_USER connection trait that JDBC passes to the
database. This is merely a second check for us to assure that the client identifier set by the application,
which represents the OS user identity, is the same as the OS user identity sensed by the Oracle Database.
We assure that the application has not set client identity to something other than the OS user identity.
We get the OS user that was sensed by Oracle database, just_os_user through the JDBC client using
the code in Listing 8-8. Really, it is just another session context environment setting; however, we need
to massage the value.
Listing 8-8. Getting the OS User Sensed by Oracle
just_os_user VARCHAR2(40); -- Windows users are 20, allow 20 for domain
backslash_place NUMBER;
BEGIN
-- Upper case OS_USER and discard prepended domain name, if exists
just_os_user := UPPER ( SYS_CONTEXT( 'USERENV' , ' OS_USER ' ) );
-- Back slash is not an escape character in this context
-- Negative 1 indicates count left from the right end, get last backslash
backslash_place := INSTR ( just_os_user, '\', -1 );
IF( backslash_place > 0 )
THEN
just_os_user := SUBSTR ( just_os_user, backslash_place + 1 );
END IF;
Note that we need to upper-case the OS_USER sent by JDBC to Oracle Database using the UPPER
function so that we can match by case the uppercase user identity we set in client identifier. In some
cases, the domain name is prepended on the OS_USER with a backslash separator like this:
ORGDOMAIN\OSUSER . We use the INSTR (in string) function to find the place of the backslash, if any.
Then we remove the domain name and backslash using the SUBSTR (substring) function. Looking back at
Listing 8-7, it is this massaged OS_USER , just_os_user that we compare to the value we set in the client
identifier.
Testing that all these user identity traits are identical doesn't really buy us a lot of additional
security, but sets another hurdle to trip up would-be hackers. You might be surprised at how often that
one extra hurdle is all you needed to prevent a break-in, and how often a simple added hurdle is sold as a
commercial security solution.
Audit Activity with Client Identifier Set
The following query will show you audit trail entries for connections where the client identifier is set.
You may not have any just yet, but we will see these connections after our tests.
SELECT * FROM sys.dba_audit_trail
WHERE client_id IS NOT NULL
ORDER BY TIMESTAMP DESC;