Oracle: Locked Users or Schemas

Dear Readers,

My name is Franz Devantier, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g) Security DBA.  I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks. 

Locked Users or Schemas

One of the users comes to tell you that they can't log in, because the user is locked. 

"Please fix, ASAP!".

Firstly why has this happened?

Lets see if the default profile is assigned to this user. 

SQL> select username, account_status, profile

          2 from dba_users

          3 where username = <user_name> ;

USERNAME                       ACCOUNT_STATUS                   PROFILE

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

<user_name>                         LOCKED                                 DEFAULT

If there is another profile, you will follow the same method to investigate.

SQL> set linesize 120 pagesize 25

SQL> select resource_name, resource_type, limit

           2  from dba_profiles

           3 where profile = 'DEFAULT';

RESOURCE_NAME                    RESOURCE LIMIT

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

COMPOSITE_LIMIT                  KERNEL   UNLIMITED

SESSIONS_PER_USER                KERNEL   UNLIMITED

CPU_PER_SESSION                  KERNEL   UNLIMITED

CPU_PER_CALL                     KERNEL   UNLIMITED

LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED

LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED

IDLE_TIME                        KERNEL   UNLIMITED

CONNECT_TIME                     KERNEL   UNLIMITED

PRIVATE_SGA                      KERNEL   UNLIMITED

FAILED_LOGIN_ATTEMPTS            PASSWORD 10

PASSWORD_LIFE_TIME               PASSWORD UNLIMITED

PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

PASSWORD_VERIFY_FUNCTION         PASSWORD NULL

PASSWORD_LOCK_TIME               PASSWORD UNLIMITED

PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

16 rows selected.

SQL>

You will notice that FAILED_LOGIN_ATTEMPTS            PASSWORD 10

This means that if there are 10 wrong passwords entered in a row without a correct entry, then the user will become locked.  If somebody enters the correct password, the FAILED_LOGIN_ATTEMPTS is reset again.  The default for FAILED_LOGIN_ATTEMPTS  is 10.

So now you need to do some research as to why there were ten attempts to login, with the wrong password.  It could be that because of the nature of the application, this may be expected.  In this case you may consider changing this setting to a higher number or even unlimited.  If it was a transient occurance, then you can leave it at the default, or the number it was set to.

SQL> alter profile default limit failed_login_attempts 25;

or

SQL> alter profile default limit failed_login_attempts unlimited;

If this was not expected from the application, then there may have been an attempt to compromise the security of your Oracle Database, by an un-authorized agent.  You will need to investigate this possibility.

However in the meantime the users, are probably keen to be able to connect again, especially if it is a production environment, so you will unlock the user account.  You don't need to reset the password in this case, you only need to unlock the user account.

SQL> alter user <user_name> account unlock;

You are done.

Franz Devantier,

Need a database health check?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report:  The report will give you all the information you need to start making a nice long-term residual income stream for yourself.