Database audit and failed login trace

1.  When database account keeps getting locked, below trigger is useful to find failed logins.

CREATE TABLE system.LOGIN_AUDIT
(
  LOGIN_TIME  DATE,
  MACHINE     VARCHAR2(64 BYTE),
  IP_ADDR     VARCHAR2(64 BYTE),
  OSUSER      VARCHAR2(30 BYTE),
  USERNAME    VARCHAR2(30 BYTE),
  PROGRAM     VARCHAR2(48 BYTE),
  MODULE      VARCHAR2(64 BYTE)
)
TABLESPACE users;

CREATE OR REPLACE TRIGGER system.LOGIN_AUDIT
AFTER SERVERERROR
ON DATABASE
DECLARE
  v_prog    login_audit.PROGRAM%TYPE;
  v_mod     login_audit.module%TYPE;
BEGIN
  -- Login failure
   IF (is_servererror(1017)) THEN
    SELECT PROGRAM, module INTO v_prog, v_mod  FROM v$session
     WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID') AND ROWNUM<2;

    INSERT INTO LOGIN_AUDIT (
               username
                ,machine
                ,ip_addr
                ,osuser
                ,login_time
                ,PROGRAM,
                MODULE
        )
    VALUES (
          SYS_CONTEXT('USERENV','SESSION_USER')
         ,SYS_CONTEXT('USERENV', 'HOST')
         ,SYS_CONTEXT('USERENV', 'IP_ADDRESS')
         ,SYS_CONTEXT('USERENV', 'OS_USER')
         ,SYSDATE
         ,v_prog
         ,v_mod
    );
  END IF;
END;
/

2.  Metalink note 352389.1 also gives a way to find the source of failed login attempts

3.  Database auditing 
To turn on audit on database level may not practically acceptable to users due to potential impact on database performance and a database downtime.

Auditing can be enabled by setting the AUDIT_TRAIL init parameter, which has the following allowed values.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

. none or false - Auditing is disabled. This is the default.
. db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
. db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
. xml - Auditing is enabled, with all audit records stored as XML format OS files.
. xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
. os - Auditing is enabled, with all audit records saved to OS files.

The AUDIT_SYS_OPERATIONS init parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail defined by AUDIT_FILE_DEST parameter. The audit on activities by a user with DBA privilege is not controled by AUDIT_SYS_OPERATIONS parameter.

The AUDIT_FILE_DEST init parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used (In addition, V$XML_AUDIT_TRAIL for xml format). (It seems to me that AUDIT_FILE_DEST holds audit files for activities of SYSDBA or SYSOPER. All other audit files go to adump directory).

To audit all operations by user JY, run below lines by SYS:

SQL> CONNECT sys/password AS SYSDBA

SQL> AUDIT ALL BY jy BY ACCESS;
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY jy BY ACCESS;
SQL> AUDIT EXECUTE PROCEDURE BY jy BY ACCESS;

These options audit all DDL and DML, along with some system events.

. DDL (CREATE, ALTER & DROP of objects)
. DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
. SYSTEM EVENTS (LOGON, LOGOFF etc.)

Without specifying the username JY, the audit trial will be on all users. You can see all enabled options from view DBA_STMT_AUDIT_OPTS:

SELECT user_name, audit_option, success, failure
FROM sys.dba_stmt_audit_opts;

You can get the list of views that hold the audit trail by below query:

SELECT view_name FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%' ORDER BY view_name;

The three main views are:

. DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
. DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
. DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.

The database audit trail must be deleted, or archived, on a regular basis to prevent the SYS.AUD$ table growing to an unnacceptable size. Only DBAs should have maintenance access to the audit trail. Auditing modifications of the data in the audit trail itself can be achieved using the following statement:

AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

Fine grained auditing (FGA) extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. For more details, check out DBMS_FGA package.