| User Password Expired [message #530840] |
Fri, 11 November 2011 00:58  |
 |
pbardale7
Messages: 28
Registered: November 2011
Location: India
|
Junior Member |
|
|
HI EXPERTS,
i am using a oracle server. And all my users password has been expired, is there any way to recover those users without failing my data.
Thank you all in advance,
from
Pravin.
|
|
|
|
|
|
|
|
|
|
| Re: User Password Expired [message #530858 is a reply to message #530856] |
Fri, 11 November 2011 02:52  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I don't know what you mean by "recover", but you probably want to re-set the passwords. For example,
alter user sysman identified by Pa55w0rd;
connect sysman/Pa55w0rd
OK?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: User Password Expired [message #530878 is a reply to message #530868] |
Fri, 11 November 2011 03:47 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Let me try:
- there are certain users in your database whose passwords have expired
- you have no idea what these passwords were, so you can't connect
- lucky you, there IS a way to set these passwords - you need to connect as a privileged user and run ALTER USER statement - the one John provided some time ago.
- once you do that for all users you are interested in, you'll be able to connect to them, as if nothing happened (sort of; database links might not work, for example, but I don't think that it bothers you at the moment)
For example: there's user PBARDALE in my database:SQL> select username, account_status from dba_users where username = 'PBARDALE';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
PBARDALE EXPIRED
Now I'll change its password:SQL> alter user pbardale identified by november11;
User altered.
Finally, connect as PBARDALE:SQL> connect pbardale/november11@ora10
Connected.
I hope you got the idea; if not, say so.
Now I saw your last message; you ALTER USER one by one. You can set the same password for all of them. The easiest way would be to write SQL which would write SQL for you:
SQL> set pagesize 0
SQL> spool change_pwd.sql
SQL> select 'alter user ' || username || ' identified by november11;'
2 from dba_users where account_status = 'EXPIRED';
<you'd get list of statements here>
SQL> spool off;
SQL> @change_pwd |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: User Password Expired [message #530930 is a reply to message #530927] |
Fri, 11 November 2011 06:38 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
These are "EXPIRED & LOCKED", not just "EXPIRED" (most of them). That's why the WHERE clause you used ("WHERE account_status = 'EXPIRED'") didn't catch them. Obviously, you'd replace 'EXPIRED' with 'EXPIRED & LOCKED'.
However: are you sure you want to do that for all these users? I'm pretty much sure that, as long as you might want to unlock and use SCOTT or HR schema, you don't want to do that with APEX_030200 or SPATIAL_CSW_ADMIN_USER and similar.
Therefore, choose wisely and be careful. You might regret your actions, some day.
[Updated on: Fri, 11 November 2011 06:38] Report message to a moderator |
|
|
|
|
|
|
|
| Re: User Password Expired [message #534160 is a reply to message #534153] |
Sat, 03 December 2011 08:58 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Password file is irrelevant to the question.
Password file is for SYSDBA or SYSOPER accounts not for normal ones.
Password file does not contain any information regarding the expiration of passwords.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
