1 2 Previous Next 27 Replies Latest reply: May 8, 2013 4:16 PM by jgarry RSS

    User account locked frequently

    user1764238
      I am using 11.2.0.3

      there is a user which is frequently getting locked. I have unlock it several times but after 10 sec the account gets locked again.

      anyone please help me in this. I changes the profile for that user to set FAILED_LOGIN_ATTEMTS to unlimited but no hopes.

      anyone can help me in this please.

      NAME PASSWORD CTIME PTIME LTIME EXPTIME ASTATUS
      ------------------------------ ------------------------------ ----------- ----------- ----------- ----------- ----------
      DCSDBA 3967570F423D3D69 25-JAN-2010 08-MAY-2013 08-MAY-2013 8


      also please let me know how can i get which are the machine trying to access this user "DCSDBA"
        • 1. Re: User account locked frequently
          swapnil kambli
          user1764238 wrote:
          I am using 11.2.0.3

          there is a user which is frequently getting locked. I have unlock it several times but after 10 sec the account gets locked again.
          probably there is a scripts running in the background with hardcoded old password

          >
          anyone please help me in this. I changes the profile for that user to set FAILED_LOGIN_ATTEMTS to unlimited but no hopes.

          anyone can help me in this please.

          NAME PASSWORD CTIME PTIME LTIME EXPTIME ASTATUS
          ------------------------------ ------------------------------ ----------- ----------- ----------- ----------- ----------
          DCSDBA 3967570F423D3D69 25-JAN-2010 08-MAY-2013 08-MAY-2013 8

          what is your password_expire_time
          • 2. Re: User account locked frequently
            999909
            Check if there is any change in your pwd recently. Some of the application users refer to a file where the pwd is stored and it tries to make the connection until it gets connected.. this could be the reason behind your frequent account locks.. or the change mark the user under a profile where all the pwd related verify functions are unlimited. this would ideally solve your problem.
            • 3. Re: User account locked frequently
              srsatya
              Hi,

              It dosent lock automatically, some how the password is entered wrongly, if not the user, just check if there are any scripts or application where the user password is hardcoded.
              • 4. Re: User account locked frequently
                jcagomes
                You can do the following statement and look at the audit trail.

                audit create session whenever not successful;
                • 5. Re: User account locked frequently
                  817202
                  Try This,

                  1. exp system/password owner=u1 file=myu1export.dmp log=myu1export.log

                  2. Drop user u1 cascadr;

                  3. Recreate user u1 with the same privs and default profile

                  4. First verify user didnt' get locked (should be able to find that out in 10 seconds).

                  5. If not then import

                  6. imp system/password fromuser=u1 touser=u1 file=myu1export.dmp log=myu1import.log statistics=NONE grants=N

                  7. Verify agan user u1 don't get locked up again


                  And Check Whether your problem is sorted out or not
                  • 6. Re: User account locked frequently
                    user1764238
                    Application guys unable to check which are the scripts trying to login to this user.

                    So cant getting help much. Any possible way to check from the database end?
                    • 7. Re: User account locked frequently
                      John Stegeman
                      Try This,
                      @vk82 :0 really? Drop the user? If you have any potatoes you need to peel do you use an atom bomb? How is dropping the user and recreating it going to stop whatever process is trying to log on as that user (that's a rhetorical question, it won't). Plus your import with grants=N, that's really going to screw things up, isn't it (that's a rhetorical question as well, it will)
                      Any possible way to check from the database end?
                      Turn on auditing as suggested. The user is failing to log on, so that's the only way.
                      Application guys unable to check which are the scripts trying to login to this user.
                      Get some "application guys" who can do their job properly
                      • 8. Re: User account locked frequently
                        user1764238
                        i have turned on audinting.

                        but from DCSDBA it is not showing anything. Also this account is locked again.

                        Please see the attachement:

                        SQL> select * from DBA_PRIV_AUDIT_OPTS;

                        USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
                        ------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
                        KF_DBA CULLIL01 ALTER SYSTEM BY ACCESS BY ACCESS
                        KF_DBA CULLIL01 AUDIT SYSTEM BY ACCESS BY ACCESS
                        KF_DBA CULLIL01 CREATE SESSION BY ACCESS BY ACCESS
                        KF_DBA GUPTAV01 ALTER SYSTEM BY ACCESS BY ACCESS
                        KF_DBA GUPTAV01 AUDIT SYSTEM BY ACCESS BY ACCESS
                        KF_DBA GUPTAV01 CREATE SESSION BY ACCESS BY ACCESS
                        KF_DBA DONGCH01 ALTER SYSTEM BY ACCESS BY ACCESS
                        KF_DBA DONGCH01 AUDIT SYSTEM BY ACCESS BY ACCESS
                        KF_DBA DONGCH01 CREATE SESSION BY ACCESS BY ACCESS
                        KF_DBA SYMINP02 ALTER SYSTEM BY ACCESS BY ACCESS
                        KF_DBA SYMINP02 AUDIT SYSTEM BY ACCESS BY ACCESS
                        KF_DBA SYMINP02 CREATE SESSION BY ACCESS BY ACCESS
                        KF_DBA PABBIK01 ALTER SYSTEM BY ACCESS BY ACCESS
                        KF_DBA PABBIK01 AUDIT SYSTEM BY ACCESS BY ACCESS
                        KF_DBA PABBIK01 CREATE SESSION BY ACCESS BY ACCESS
                        KF_DBA MAHATB01 ALTER SYSTEM BY ACCESS BY ACCESS
                        KF_DBA MAHATB01 AUDIT SYSTEM BY ACCESS BY ACCESS
                        KF_DBA MAHATB01 CREATE SESSION BY ACCESS BY ACCESS
                        KF_DBA BHATTI01 ALTER SYSTEM BY ACCESS BY ACCESS
                        KF_DBA BHATTI01 AUDIT SYSTEM BY ACCESS BY ACCESS
                        KF_DBA BHATTI01 CREATE SESSION BY ACCESS BY ACCESS
                        KF_DBA GODFRC01 ALTER SYSTEM BY ACCESS BY ACCESS
                        KF_DBA GODFRC01 AUDIT SYSTEM BY ACCESS BY ACCESS
                        KF_DBA GODFRC01 CREATE SESSION BY ACCESS BY ACCESS
                        KF_DBA CORDES01 ALTER SYSTEM BY ACCESS BY ACCESS
                        KF_DBA CORDES01 AUDIT SYSTEM BY ACCESS BY ACCESS
                        KF_DBA CORDES01 CREATE SESSION BY ACCESS BY ACCESS
                        KF_DBA MISTRP03 ALTER SYSTEM BY ACCESS BY ACCESS
                        KF_DBA MISTRP03 AUDIT SYSTEM BY ACCESS BY ACCESS
                        KF_DBA MISTRP03 CREATE SESSION BY ACCESS BY ACCESS
                        CREATE SESSION NOT SET BY ACCESS
                        • 9. Re: User account locked frequently
                          John Stegeman
                          I have no idea what "DCSDBA" is, nor whether you have enabled auditing correctly.

                          What statements did you run to "enable auditing"

                          What is the result of "show parameter audit" in SQL*Plus?

                          Have you read about the
                           tag in the FAQ to make your output readable on the forum?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                          • 10. Re: User account locked frequently
                            swapnil kambli
                            John Stegeman wrote:
                            If you have any potatoes you need to peel do you use an atom bomb?
                            ohhh man..lmao
                            • 11. Re: User account locked frequently
                              Osama_Mustafa
                              vk82 wrote:
                              Try This,

                              1. exp system/password owner=u1 file=myu1export.dmp log=myu1export.log

                              2. Drop user u1 cascadr;

                              3. Recreate user u1 with the same privs and default profile

                              4. First verify user didnt' get locked (should be able to find that out in 10 seconds).

                              5. If not then import

                              6. imp system/password fromuser=u1 touser=u1 file=myu1export.dmp log=myu1import.log statistics=NONE grants=N

                              7. Verify agan user u1 don't get locked up again


                              And Check Whether your problem is sorted out or not
                              What is this solution ?
                              • 12. Re: User account locked frequently
                                Osama_Mustafa
                                Check which profile that user uses ?


                                FAILED_LOGIN_ATTEMPTS for example ?
                                • 13. Re: User account locked frequently
                                  John Stegeman
                                  What is this solution ?
                                  It's a solution to the problem "our DBAs don't have enough work to do, so they need to break the application and generate more havoc for the general business community"
                                  • 14. Re: User account locked frequently
                                    user1764238
                                    SQL> show parameter audit

                                    NAME TYPE VALUE
                                    ------------------------------------ ----------- ------------------------------
                                    audit_file_dest string /wmsd1/oracle/admin/WMSD1/audi
                                    t
                                    audit_sys_operations boolean FALSE
                                    audit_syslog_level string
                                    audit_trail string FALSE


                                    "DCSDBA" is the user whihc is getting locked frequently. And this user is using "DEFAULT" profile.


                                    SQL> select PROFILE from dba_users where USERNAME='DCSDBA';

                                    PROFILE
                                    ------------------------------
                                    DEFAULT


                                    SQL> select * from dba_profiles where PROFILE='DEFAULT';

                                    PROFILE RESOURCE_NAME RESOURCE LIMIT
                                    ------------------------------ -------------------------------- -------- ----------------------------------------
                                    DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
                                    DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
                                    DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
                                    DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
                                    DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
                                    DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
                                    DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
                                    DEFAULT PRIVATE_SGA KERNEL UNLIMITED
                                    DEFAULT CONNECT_TIME KERNEL UNLIMITED
                                    DEFAULT IDLE_TIME KERNEL UNLIMITED
                                    DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
                                    DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
                                    DEFAULT CPU_PER_CALL KERNEL UNLIMITED
                                    DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
                                    DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
                                    DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
                                    1 2 Previous Next