Home » RDBMS Server » Security » User Password Expired (oracle, 11g. (11.2.0.1.0) , Linux (Red Hat 5.0))
User Password Expired [message #530840] Fri, 11 November 2011 00:58 Go to next message
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 #530845 is a reply to message #530840] Fri, 11 November 2011 01:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum, Pravin. Is the problem that you need to re-set the users' passwords? Or something else?
Re: User Password Expired [message #530850 is a reply to message #530840] Fri, 11 November 2011 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have not to know account passwords to get their data: a DBA (or anyone with SELECT ANY TABLE privilege) can read these data and a DBA (or anyone with EXP_FULL_DATABASE role) can export these tables and data.

You have not to know account passwords to reset them: a DbA (or anyone with ALTER USER privilege) can reset them.

Regards
Michel
Re: User Password Expired [message #530856 is a reply to message #530850] Fri, 11 November 2011 02:49 Go to previous messageGo to next message
pbardale7
Messages: 28
Registered: November 2011
Location: India
Junior Member
hi all, i wanted to recover these accounts.


USERNAME USER_ID PASSWORD ACCOUNT
------------------------------ ---------- ------------------------------ -------
MGMT_VIEW 74 OPEN
SYS 0 OPEN
SYSTEM 5 OPEN
EAGLE 92 OPEN
DBSNMP 30 EXPIRED
SYSMAN 72 EXPIRED
OUTLN 9 EXPIRED
FLOWS_FILES 75 EXPIRED
MDSYS 57 EXPIRED
ORDSYS 53 EXPIRED
EXFSYS 42 EXPIRED

USERNAME USER_ID PASSWORD ACCOUNT
------------------------------ ---------- ------------------------------ -------
WMSYS 32 EXPIRED
APPQOSSYS 31 EXPIRED
APEX_030200 78 EXPIRED
OWBSYS_AUDIT 83 EXPIRED
ORDDATA 54 EXPIRED
CTXSYS 43 EXPIRED
ANONYMOUS 46 EXPIRED
XDB 45 EXPIRED
ORDPLUGINS 55 EXPIRED
OWBSYS 79 EXPIRED
SI_INFORMTN_SCHEMA 56 EXPIRED



Thank you.

Regards Pravin
Re: User Password Expired [message #530858 is a reply to message #530856] Fri, 11 November 2011 02:52 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #530861 is a reply to message #530856] Fri, 11 November 2011 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
received in PM
Thank you michel for your reply,

but actually when i wanted to log in to the user's profile it is giving that password has been expired.


Just connect as SYSTEM and change the password of your expired account using ALTER USER.

Regards
Michel
Re: User Password Expired [message #530863 is a reply to message #530858] Fri, 11 November 2011 03:05 Go to previous messageGo to next message
pbardale7
Messages: 28
Registered: November 2011
Location: India
Junior Member
Thank you very much John for your fast reply.

I am new for oracle database,

actually john what happened is, when i fire

SQL> select * from dba_users;

then it gives the list of all users, here some account has been Expired, so can i get those account by resetting the passwords?
How?


thank you.
Re: User Password Expired [message #530868 is a reply to message #530863] Fri, 11 November 2011 03:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What do you mean by get those accounts?
What exactly are you trying to get?
Re: User Password Expired [message #530875 is a reply to message #530861] Fri, 11 November 2011 03:37 Go to previous messageGo to next message
pbardale7
Messages: 28
Registered: November 2011
Location: India
Junior Member
Thank you Michel,

i have to do alter user for all account separately right?
is there any way i can set same password for all user accounts, in one statements

Thank you.
Re: User Password Expired [message #530878 is a reply to message #530868] Fri, 11 November 2011 03:47 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 #530880 is a reply to message #530875] Fri, 11 November 2011 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i have to do alter user for all account separately right?

Right.

Quote:
is there any way i can set same password for all user accounts, in one statements

No but it is easy to create a script to do it.

select 'alter user '||username||' identified by michel;'
from dba_users
where account_status = 'EXPIRED';


And copy and paste the result in SQL*Plus window to execute it.

Regards
Michel
Re: User Password Expired [message #530920 is a reply to message #530880] Fri, 11 November 2011 06:09 Go to previous messageGo to next message
pbardale7
Messages: 28
Registered: November 2011
Location: India
Junior Member
thank you very much..

I tried your suggestion of script for alter user,

but it is working only for DBSNMP and SYSMAN, not for others.


thank you..


Regrds,
Pravin
Re: User Password Expired [message #530923 is a reply to message #530920] Fri, 11 November 2011 06:11 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does that mean? Any error? If so, which one?

If possible, post your SQL*Plus session.
Re: User Password Expired [message #530924 is a reply to message #530923] Fri, 11 November 2011 06:16 Go to previous messageGo to next message
pbardale7
Messages: 28
Registered: November 2011
Location: India
Junior Member

there was no error,

I have attached txt file having sql session
Re: User Password Expired [message #530926 is a reply to message #530924] Fri, 11 November 2011 06:23 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So only these two are "expired". Please, post the result of
select username, account_status from dba_users where account_status <> 'OPEN';

Re: User Password Expired [message #530927 is a reply to message #530926] Fri, 11 November 2011 06:31 Go to previous messageGo to next message
pbardale7
Messages: 28
Registered: November 2011
Location: India
Junior Member
result of

select username, account_status from dba_users where account_status <> 'OPEN';


is attached


thanks.
  • Attachment: new.txt
    (Size: 1.88KB, Downloaded 1909 times)
Re: User Password Expired [message #530930 is a reply to message #530927] Fri, 11 November 2011 06:38 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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 #530931 is a reply to message #530930] Fri, 11 November 2011 06:41 Go to previous messageGo to next message
pbardale7
Messages: 28
Registered: November 2011
Location: India
Junior Member
thank you very much for your proper guidance.

i am very happy now..

i got where i was wrong.....



thank you.


Regards,

Pravin
Re: User Password Expired [message #534153 is a reply to message #530840] Sat, 03 December 2011 05:47 Go to previous messageGo to next message
dbamanoj
Messages: 2
Registered: December 2011
Location: bhilai
Junior Member
If u have password file backup, replace it and try to login.

regards
dbamanoj

[Updated on: Sat, 03 December 2011 05:49]

Report message to a moderator

Re: User Password Expired [message #534160 is a reply to message #534153] Sat, 03 December 2011 08:58 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Set the EXCLUSIVE to REMOTE_LOGIN_PASSWORD
Next Topic: Password verifiy function while creating a profile
Goto Forum:
  


Current Time: Thu Mar 28 08:49:47 CDT 2024