• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1697
  • Last Modified:

update user's password

how to update or set a new password  expiry_date for a user whose password has expired  in oracle
both using oem and through sqlplus.
regards
0
taaz
Asked:
taaz
  • 5
  • 4
  • 3
2 Solutions
 
sdstuberCommented:
you can't update the expiry_date directly.


you'll have to change the user's password or change the profile of the user
0
 
taazAuthor Commented:
How to change the user's password or change the profile of the user?

thanks
0
 
johnsoneSenior Oracle DBACommented:
What about changing the password to what it already is?

select password from dba_users where username = '<user>';
alter user <user> identified by values '<pass>';

Use the encrypted password returned by the first quey in the second statement to set the password.

I cannot test this on my system because we have a password validation function that requres the passwords be different.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
taazAuthor Commented:
can I just use

alter user abc password expire;
the thing is there is a user profile which is assigned to users and one of the user's password is expired
how to update that?
0
 
taazAuthor Commented:
Johnsone thnaks a lot but will this set up a new expiry date for a user's password?
0
 
johnsoneSenior Oracle DBACommented:
My guess is that it should.  I cannot confirm that because we have a password validation function in place that does not allow that statement because you are changing the password to the same thing it currently is.

Try it.  There is no down side.  The worst that can happen is you change the password to what it is already set to.
0
 
sdstuberCommented:
yes, setting the password to what it already is will reset the expiry date.

note,  in 11g,  dba_users does not have the password available anymore,  it is NULL by default
and, as already noted you may not be able to reuse the password.

Here's an example from 10g db, showing the expiry reset
SQL> CREATE PROFILE testprofile LIMIT
  2   SESSIONS_PER_USER UNLIMITED
  3    CPU_PER_SESSION UNLIMITED
  4    CPU_PER_CALL UNLIMITED
  5    CONNECT_TIME UNLIMITED
  6    IDLE_TIME UNLIMITED
  7    LOGICAL_READS_PER_SESSION UNLIMITED
  8    LOGICAL_READS_PER_CALL UNLIMITED
  9    COMPOSITE_LIMIT UNLIMITED
 10    PRIVATE_SGA UNLIMITED
 11    FAILED_LOGIN_ATTEMPTS UNLIMITED
 12    PASSWORD_LIFE_TIME 90
 13    PASSWORD_REUSE_TIME UNLIMITED
 14    PASSWORD_REUSE_MAX UNLIMITED
 15    PASSWORD_LOCK_TIME UNLIMITED
 16    PASSWORD_GRACE_TIME UNLIMITED
 17    PASSWORD_VERIFY_FUNCTION NULL;
 
Profile created.
 
SQL>   
SQL> CREATE USER sds
  2    IDENTIFIED BY testpassword
  3    PROFILE testprofile;
 
User created.
 
SQL> 
SQL> SELECT to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss')
  2    FROM dba_users
  3   WHERE username = 'SDS';
 
TO_CHAR(EXPIRY_DATE
-------------------
2009-02-01 14:27:00
 
SQL> 
SQL> exec dbms_lock.sleep(2);
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> ALTER USER sds IDENTIFIED BY VALUES 'ff5b28225c6e8f20';
 
User altered.
 
SQL> 
SQL> SELECT to_char(expiry_date,'yyyy-mm-dd hh24:mi:ss')
  2    FROM dba_users
  3   WHERE username = 'SDS';
 
TO_CHAR(EXPIRY_DATE
-------------------
2009-02-01 14:27:02
 
SQL> 
SQL> DROP USER sds;
 
User dropped.
 
SQL> 
SQL> DROP PROFILE testprofile;

Open in new window

0
 
johnsoneSenior Oracle DBACommented:
Thanks for the confirmation.  I couldn't test it without changing the profile, which would reset the expire date and nullify the test.
0
 
taazAuthor Commented:
So if a useer abc is unable to login because the passwd is expired.
To enable the password we will

reset the password like

  alter user abc password expire;

  and now when the next time user try to log in he would be asked to enter the new password.  is that right? would his pssword expire after 90 days?.

  or copy the original password and then change the password to new and then change it back to the original like

  select password,account_status from dba_users
where   username = 'abc';
 copy the password and save it

alter user abc identified by 'new_passwprd';

alter user abc identified by 'original_password';

thanks
0
 
johnsoneSenior Oracle DBACommented:
If the password is expired and the user knows the current password, then you do not need to do anything.  The next time the user logs in it will prompt the user to change it.  Be sure to tell them to use SQL*Plus, there are a lot of tools out there that do not handle expired passwords.

If the profile is set up correctly the new password with expire in 90 days.
0
 
taazAuthor Commented:
I really appreciate to both of you.
You guys are excellent.
0
 
sdstuberCommented:
glad we could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now