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
taazAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.