?
Solved

update user's password

Posted on 2008-11-03
12
Medium Priority
?
1,694 Views
Last Modified: 2013-12-19
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
Comment
Question by:taaz
  • 5
  • 4
  • 3
12 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 22868984
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
 

Author Comment

by:taaz
ID: 22869260
How to change the user's password or change the profile of the user?

thanks
0
 
LVL 35

Expert Comment

by:johnsone
ID: 22869319
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:taaz
ID: 22869331
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
 

Author Comment

by:taaz
ID: 22869351
Johnsone thnaks a lot but will this set up a new expiry date for a user's password?
0
 
LVL 35

Expert Comment

by:johnsone
ID: 22869424
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 22870060
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
 
LVL 35

Expert Comment

by:johnsone
ID: 22870342
Thanks for the confirmation.  I couldn't test it without changing the profile, which would reset the expire date and nullify the test.
0
 

Author Comment

by:taaz
ID: 22871197
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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 400 total points
ID: 22871279
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
 

Author Closing Comment

by:taaz
ID: 31512788
I really appreciate to both of you.
You guys are excellent.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22872311
glad we could help
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question