How to Track Account Lockers with a Trigger

First, let’s create our own audit log table to track the info about the host and time of the attempt:

CREATE TABLE SYS.FALSE_LOGON_TRIGGER
(
  USERNAME   VARCHAR2(30 BYTE),
  USERHOST   VARCHAR2(128 BYTE),
  TIMESTAMP  DATE
);

Then create the trigger:

CREATE OR REPLACE TRIGGER SYS.FALSE_LOGON_TRIGGER
AFTER SERVERERROR ON DATABASE
BEGIN
  IF (IS_SERVERERROR(1017)) THEN
    INSERT INTO FALSE_LOGON_TRIGGER VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), SYS_CONTEXT('USERENV', 'HOST'), SYSDATE);
    COMMIT;
  END IF;
END;
/

Now, if you query the table SYS.FALSE_LOGON_TRIGGER you will find your usual suspects… Of course it is also possible to capture this info using the AUDIT mechanism, but this is easier and I want to be able to easily disable enable when required…