How to get rid of ORA-28002 message the password will expire within 6 days

oracleSecurity

I have a user getting an ORA-28002 indicating that the password will expire within six days. I ran the following:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

But when I try to log in as the user, the message is still there. Executing this:

select * from dba_profiles where RESOURCE_NAME LIKE 'PASSWORD_LIFE_TIME';

shows that the values was really changed to UNLIMITED.

Best Answer

The password has been marked as 'EXPIRED' or marked with an 'EXPIRY_DATE' in dba_users. You will have to change it. You can set it back to the same password. The easy way would be setting the password "by values". This usually bypasses history checking.

12:28:33 SQL> select * from dba_users where username = 'MYUSER1';

USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS                   LOCK_DATE          EXPIRY_DATE
-------------------------------- ------------------ ------------------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ ------------------
PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
MYUSER1                               338 66856982BE5CD23F
OPEN
USERS                          TEMP                           17-JAN-11
DEFAULT                        DEFAULT_CONSUMER_GROUP



Elapsed: 00:00:00.03
12:28:43 SQL> alter user myuser1 password expire;

User altered.

Elapsed: 00:00:00.00
12:29:11 SQL> select * from dba_users where username = 'MYUSER1';

USERNAME                          USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS                   LOCK_DATE          EXPIRY_DATE
-------------------------------- ------------------ ------------------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ ------------------
PROFILE                        INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
MYUSER1                               338 66856982BE5CD23F
EXPIRED                                             17-JAN-11
USERS                          TEMP                           17-JAN-11
DEFAULT                        DEFAULT_CONSUMER_GROUP



Elapsed: 00:00:00.03
12:29:14 SQL>

The format of this is "alter user USER identified by values 'hash from dba_users.password';"

[TEST] C:\>sqlplus system

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jan 17 12:18:16 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

new: showmode BOTH
12:18:17 SQL> prompt end of LOGIN.SQL
end of LOGIN.SQL
12:18:17 SQL> create user myuser1 identified by mypassword1;

User created.

Elapsed: 00:00:00.01
12:18:21 SQL> grant connect, resource to myuser1;

Grant succeeded.

Elapsed: 00:00:00.01
12:18:30 SQL> connect myuser1/mypassword1
Connected.
12:18:39 SQL> connect system
Enter password:
Connected.
12:18:51 SQL> alter user myuser1 password expire;

User altered.

Elapsed: 00:00:00.00
12:19:05 SQL> connect myuser1/mypassword1
ERROR:
ORA-28001: the password has expired


Changing password for myuser1
New password:
Retype new password:
Password changed
Connected.
12:19:16 SQL> connect myuser1/mypassword1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
12:19:21 SQL> connect system
Enter password:
Connected.
12:19:34 SQL> alter user myuser1 identified by mypassword1;

User altered.

Elapsed: 00:00:00.01
12:19:49 SQL> alter user myuser1 identified by mypassword1 password expire;

User altered.

Elapsed: 00:00:00.01
12:20:26 SQL> select username, password from dba_users where username = 'MYUSER1';

USERNAME                       PASSWORD
------------------------------ ------------------------------
MYUSER1                        66856982BE5CD23F

Elapsed: 00:00:00.01
12:20:36 SQL> alter user myuser1 identified by values '66856982BE5CD23F' ;

User altered.

Elapsed: 00:00:00.01
12:21:01 SQL> connect myuser1/mypassword1
Connected.
12:21:13 SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Related Solutions

Oracle11g password expiry issue

According to the Oracle documentation here (see heading Setting the PASSWORD_LIFE_TIME Profile Parameter to a Low Value) the account status is changed from OPEN to EXPIRED(GRACE) if the user is currently logged in when you change PASSWORD_LIFE_TIME to a low value. I guess you were logged in as system when you changed the PASSWORD_LIFE_TIME. Try logging in as CUSUSER and then changing it.

The following method will set a user's account status to EXPIRED(GRACE):

create profile EXPTESTPROF limit
failed_login_attempts             1
password_life_time                1/24/60/60
password_reuse_time               unlimited
password_reuse_max                1
password_verify_function          null
password_lock_time                1
password_grace_time               1;

create user EXPTEST identified by EXPTEST;

alter user EXPTEST profile EXPTESTPROF;

grant "CONNECT" to EXPTEST;

Then login as EXPTEST. You'll get the message that the password will expire within 1 day. If you check the ACCOUNT_STATUS now it should say EXPIRED(GRACE):

select ACCOUNT_STATUS
  from DBA_USERS
 where USERNAME = 'EXPTEST'

MySQL Startup Questions – PHP, Security, and phpMyAdmin

You may have been unaware of this, but everything and its grandmother done in MySQL is a question.

In light of this, the real question is : To mysqld, what is a Question ?

According to the MySQL Documentation on Questions:

The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

Anything you issue for the sake of running queries or just checking a status of some kind is a Question. Even checking how many questions in your session is itself a Question. Here is proof: (I will connect to MySQL 5.6.14 for Windows and ask for Questions in my session):

C:\Windows\system32>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 3     |
+---------------+-------+
1 row in set (0.11 sec)

mysql> show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 4     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 5     |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

This is normal to expect, so there is really nothing to worry about.

YOUR ORIGINAL QUESTIONS

Is someone hacking my database?

No, you are not being hacked.

Is PHPMyAdmin reporting inaccurate information?

PHPMyAdmin asks mysqld Questions all day long. The result is that it just runs up the global count (As seen from SHOW GLOBAL STATUS;). I wrote an answer to a post in ServerFault about 2.5 years ago entitled 1 billion mysql queries in 24 days? Can something be wrong?. I attributed the runaway stats to monitoring then, and I still strongly assert this now.

Best Answer

Related Solutions

Oracle11g password expiry issue

MySQL Startup Questions – PHP, Security, and phpMyAdmin

YOUR ORIGINAL QUESTIONS

Related Question