Re: Password expiration questions

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 13 Dec 2003 15:23:13 +1100
Message-ID: <3fda9432$0$18752$afc38c87@news.optusnet.com.au>

"Tim Kearsley" <tim.kearsley_at_milton-keynes.gov.uk> wrote in message news:725736ef.0312120038.1b027409_at_posting.google.com...
> Hi all,
>
> Oracle 8.1.7.3
> AIX 4.3.3
> RS6000 SP node
>
> A couple of questions:
>
> 1) If I create a profile which enforces password expiration after a
> given time and apply that profile to a user, when does the expiration
> period commence? Is it from the instant the profile is applied or is
> it from the next time the user logs on?

See if this answers the question for you:

SQL> select username, expiry_date from dba_users where username='SCOTT';

USERNAME                       EXPIRY_DA
------------------------------ ---------

SCOTT SQL> create profile expireme limit
  2 password_life_time 2;

SQL> alter user scott profile expireme;

User altered.

SQL> select username, expiry_date from dba_users where username='SCOTT';

USERNAME                       EXPIRY_DA
------------------------------ ---------
SCOTT                          08/DEC/03

So, the effect is immediate upon granting. Interesting, however, that my system thinks the password should expire on 8th December, when the current date is already the 13th!! Why it gets it wrong?

SQL> select to_date(startup_time,'DD-MM-YYYY') from v$instance;

TO_DATE(S

---

06/DEC/03 But as soon as I do this:

SQL> connect sys/xxxxxxx_at_win92 as sysdba Connected.
SQL> startup force
ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

Database mounted.
Database opened.

....then I can try this:

SQL> connect scott/tiger_at_win92
ERROR:
ORA-28001: the password has expired

Changing password for scott
New password:

... at which point the thing works as intended.

Regards
HJR

-- 
------------------------------------
Oracle insights at www.dizwell.com
------------------------------------

Received on Fri Dec 12 2003 - 22:23:13 CST