Oracle Tips and Tricks — David Fitzjarrell

August 10, 2018

Well, That Didn’t Work …

Filed under: General — dfitzjarrell @ 11:46

"There are no wrong roads to anywhere."
-- Norton Juster, The Phantom Tollbooth

Interesting behavior with the unified audit trail has been reported by Jeff Hunter and verified on at least one platforms, RedHat Enterprise Linux 6. While other ports of Oracle behave as expected (Windows and Oracle Enterprise Linux 7, to name two that I’ve tested) the problem platform fails to return data from V$SESSION because the AUDSID values apparently don’t correspond to SESSIONID values returned by SYS_CONTEXT() or in the UNIFIED_AUDIT_TRAIL table. Let’s take this ‘for a spin’.

The following query returns data for the current user session from Oracle databases running on Windows, Solaris 10 or OEL 7:


select sid, serial#, os_username, machine, program
from v$session, unified_audit_trail
where audsid = sessionid
and sessionid = (select sys_context('userenv','sessionid') from dual);

As an example let’s run this on a Windows version of Oracle:


SQL> select sid, serial#, os_username, machine, program
  2  from v$session, unified_audit_trail
  3  where audsid = sessionid
  4  and sessionid = (select sys_context('userenv','sessionid') from dual)
  5  /

       SID    SERIAL# OS_USERNAME        MACHINE           PROGRAM
---------- ---------- ------------------ ----------------- -------------------------
       229       8579 SPLEEZO\fnordwamp  SPLEEZ0\NOTYOURPC sqlplus.exe

SQL>

From OEL 7 similar results are produced, again for the currently connected session:


SQL> select sid, serial#, os_username, machine, program
  2  from v$session, unified_audit_trail
  3  where audsid = sessionid
  4  and sessionid = (select sys_context('userenv','sessionid') from dual)
  5  /

       SID    SERIAL# OS_USERNAM   MACHINE           PROGRAM
---------- ---------- ------------ ----------------- ----------------------------------
        12      33089 oracle       mydbserver        sqlplus@mydbserver (TNS V1-V3)

SQL>

This is the output (or lack thereof) reported by Jeff Hunter for RHEL 6:


SQL> select sid, serial#, os_username, machine, program
  2  from v$session, unified_audit_trail
  3  where audsid = sessionid
  4  and sessionid = (select sys_context('userenv','sessionid') from dual)
  5  /

no rows returned

SQL>

From the above tests and results it appears that Oracle 12.x doesn’t behave the same way on all platforms. The interesting part of this is that the values do appear to match when selected independently yet they don’t ‘match’ when the AUDSID and SESSIONID columns are joined for user sessions. I can’t believe that the RHEL 6 behavior is the ‘norm’ as audit records should be able to be linked to sessions so that activity can be tracked and recorded.

Oracle support provides no documents regarding this behavior so no work-around is provided, and this may be an issue to raise an SR for. All platforms should behave in the same way with unified auditing, otherwise it’s of no benefit to the DBA.

Sometimes that fork in the road is actually a spoon …

Advertisements

1 Comment »

  1. First thought, without investigation, could this be another case of a wayward null terminator? Can’t find the link where I read another example of this recently but that example was related to DBMS_SCHEDULER.
    DUMP() of AUDSID / SESSIONID on the platform in question would reveal if relevant.

    Comment by Dom Brooks — August 13, 2018 @ 10:05 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Blog at WordPress.com.

%d bloggers like this: