Home » SQL & PL/SQL » Client Tools » Password Expired (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0)
Password Expired [message #672550] Wed, 17 October 2018 14:55 Go to next message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
I am having problem creating a new account that when the user login first time it gives him the password expired. I want the user to be able to change the password at initial login.

This is what I did.

I created the account user1 originally as:

create user user1 identified by "lzs3!RSESY"
default tablespace users
temporary tablespace temp
account unlock 
password expire;

grant connect to user1;
GRANT CREATE SESSION TO user1;

-- ROLE
GRANT READ_ROLE TO user1;

Then user1 gets this error "status: Failure -Test failed: ORA-28001: the password has expired" at login. I changed the password using this command
alter user user1 identified by " bahPxD6!Mq" account unlock;

It works but did not prompts him to change password. I tried again to
alter user user1 identified by " x!rLrXoc2Y" account unlock password expire;

It says password expired.

So I checked
SELECT EXPIRY_DATE from dba_users where username = upper('user1');
EXPIRY_DATE
-----------
17-OCT-18

 
SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LOCK_TIME');
RESOURCE_NAME             LIMIT
------------------------- ----------
FAILED_LOGIN_ATTEMPTS     10
PASSWORD_LOCK_TIME        1

Please help.

Thank you.

[Updated on: Wed, 17 October 2018 14:56]

Report message to a moderator

Re: Password Expired [message #672551 is a reply to message #672550] Wed, 17 October 2018 15:09 Go to previous messageGo to next message
Michel Cadot
Messages: 66725
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to use a client program that handles ORA-28001 return code.
The problem is NOT with Oracle, it is with your client program.
Test it with SQL*Plus and you will see it works:
SQL> create user user1 identified by "lzs3!RSESY" account unlock
  2  password expire;

User created.

SQL> GRANT CREATE SESSION TO user1;

Grant succeeded.

SQL> conn user1/"lzs3!RSESY"

ERROR:
ORA-28001: the password has expired


Changing password for user1
New password:
Note: Remove and forget for ever "grant connect to ".

Re: Password Expired [message #672571 is a reply to message #672551] Thu, 18 October 2018 07:14 Go to previous messageGo to next message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
thanks so much.
Re: Password Expired [message #672573 is a reply to message #672550] Thu, 18 October 2018 09:24 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
wtolentino wrote on Wed, 17 October 2018 14:55
I am having problem creating a new account that when the user login first time it gives him the password expired. I want the user to be able to change the password at initial login.

This is what I did.

I created the account user1 originally as:

create user user1 identified by "lzs3!RSESY"
default tablespace users
temporary tablespace temp
account unlock 
password expire;

grant connect to user1;
GRANT CREATE SESSION TO user1;

-- ROLE
GRANT READ_ROLE TO user1;

Then user1 gets this error "status: Failure -Test failed: ORA-28001: the password has expired" at login. I changed the password using this command
alter user user1 identified by " bahPxD6!Mq" account unlock;

As an aside, I would never create a password with a space in it.
Re: Password Expired [message #672662 is a reply to message #672573] Mon, 22 October 2018 09:41 Go to previous messageGo to next message
wtolentino
Messages: 284
Registered: March 2005
Senior Member
Quote:

As an aside, I would never create a password with a space in it.
thanks for pointing that out. i think that was a typo error.
Re: Password Expired [message #672663 is a reply to message #672662] Mon, 22 October 2018 09:49 Go to previous message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Additionally, don't create a password with an "@" sign in. Oracle will interpret it as a connect string.
Previous Topic: Which client version is used?
Next Topic: SP2-0667: Message file sp1<lang>.msb not found
Goto Forum:
  


Current Time: Thu Dec 12 15:42:11 CST 2019