| password expiration date as per profile attached [message #605799] |
Tue, 14 January 2014 21:15  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Hi Team,
As per my project requiremnt, end user's password reset must be done by DBA only. Password can be expired as per PROFILE attached to that user or user may have forgotten the password, and he may approach me to reset the password at any point of time.
This is a test database. My role is DBA. For e.g. there is an end user - USER1, with DEFAULT profile attached and PASSWORD_LIFE_TIME = 90 days.
For e.g. I have created this user USER1, 3 months back ( say Oct/14/2013 ). As per the profile setting, password expires on Jan/14/2014, that is TODAY. If i reset the password now, WHEN THE PASSWORD WILL GET EXPIRE NEXT ? after 90 days from the date of RESET ? i.e. APR/14/2014 ? how PASSWORD_LIFE_TIME works ?
the reason is, I have written a job already to LOCK the user account when it reaches 90th day and I am resetting the password and unlocking that user a/c thro one .sql script. As of now, my code calculate expiry_date = user_creation_date + 90 days and if this expiry date is TODAY, then I am locking that account.
Please guide me.
Thank you
Kesavan
|
|
|
|
|
|
| Re: password expiration date as per profile attached [message #605801 is a reply to message #605800] |
Tue, 14 January 2014 23:03  |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Hi,
for example, I have created one user on 16/Oct/2013 with DEFAULT profile ( PASSWORD_LIFE_TIME = 90 DAYS ). Further today password expired. I am resetting the pasword today, 14/Jan/2014.
SQL> select to_date('16-OCT-13','DD-MON-YY')+90 FROM DUAL;
TO_DATE('
---------
14-JAN-14
Further, when this new password will get expire ? 14-APR-2014 ?
SQL> select to_date('14-JAN-14','DD-MON-YY')+90 FROM DUAL;
TO_DATE('
---------
14-APR-14
kesavan
|
|
|
|
|
|
|
|
| Re: password expiration date as per profile attached [message #605876 is a reply to message #605807] |
Wed, 15 January 2014 12:01 |
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
|
Senior Member |
|
|
Hi,
Yes, I am able to achieve what I need. we have EXPIRY_DATE available in DBA_USERS view ( and I am able to see LOCK_DATE also ) . I did not notice this previously. Hence i tried to capture password reset date & timestamp and then call it in my job. so, once password is changed, the date&time stamp is added to PASSWORD_LIFE_TIME ( 90 days ) and it is updated as EXPIRY_DATE. now I am able to use EXPIRY_DATE in my code so that it locks the user account whenever it reaches this EXPIRY_DATE.
Thank you all
Kesavan
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
