What SQL statements can be used to check if logfails are recorded into the Oracle audit trail ('SYS. (NETIQKB30510)

goal
What SQL statements can be used to check if logfails are recorded into the Oracle audit trail ('SYS.AUD$')?

fact
VigilEnt Password Manager for Oracle 6.x

fact
VigilEnt Security Agent for Oracle 1.x

fix

The Oracle "SYS.AUD$" table holds connection records. The expected "return codes" values are "0"(logins) and "1017"(logfails). Once a logfail record has been scanned the "Audit Scanner" can act upon the logfails if needed.  The following SQL statements can be used to check if records are being written to the Oracle audit trail:

• The following statement will list users and return codes for a specific day (today) found in the AUD$ table:

  select userid, returncode, count(*) from sys.aud$ where timestamp# like sysdate||'%' group by userid, returncode;

• The following statement will list ALL users and returncodes in the aud$ table:

  select userid, returncode, count(*) from sys.aud$ group by userid, returncode;

• The following statement will return users that have logfailed and been scanned by the "Audit Scanner" of VigilEnt Password Manager for Oracle:

select userid, returncode, count(*) from sys.aud$ where comment$text like '%SQLCQR%' group by userid, returncode;

note
Oracle must be configured to record both successful and unsuccessful log-in events in the audit table. If running the SQL statements above returns no rows, ensure that the Oracle instance has been configured for connection auditing. Enable connection auditing by setting the following in the init.ora file:

audit_trail = true

or

audit_trail = db

Connect auditing must also be enabled by executing the following SQL statement while logged-into Oracle as a privileged user:

AUDIT CONNECT;

Formerly known as NETIQKB30510