1 2 Previous Next 15 Replies Latest reply: Oct 9, 2013 9:03 AM by OmbrettaV RSS

    Oracle user account is getting locked frequently

    Tushar Lapani

      Hi everyone!!!

      I am using Oracle 11g on Linux . I have user named "XXX" to whom I have assigned a DEFAULT profile. The Password parameters in DEFAULT profile are as follow.

       

      Resource Name                                      Resource                                 Limit

      FAILED_LOGIN_ATTEMPTS                    PASSWORD                            20

      PASSWORD_LIFE_TIME                        PASSWORD                            UNLIMITED

      PASSWORD_LOCK_TIME                      PASSWORD                           UNLIMITED

      PASSWORD_REUSE_TIME                   PASSWORD                            UNLIMITED

      PASSWORD_REUSE_MAX                   PASSWORD                             UNLIMITED

       

       

      I don't know why my user is getting locked continuously. Even i haven't reached Failed_login_attempts (20). Each time I require to unlock user account as SYS user and then I can connect as XXX user.

       

      And another thing that I want to know is when user account's status is set to LOCKED, EXPIRED, EXPIRED & LOCKED and LOCKED(TIME).

       

      Thanks & Regards

      Tushar Lapani

        • 1. Re: Oracle user account is getting locked frequently
          Vedant..

           

          And another thing that I want to know is when user account's status is set to LOCKED, EXPIRED, EXPIRED & LOCKED and LOCKED(TIME).

          Pls refer foll link:

          http://www.orafaq.com/forum/t/80676/2/

          • 2. Re: Oracle user account is getting locked frequently
            Vedant..

            TusharLapani wrote:

             

            Hi everyone!!!

            I am using Oracle 11g on Linux . I have user named "XXX" to whom I have assigned a DEFAULT profile. The Password parameters in DEFAULT profile are as follow.

             

            Resource Name                                      Resource                                 Limit

            FAILED_LOGIN_ATTEMPTS                    PASSWORD                            20

            PASSWORD_LIFE_TIME                        PASSWORD                            UNLIMITED

            PASSWORD_LOCK_TIME                      PASSWORD                           UNLIMITED

            PASSWORD_REUSE_TIME                   PASSWORD                            UNLIMITED

            PASSWORD_REUSE_MAX                   PASSWORD                             UNLIMITED

             

             

            I don't know why my user is getting locked continuously. Even i haven't reached Failed_login_attempts (20). Each time I require to unlock user account as SYS user and then I can connect as XXX user.

            Post the contents of the below query:

            select username, account_status from dba_users where username='YOUR_USERNAME';

            • 3. Re: Oracle user account is getting locked frequently
              Tushar Lapani

              SQL> select username ,account_status from dba_users where username='EDC_ACCU111';

               

               

              USERNAME                       ACCOUNT_STATUS

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

              EDC_ACCU111                   LOCKED

              • 4. Re: Oracle user account is getting locked frequently
                Vedant..

                TusharLapani wrote:

                 

                SQL> select username ,account_status from dba_users where username='EDC_ACCU111';

                 

                 

                USERNAME                       ACCOUNT_STATUS

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

                EDC_ACCU111                   LOCKED

                Since the account status is showing LOCKED, it means that the user EDU_ACCU111 is being explicitly locked, by the statement

                alter user EDU_ACCU111 account lock.

                 

                Kindly check who is executing the above statement by querying v$sqlarea and v$session

                 

                If the account is getting locked due to failed login attempts, then the account_status would show LOCKED(TIMED)

                 

                Message was edited by: Vedant..

                • 5. Re: Oracle user account is getting locked frequently
                  Aman....

                  Since the failed login attempts are mentioned, it may be possible that not you but someone else is trying to log in. Have you got the auditing enabled ? If not, try to increase the attempt count or make it unlimited. There is no default , automatic mechanism to get the account locked automatically.

                   

                  Aman....

                  • 6. Re: Oracle user account is getting locked frequently
                    jgarry

                    Some apps become weirdly obsessed with trying to login again when they can't.  Any clues in listener.log?

                    • 7. Re: Oracle user account is getting locked frequently
                      Tushar Lapani

                      Hi Vedant !!

                      I am DBA and no one else has right of SYSDBA or SYSOPER. So no user except me can fire lock statement .

                                                            alter user EDU_ACCU111 account lock

                      And another thing that I would like to know  what is the query to find out which user has issued LOCK statement.

                      • 8. Re: Oracle user account is getting locked frequently
                        Tushar Lapani

                        Hi Jgarry

                        I checked listener.log file nut i couldn't find any clue in  it.

                        • 9. Re: Oracle user account is getting locked frequently
                          tmbeetz

                          Hi,

                           

                          are there any cronjobs for your acount or a database link, because FAILED_LOGIN_ATTEMPTS  isn't unlimited.

                           

                          Best regards

                          Thomas

                          • 10. Re: Oracle user account is getting locked frequently
                            Vedant..

                             

                            And another thing that I would like to know  what is the query to find out which user has issued LOCK statement.

                            Querying the view v$sqlarea and v$sql may help.

                            • 11. Re: Oracle user account is getting locked frequently
                              vk82

                              Can you please mention the error what exactly are you getting

                              • 12. Re: Oracle user account is getting locked frequently
                                OmbrettaV

                                 

                                Hi,
                                can you tell me the exact db version?

                                 

                                As explained in MOS notes:
                                DBA_USERS.ACCOUNT_STATUS shows LOCKED after FAILED_LOGIN_ATTEMPTS Is Breached (Doc ID 284344.1)
                                How to Interpret the ACCOUNT_STATUS Column in DBA_USERS (Doc ID 260111.1)

                                 

                                Expected behaviour is:
                                1. Oracle release is <= 11.1.0.7.
                                DBA_USERS.ACCOUNT_STATUS = LOCKED(TIMED) whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

                                2. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME = unlimited:
                                DBA_USERS.ACCOUNT_STATUS = LOCKED whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

                                3. Oracle release is >= 11.2 and PASSWORD_LOCK_TIME = <some fix value>
                                DBA_USERS.ACCOUNT_STATUS = LOCKED(TIMED) whenever the number of failed login attempts is > FAILED_LOGIN_ATTEMPTS

                                Note
                                that 10.2.0.5 displays the same behavior as 11.2, because the fix that  changed the behavior in 11.2 was introduced in 10.2.0.5.

                                 

                                So I suggest you to follow MOS note
                                Finding the source of failed login attempts. (Doc ID 352389.1)
                                to find who locked the account.


                                Ombretta

                                • 13. Re: Oracle user account is getting locked frequently
                                  Tushar Lapani

                                  Hi Ombretta!!

                                  My Oracle Database version is 11.2.0.1.0

                                  And another thing that I want to tell you is FAILED_LOGIN_ATTEMPTS parameter  was prior set to unlimited . And then I set to it 20 just to know whether account is being locked due to Failed Login Attempts.

                                  An please provide me exact link for Doc ID 352389.1 from where i can find who locked the account

                                  • 14. Re: Oracle user account is getting locked frequently
                                    EdStevens

                                    TusharLapani wrote:

                                     

                                    Hi Ombretta!!

                                    My Oracle Database version is 11.2.0.1.0

                                    And another thing that I want to tell you is FAILED_LOGIN_ATTEMPTS parameter  was prior set to unlimited . And then I set to it 20 just to know whether account is being locked due to Failed Login Attempts.

                                    An please provide me exact link for Doc ID 352389.1 from where i can find who locked the account

                                     

                                    The referenced doc is a My Oracle Support doc.  If you have an MOS account, just log on and put the document number in the search field.  If you do not have an MOS account (paid service contract required), a direct link will do you no good.

                                    1 2 Previous Next