[Previous entry: "It's been a while..."]

07/01/2010: "Checking logons in Oracle in the absence of auditing"

So, it turns out SQL*Plus under 10g Release 1 executes "SELECT NULL FROM DUAL FOR UPDATE NOWAIT" when the user logs in and this creates a transaction which is in turn logged in the redologs which we can use to get logon times. What's more, a record of this transaction can also be found in the X$KTUQQRY in-memory table which we can query: SELECT DISTINCT LOGON_USER, COMMIT_TIMESTAMP FROM X$KTUQQRY. This will nicely show us who logged onto the system and when which, in the absence of auditing being enabled, is very useful during forensic investigations. Will need to investigate other versions of the Oracle client.