[EN] Who is locking my user ?

Use the following steps, if someone is locking an oracle database user by entering wrong password.
Wrong password attemp limit is determined by FAILED_LOGIN_ATTEMPTS profile parameter.
So check it first. In our case we assume that it's value is 3.

ORA-01017: invalid username/password; logon dedied
ORA-01017: invalid username/password; logon dedied
ORA-01017: invalid username/password; logon dedied
ORA-28000: the account is locked




---------------------------------------------------------------------------------------------------------

CREATE TABLE dba_maintain.account_lock_info
    (
    LOG_DATE DATE,
    INSTANCE_NUM    NUMBER(2),
    USERNAME VARCHAR2(30),
    ERR_MSG         VARCHAR2(100),
    USERHOST VARCHAR2(128),
    TERMINAL  VARCHAR2(50 BYTE),
    OS_USER         VARCHAR2(30 BYTE),
    CLIENT_INFO     VARCHAR2(100 BYTE),
    SYSEVENT        VARCHAR2(20 BYTE)
    );

CREATE OR REPLACE TRIGGER sys.account_lock_info
AFTER SERVERERROR ON DATABASE
BEGIN
  IF (IS_SERVERERROR(1017)) THEN
      INSERT INTO dba_maintain.account_lock_info VALUES(SYSDATE, ora_instance_num, SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), 'ORA-01017: invalid username/password; logon dedied', SYS_CONTEXT('USERENV', 'HOST'), SYS_CONTEXT ('USERENV', 'TERMINAL'), SYS_CONTEXT ('USERENV', 'OS_USER'), SYS_CONTEXT ('USERENV', 'CLIENT_INFO'), ora_sysevent);
    COMMIT;
  ELSE IF (IS_SERVERERROR(28000)) THEN
      INSERT INTO dba_maintain.account_lock_info VALUES(SYSDATE, ora_instance_num, SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), 'ORA-28000: the account is locked', SYS_CONTEXT('USERENV', 'HOST'), SYS_CONTEXT ('USERENV', 'TERMINAL'), SYS_CONTEXT ('USERENV', 'OS_USER'), SYS_CONTEXT ('USERENV', 'CLIENT_INFO'), ora_sysevent);
    COMMIT;
END IF;
END IF;
END;
/

select * from dba_maintain.account_lock_info ORDER BY LOG_DATE desc;