Thursday, January 23, 2014

ORA-28002

The PASSWORD_LIFE_TIME in the DEFAULT profile is 180 days in Oracle 11:
 
SQL> select limit from dba_profiles
  2  where profile = 'DEFAULT'
  3  and resource_name = 'PASSWORD_LIFE_TIME'
  4  /
 
LIMIT
----------------------------------------
180
 
SQL>
 
If you are not careful, a user’s password will expire if it is not changed soon enough. It will go into a grace period before it does. This has just happened to the user below:
 
SQL> l
  1  select account_status from dba_users
  2* where username = 'EBASE'
SQL> /
 
ACCOUNT_STATUS
--------------------------------
EXPIRED(GRACE)
 
SQL>
 
This isn’t too much of a problem if the user connects via SQL*Plus as Oracle just displays an appropriate warning before connecting during the grace period. This allows you to identify and rectify the issue before the password expires and locks the user out completely:
 
SQL> conn EBASE
Enter password:
ERROR:
ORA-28002: the password will expire within 6 days
 
Connected.
SQL>
 
However, many people, especially application users, connect via an application-specific front-end screen. These may have to work on a variety of hardware platforms and on other databases besides Oracle (e.g. Microsoft SQL Server or MySQL). Typically they do not handle error situations very well.

I came across one of these today. A user in Holland had connected to a database in the United Kingdom with the EBASE user above. The connection was successful and a corresponding row appeared in V$SESSION. However, the front-end screen saw the ORA-28002 warning, displayed a message saying that it could not connect to the database and allowed the user to go no further. The user sent the message to me but it did not make it clear what had caused the problem. The presence of entries for the user in V$SESSION did not seem to agree with the fact that the application said it had failed to connect. It was therefore quite some time before I worked out what had gone wrong.
 
You can reset a user's ACCOUNT_STATUS by changing its password. This particular application expects to always see the same password so I just reinstated the previous encrypted value as I do not want to show the actual value in a public blog post:
 
SQL> select password from sys.user$
  2  where name = 'EBASE'
  3  /
 
PASSWORD
------------------------------
82E46EECF1AFF14B
 
SQL> alter user ebase identified by values '82E46EECF1AFF14B'
  2  /
 
User altered.
 
SQL> select account_status from dba_users
  2  where username = 'EBASE'
  3  /
 
ACCOUNT_STATUS
--------------------------------
OPEN
 
SQL>
 
Then when the user logs in again, Oracle will not display an error:
 
SQL> conn EBASE
Enter password:
Connected.
SQL>
 
To stop this happening again, you may decide to change PASSWORD_LIFE_TIME to UNLIMITED:
 
SQL> conn / as sysdba
Connected.
SQL> l
  1  alter profile default
  2* limit password_life_time unlimited
SQL> /
 
Profile altered.
 
SQL> select limit from dba_profiles
  2  where profile = 'DEFAULT'
  3  and resource_name = 'PASSWORD_LIFE_TIME'
  4  /
 
LIMIT
----------------------------------------
UNLIMITED
 
SQL>

No comments: