Home » RDBMS Server » Security » password expiration date as per profile attached (oracle 11.2.0.3 linux 6.4)
password expiration date as per profile attached [message #605799] Tue, 14 January 2014 21:15 Go to next message
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 #605800 is a reply to message #605799] Tue, 14 January 2014 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I don't understand what you want/desire from us.

SQL> select to_date('2013-10-14','YYYY-MM-DD')+90 FROM DUAL;

TO_DATE('
---------
12-JAN-14



which is NOT today.
Re: password expiration date as per profile attached [message #605801 is a reply to message #605800] Tue, 14 January 2014 23:03 Go to previous messageGo to next message
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
Thank you
kesavan
Re: password expiration date as per profile attached [message #605807 is a reply to message #605801] Wed, 15 January 2014 01:13 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Have a look at Ed Stevens' Exploring password lifetime and grace period, maybe you'll find it interesting.
Re: password expiration date as per profile attached [message #605809 is a reply to message #605799] Wed, 15 January 2014 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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.


For reference, the topic is there.

Re: password expiration date as per profile attached [message #605876 is a reply to message #605807] Wed, 15 January 2014 12:01 Go to previous message
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
Previous Topic: how to grant role using EXECUTE IMMEDIATE
Next Topic: How to clean up older records in AUD$ table
Goto Forum:
  


Current Time: Thu Mar 28 13:39:01 CDT 2024