Who is locking your accounts (ORA-01017 and ORA-28000 errors) ?

Posted on , updated on by Yannick Jaquier
Oracle

Table of contents

Preamble

I have decided to write this blog post after second time I received question on how to know from where are coming connections that are locking an account in an Oracle database…

Do not smile, I have seen at least two situations where, after a password change, a batch job was initiating plenty of connection (with previous wrong password) and no one was able to know from where this batch job was running (!!).

As a reminder, with default profile in Oracle 11g, accounts are automatically locked 1 day (PASSWORD_LOCK_TIME) after 10 failed login attempt (FAILED_LOGIN_ATTEMPTS):

SQL> SET lines 200
SQL> SET pages 200
SQL> SELECT * FROM dba_profiles WHERE PROFILE='DEFAULT' ORDER BY resource_name;
 
PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED
DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED
DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED
DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD 10
DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD 7
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD 1
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED
DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED
 
16 ROWS selected.

Oracle client session will received 10 times ORA-01017: invalid username/password; logon denied error message and then ORA-28000: the account is locked error message (for one day and then back to ORA-01017 error message).

The final question is how to identify from where (client IP address/name) are coming those tentative connections… I have done my testing using Oracle 11.2.0.3 running on Oracle Linux Server release 6.3.

ORA-01017/ORA-28000 with AUDIT_TRAIL

The first and preferred solution is with Oracle standard auditing feature. Start by setting initialization parameter AUDIT_TRAIL to db and restart your Oracle database as it is static parameter.

Then activate network auditing with (as SYS):

SQL> AUDIT network BY ACCESS;
 
AUDIT succeeded.

With below query you get everything needed:

SELECT *
FROM dba_audit_session
ORDER BY sessionid DESC;

Returncode column contains Oracle error code and so different of 0 if logon/logoff issue. The invalid password is the error we are chasing:

[oracle@server1 ~]$ oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:

So if you find 1017 values in this column then we have found what we were looking for. For example with my test case where I intentionally specify a wrong password for my account:

SQL> SELECT username,userhost,returncode
     FROM dba_audit_session
     WHERE username='YJAQUIER'
     ORDER BY sessionid DESC;
 
USERNAME                       USERHOST             RETURNCODE
------------------------------ -------------------- ----------
YJAQUIER                       server1                    1017
YJAQUIER                       GVADT30596                    0
YJAQUIER                       server1                       0
YJAQUIER                       server1                       0
.
.
.

And if you insist, as explained, you get:

SQL> SELECT username, account_status,lock_date, PROFILE FROM dba_users WHERE username='YJAQUIER';
 
USERNAME                       ACCOUNT_STATUS                   LOCK_DATE            PROFILE
------------------------------ -------------------------------- -------------------- ------------------------------
YJAQUIER                       LOCKED(TIMED)                    23-nov-2012 10:30:37 DEFAULT

If you set AUDIT_TRAIL to db behave the size of SYS.AUD$ table as a small list of audits are already implemented by default:

SQL> SET lines 200
SQL> SET pages 200
SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS;
 
USER_NAME                      PROXY_NAME                     AUDIT_OPTION                             SUCCESS    FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
                                                              ALTER SYSTEM                             BY ACCESS  BY ACCESS
                                                              SYSTEM AUDIT                             BY ACCESS  BY ACCESS
                                                              CREATE SESSION                           BY ACCESS  BY ACCESS
                                                              CREATE USER                              BY ACCESS  BY ACCESS
                                                              ALTER USER                               BY ACCESS  BY ACCESS
                                                              DROP USER                                BY ACCESS  BY ACCESS
                                                              PUBLIC SYNONYM                           BY ACCESS  BY ACCESS
                                                              DATABASE LINK                            BY ACCESS  BY ACCESS
                                                              ROLE                                     BY ACCESS  BY ACCESS
                                                              PROFILE                                  BY ACCESS  BY ACCESS
                                                              CREATE ANY TABLE                         BY ACCESS  BY ACCESS
                                                              ALTER ANY TABLE                          BY ACCESS  BY ACCESS
                                                              DROP ANY TABLE                           BY ACCESS  BY ACCESS
                                                              CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS
                                                              GRANT ANY ROLE                           BY ACCESS  BY ACCESS
                                                              SYSTEM GRANT                             BY ACCESS  BY ACCESS
                                                              ALTER DATABASE                           BY ACCESS  BY ACCESS
                                                              CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS
                                                              ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS
                                                              DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS
                                                              ALTER PROFILE                            BY ACCESS  BY ACCESS
                                                              DROP PROFILE                             BY ACCESS  BY ACCESS
                                                              GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS
                                                              CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS
                                                              EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS
                                                              GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS
                                                              CREATE ANY JOB                           BY ACCESS  BY ACCESS
                                                              CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS

So you must put in place a purging policy for this table.

ORA-01017/ORA-28000 without AUDIT_TRAIL

The only drawback of the previous solution is that you have to restart the database. And maybe two times because after problem solved you would like to deactivate auditing. This is most probably not reliable solution on a production database so I have been looking for a better solution with no database reboot.

I initially thought of the AFTER LOGON trigger but you need to be logged-in and the BEFORE LOGON does not exits. Then at same documentation place I found the AFTER SERVERERROR trigger and decided to give it a try.

First I created a dummy table to log server error (columns inherited from dba_audit_session dictionary table):

CREATE TABLE sys.logon_trigger
(
USERNAME VARCHAR2(30),
USERHOST VARCHAR2(128),
TIMESTAMP DATE
);

Second I created below trigger:

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

Then third simulated a wrong password access with my account and issued:

SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
 
SESSION altered.
 
SQL> SET lines 200
SQL> col USERHOST FOR a30
SQL> SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;
 
USERNAME                       USERHOST                       TIMESTAMP
------------------------------ ------------------------------ --------------------
yjaquier                       ST\GVADT30596                  23-nov-2012 11:05:56

And that’s it !!

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

This entry was posted in Oracle and tagged . Bookmark the permalink.

68 thoughts on “Who is locking your accounts (ORA-01017 and ORA-28000 errors) ?”

  1. Warren says:

    Is the server “AFTER SERVERERROR ON DATABASE” works only for sys? I have no sys account, and I am trying to find the source/process/module of the lock. When I created the logon trigger with the “AFTER SERVERERROR ON DATABASE” it compiled successfully. However, when I test the account that is being lock intentionally entering the wrong password it does not insert a row to the log table.


    create or replace trigger testdwdev.logon_TRAL
    after servererror on database
    declare
    vUserId number;
    vUserName varchar2(40);
    vOSuserName varchar2(40);
    vMachine varchar2(40);
    vProgram varchar2(80);
    vModule varchar2(80);
    vSysTimeStamp timestamp;
    begin

    select sys_context('USERENV','CURRENT_USERID') userid,
    substr(user,1,15) username,
    sys_context('USERENN','OS_USER') osuser,
    sys_context('USERENV','HOST') machine,
    sys_context('USERENV','CLIENT_PROGRAM_NAME') program_name,
    sys_context('USERENV','MODULE') module_name,
    systimestamp systemtimestamp
    into vUserId, vUserName, vOSuserName, vMachine, vProgram, vModule, vSysTimeStamp
    from dual;

    insert into sqldba.logon_lines
    values
    (vUserId,
    vUserName,
    vOSuserName,
    vMachine,
    vProgram,
    vModule,
    'Login Failed Error '||Ora_server_error(1),
    vSysTimeStamp);
    end;
    /

    Thanks.

  2. Ravi says:

    Great Post…But in addition to this I am trying to find which module is causing the problem of account lock. How can I achieve that.

    I tried below but it doesn’t work..

    CREATE OR REPLACE TRIGGER sys.logon_trigger
    AFTER SERVERERROR ON DATABASE
    BEGIN
    IF (IS_SERVERERROR(1017)) THEN
    INSERT INTO logon_trigger VALUES(SYS_CONTEXT(‘USERENV’, ‘AUTHENTICATED_IDENTITY’), SYS_CONTEXT(‘USERENV’, ‘HOST’), SYSDATE, SYS_CONTEXT(‘USERENV’, ‘MODULE’));
    COMMIT;
    END IF;
    END;
    / 2 3 4 5 6 7 8 9

    Trigger created.

  8. doleske says:

    Thank you sir – I used the second method to quickly identify the culprits and resolve the situation. I appreciate your clearly written post.

  9. Eswar says:

    Good article. I have tried second this method in 11g database and it worked. Does Second method also work in 9i database?

    Though I have created trigger in 9i database, I couldn’t see anything in output.

    SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;

    no rows selected

    • Yannick Jaquier says:

      Thank you !

      IMHO nothing should forbid the trigger to work even in 9i…

      But honestly the best advice I can give you is to move out fast from this super-obsolete 9i release that you are still using…

  11. Simon says:

    Hi Yannick

    Great article.
    I have used your table and trigger and determined the culprit.

    However I know that the culprit is user redlive which locked the DB.

    However not sure what to do next?

    Please help

    Thanks
    Simon

  15. Dave Meador says:

    Very slick! Saved me in dealing with password changes and developers having no clue knowing what applications connect to what!

    Thank you!

  17. SZDBA says:

    How many seconds or milliseconds apart before Oracle actually locks an account based on the value set for FAILED_LOGIN_ATTEMPTS. I have this parameter set at 7 and I repeatedly see failed login attempts for a single user > 7 attempts and the account does not lock.

  18. pratik says:

    Hi Yannick,

    Nice Blog, Thank you for posting.!!

    Can we add more than one error code in trigger? if yes how

    I want to add 28000 and 1005 in below trigger and also want to add one more column in table like RETURNCODE.
    Kindly help in to this

    CREATE TABLE sys.logon_trigger
    (
    USERNAME VARCHAR2(30),
    USERHOST VARCHAR2(128),
    TIMESTAMP DATE
    );

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

    • Yannick Jaquier says:

      Hi Pratik,

      Thanks for nice comment.

      Not sure to get your point as it is pretty easy. You can add a servererror varchar2 column to your table and do a list of IF THEN and replace the insert by something like:

      IF (IS_SERVERERROR(1017)) THEN
INSERT INTO logon_trigger VALUES(SYS_CONTEXT(‘USERENV’, ‘AUTHENTICATED_IDENTITY’), SYS_CONTEXT(‘USERENV’, ‘HOST’), SYSDATE,'1017');
COMMIT;
END IF;

      Add as many if loop as you need to track all required error codes…

      Yannick.

  19. rashi says:

    my database login account details are correct but account is keep on locking with error (28000). I have reset the password and tried logging as well but same error

  21. Amir Mahmood says:

    Dear Yannick Hi,

    Indeed it is very informative and helpful I have a question when i tried the second option that is without audit i am confused with the below results

    SQL> SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;

    USERNAME USERHOST TIMESTAMP
    —————————— —————————— ———–
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15
    test testserver 12-OCT-15

    25 rows selected.

    SQL> SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME=’TEST’;

    ACCOUNT_STATUS
    ——————————–
    OPEN

    NOW my question is how come the account is open when it is returning the rows with SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;

    and why there are so many rows selected 25 does that mean this user get locked 25 times which raised the trigger to insert data in sys.logon_trigger table please advise.

    Thanks

    • Yannick Jaquier says:

      Dear Amir,

      If you have exactly followed the method I have described your 25 rows show that you had 25 unsuccessful connection tentative (IS_SERVERERROR(1017) in trigger). It also means your profile has a FAILED_LOGIN_ATTEMPTS greater or equal than 25.

      Thanks, Yannick.

  22. Jaspreet says:

    Hi Yannick

    Thank you for such an informative article, the 2nd part of capturing 1017 using trigger is really useful.
    We have auditing enabled in our environment but it is at os level(audit_trail=os). I tried searching for documents related to how to extract audit data when audit_trail=os but could not find anything concrete. Do you know some good documentation for it.

    Thank you

    • Yannick Jaquier says:

      Hi Jaspreet,

      Thanks for nice comment. Unfortunately no particular documentation to share on audit_trail = os. I prefer to have audit information in an Oracle table for easier select and filtering. I’m most probably not the only one as there are almost no reference of audit_trail = os on Internet. Have you tried with an external table ?

      Thanks, Yannick.

  23. David says:

    Thanks Yannick, very helpful, much more so than the other blog I’ve just read where the offered solution was to simply set the failed_login_attempts to unlimited!!!

  24. Lodh says:

    Cool & Nice one.
    Btw, I was wondering if we can identify the incomming sessions from listener logs – I hope not because for the listener connection is established between client & database and whatever authentication related errors is not just listener’s concerns – What’s your view ?

    • Yannick Jaquier says:

      Welcome sir !
      I agree with you, it is apparently not possible. I tried to play with highest listener log level (TRACE_LEVEL_listener_name=support) and only information in listener log file is * establish *. Whatever it is a successful or a failed connection…

  25. veera says:

    I have followed Without AUDIT_TRAIL option. Worked like a charm. Thanks for your time in helping us out !

    “There is no exercise better for the heart than reaching down and lifting people up.” ― John Holmes

  26. Peter says:

    Thank you for very useful article. But I have a question. Must be trigger created only in SYS schema? We don’t have access to SYS user because it is maintained by external DBA. But we have a user with DBA role so the values insereted in trigger are accessible with this user. Is there a special reason why trigger must be created within SYS? Thank you in advance.
    Peter

    • Yannick Jaquier says:

      Hello Peter,

      Thanks for nice comment and no the trigger does not need to be created by SYS if your account has enough rights. It is just a (bad) habit I have to create such powerful trigger with the most powerful account of the database…

      Yannick.

  27. Murali says:

    You may want to add error code “1005” too as users may attempt with null password and that could lock your account too. This happened in our DEV environment where I used similar approach to capture failed login attempts without audit trail

  29. ramkumar says:

    SQL> conn scott/tiger
    ERROR:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00942: table or view does not exist
    ORA-06512: at line 3
    ORA-01017: invalid username/password; logon denied
    after that no entry goes to table

  30. ramkumar says:

    i am getting this error
    SQL> conn scott/tiger
    ERROR:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00942: table or view does not exist
    ORA-06512: at line 3
    ORA-01017: invalid username/password; logon denied

    as per this doc so can anyone give me perfect trigger syntex for it

  31. ramkumar says:

    i have create table and trigger and when i login with wrong pwd i get this error
    SQL> conn scott/tggg
    ERROR:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00942: table or view does not exist
    ORA-06512: at line 3
    ORA-01017: invalid username/password; logon denied

    and there is no entry in logon_trigger table;

    so can you tell me specific parameter you set for it

  33. Cakeviv says:

    Hi,

    Thank you so much for posting this. Loved the second method. I keep facing this issue when I change the password for an account which the application accesses and it keeps getting locked. Was just wondering if you can provide a further insight on this below line..SYS_CONTEXT(‘USERENV’, ‘AUTHENTICATED_IDENTITY’), SYS_CONTEXT(‘USERENV’, ‘HOST’), can any more granular details be incorporated into this statement to go more deep into the auditing stuff?

    Thanks a lot..!!

  34. a3 says:

    the second method is more useful,because no reboot of db is needed..

    I meet the same situation of locking account,it’s quite noisy…

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>