ORACLE ERROR:ORA-28000: The account is locked
After installation of
Oracle10g/11g, there was a problem..couldnt login using SQL+. None of the
accounts(scott/tiger) worked . At last a quick web search gave the solution .
Here is what it is:
Once logged in as SYSDBA, you
need to unlock the SCOTT account
SQL> alter user scott account unlock;
SQL> grant connect, resource to scott;
SQL> alter user scott account unlock;
SQL> grant connect, resource to scott;
ORA-28001: the password
has expired
Applies to:
Oracle Database
Software Std & Enterprise
Edition - Version: 11.1.1.1.0 and later [Release: 11gR1 and
later ]Information in this document applies to any platform.
Reason:
In Oracle Database,
A remote DBAface this because password
have reached 180 Default limit for Password life time. The life of a password is defined as 180 days by default. DBA has to change this
limit to unlimited to solve this and to avid this in future. Before getting
this error DBA will also show ORA-28002: the password will expire
within n days
So lets deal with the issue ORA-28001
The users password were expired and in their grace period.Here is what it looked like:
SQL> select username,account_status,expiry_date
from dba_users;
USERNAME
ACCOUNT_STATUS
EXPIRY_DATE
------------------------------
------------------------ ------------
SELFLOAD
OPEN
PROD
OPEN
TECHNO
EXPIRED(GRACE)
11-JAN-14
SCOTT
EXPIRED & LOCKED
08-JUL-14
ORACLE_OCM
EXPIRED & LOCKED
15-AUG-09
XS$NULL
EXPIRED & LOCKED
15-AUG-13
MDDATA
EXPIRED & LOCKED
15-AUG-13
DIP
EXPIRED & LOCKED
15-AUG-09
APEX_PUBLIC_USER
EXPIRED & LOCKED
15-AUG-09
SPATIAL_CSW_ADMIN_USR LOCKED 15-AUG-09
So here is what I did:-
connect to sql like- sqlplus "/ as sysdba"
SQL> select password from sys.user$
where name='TECHNO';
PASSWORD
------------------------------
7A0F2B316C212D67
This gave me the hashed password for
the user TECHNO
Then again set the same password for user TECHNO:
SYS> alter user TECHNO identified by
values '7A0F2B316C212D67';
Now the account is open again and not expired or in grace period:
USERNAME
ACCOUNT_STATUS
EXPIRY_DATE
----------------------
-------------------
--------------
SELFLOAD OPEN
PROD OPEN
TECHNO OPEN 11-JAN-14
SCOTT EXPIRED & LOCKED 08-JUL-14
ORACLE_OCM EXPIRED & LOCKED 15-AUG-09
XS$NULL EXPIRED & LOCKED 15-AUG-13
MDDATA EXPIRED & LOCKED 15-AUG-13
DIP EXPIRED & LOCKED 15-AUG-09
APEX_PUBLIC_USER EXPIRED & LOCKED 15-AUG-09
SPATIAL_CSW_ADMIN_USR LOCKED 15-AUG-09
The above option may not work completely on the case "EXPIRED & LOCKED" it may remain LOCKED although it will no longer appear EXPIRED like:-
USERNAME ACCOUNT_STATUS EXPIRY_DATE
---------------------- ------------------- --------------
SCOTT LOCKED 08-JUL-14
So in that case unlock the user as illustrated below:-
connect to sql as- sqlplus "/ as sysdba"
Once logged in as SYSDBA, you need to unlock the SCOTT account
SQL> alter user scott account unlock;
SQL> grant connect, resource to scott;
SQL> alter user scott account unlock;
SQL> grant connect, resource to scott;
The other way to to open the expired password is to try to connect the
user using the command prompt, it will ask to you to change the password as
follows, It will ask you for the password provide the password,account
will be open.
SQL> conn techno/password
ERROR:
ORA-28001: the password has expired
Changing password for techno
New password:
Retype new password:
Password changed
Connected.
XS$NULL
Although by above methods one can unlock the users but the following user XS$NULL remains "EXPIRED & LOCKED"
Explanation for the same:-
Oracle
introduced a new user in version 11g called XS$NULL.
It is for Oracle’s internal use only and you should leave it alone. For this
reason it is locked and expired when it is created:
SQL> select
account_status from dba_users
2 where
username = 'XS$NULL';
ACCOUNT_STATUS
--------------------------------
EXPIRED & LOCKED
SQL>
You are
advised not to alter this account in any way, even if an auditor asks you to.
Oracle does not let you change its password:
SQL> conn / as
sysdba
Connected.
SQL> alter user
xs$null identified by new_pwd
2 /
alter user xs$null
identified by new_pwd
*
ERROR at line 1:
ORA-01031:
insufficient privileges
SQL>
Although you
can do so with the password command:
SQL> select
password from sys.user$
2 where
name = 'XS$NULL';
PASSWORD
------------------------------
DC4FCC8CB69A6733
SQL> password
xs$null
Changing password for
xs$null
New password:
Retype new password:
Password changed
SQL> select
password from sys.user$where
name = 'XS$NULL';
PASSWORD
------------------------------
C17AE3B0A14EA63F
SQL>
This is
because of bug 12822989 and you must not do this.
ERROR: ORA-28002: the password will expire within 6 days.
Lets see what we can do about resolving this issue.
You could simply just change the users password. In an ideal and secure world, this is exactly what you would do. Though this may not be practical, there may be a whole host of reasons for postponing the change.
In Oracle 11g, users are assigned to a profile, the default profile is named “DEFAULT”. This default profile comes configured with a maximum password lifetime of a 180 days.
[box type="info"] Just note that this document’s steps have been performed on Oracle 11g Release 11.2.0.3.0 running on Red Hat Enterprise Linux 6.3. The SQL syntax provided “should” work on all Oracle 11g platforms.[/box]
Step 1: Identify the Users Profile
Log into Oracle 11g using SQL*Plus tool as sysdba
Step 1: Identify the Users Profile
Log into Oracle 11g using SQL*Plus tool as sysdba
[oracle@oracle1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 20 12:18:08 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL>
Lets confirm that the user is running the default profile. Note you must capitalize the username.
SQL> SELECT profile FROM dba_users WHERE username = 'USER1';
PROFILE
------------------------------
DEFAULT
we can change the password ,
As you can see, the user has the profile of “DEFAULT” configured.
Step 2: View the Profile settings
Lets examine the settings of the profile
| SQL> select resource_name,resource_type, limit from dba_profiles 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 180 |
| PASSWORD_REUSE_TIME PASSWORD UNLIMITED |
| PASSWORD_REUSE_MAX PASSWORD UNLIMITED |
| PASSWORD_VERIFY_FUNCTION PASSWORD NULL |
| PASSWORD_LOCK_TIME PASSWORD 1 |
| PASSWORD_GRACE_TIME PASSWORD 7 |
| 16 rows selected |
As you can see the PASSWORD_LIFE_TIME is to 180 days, you may configure this however you want.
Step 3: Set PASSWORD_LIFE_TIME
In this example, we’re going to make the password life unlimited so that it never expires. This may not be the wisest thing to do in a production environment. However there may be circumstances that require a password change at a later date.
To make this change we simply need to run the following command:
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Profile altered.
Step 4: Re-Enter the Password
You may notice that even after setting the password expiry to unlimited you are still getting the “ERROR: ORA-28002: the password will expire” message. I’m guessing this is due to additional processes that Oracle does in the background for checking password age etc. So this forces us to “reset” the password to it’s current value to remove the error.
This is done via the following SQL
SQL> alter user USER1 identified by "password";
User altered.
Just note that the password must be double quoted and not single quoted.
…and that’s it, you should no longer receive the “ORA-28002: the password will expire” message for USER1 when logging in.
[box type="warning"] Just note that having never expiring passwords probably isn’t the best password policy to maintain. Oracle’s password expiration setting is a great way to remind users and admin’s to change their passwords regularly.[/box]
In the End:-
It should serve as a warning that when the account has a status of
EXPIRED & LOCKED after initial database creation it is much different than
if that account is altered to that status after an account has been assigned a
password and unlocked. Some tools, such as SQL*Plus will allow the user the
chance to change the password if the old password is properly supplied.
For
example:
1. We can easily expire the password for the ANONYMOUS
account
SQL> ALTER USER anonymous PASSWORD EXPIRE;
User altered.
And the status would clearly be:
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
--------------------- ----------------- --------- ---------
ANONYMOUS EXPIRED 11-SEP-09
2. The ANONYMOUS account would be prompted for a password
and allowed to connect after entering a new password:
SQL> connect anonymous/abc
ERROR:
ORA-28001: the password has expired
Changing password for anonymous
New password: xyz
Retype new password: xyz
Password changed
Connected.
By contrast, if you really
wanted to lock out a user from connecting in the future, regardless of password
expiration, you should LOCK the account. For example:
3. We can easily lock the ANONYMOUS account
SQL> ALTER USER anonymous ACCOUNT LOCK;
User altered.
And the status would clearly be:
USERNAME ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
--------------------- ----------------- --------- ---------
ANONYMOUS LOCKED 11-SEP-09 10-MAR-10
4. The ANONYMOUS account is totally locked out and unable to
connect:
SQL> connect anonymous/abc
ERROR:
ORA-28000: the account is locked
Expiring and locking user
accounts are one of the first steps to securing who can connect to an Oracle
database. The expiration of account passwords is not an effective lock-out
mechanism but should instead be used to encourage users to just change their
passwords over time. Locking an account though has the effect of truly locking
out an account so no one can use it to connect to the database. If you need to
disable an account this is the preferred method other than dropping it. Just be
warned that expiring an account leaves the prior password intact. Under this
condition if you were to lock the account and someone came up behind your work
and unlocked the account, the user could still log in by changing the password.
It is therefore suggested to alter the password before expiring and locking.
That way even if the lock were removed the user would have no way of ever
connecting again.
Thank You.
No comments:
Post a Comment