Re: Reseting after the first login

Matthias Hoys wrote:
> <fitzjarrell_at_cox.net> wrote in message
> news:1158693050.731845.56850_at_k70g2000cwa.googlegroups.com...
> >
> > muddu wrote:
> >> Hello,
> >>
> >> I created a list of users having create session privilege. But i want
> >> to know have the password required to be reset after the first login.
> >>
> >> Thanks.
> >
> > You cannot. You CAN create a user with an expired password, however,
> > even in 10gR2, the user cannot reset his/her own password:
> >
> > SQL> create user blabbo identified by norg default tablespace users
> > temporary tablespace temp quota unlimited on users password expire;
> >
> > User created.
> >
> > SQL> grant connect to blabbo;
> >
> > Grant succeeded.
> >
> > SQL> connect blabbo
> > Enter password:
> > ERROR:
> > ORA-00604: error occurred at recursive SQL level 1
> > ORA-06502: PL/SQL: numeric or value error
> > ORA-06512: at line 14
> > ORA-28001: the password has expired
> >
> >
> > Warning: You are no longer connected to ORACLE.
> > SQL> password blabbo
> > Changing password for blabbo
> > New password:
> > Retype new password:
> > ERROR:
> > ORA-00988: missing or invalid password(s)
> >
> >
> > Password unchanged
> > SQL>
> >
> > Setting a password grace time allows for a minimum of 1 days grace, but
> > that doesn't give you what you want, either, as the user still cannot
> > change the password:
> >
> > SQL> CREATE PROFILE prof LIMIT
> > 2 FAILED_LOGIN_ATTEMPTS 4
> > 3 PASSWORD_LOCK_TIME 30
> > 4 PASSWORD_LIFE_TIME 90
> > 5 PASSWORD_GRACE_TIME 1;
> >
> > Profile created.
> >
> > SQL> create user blabbo identified by norg default tablespace users
> > temporary tablespace temp profile prof;
> >
> > User created.
> >
> > SQL> grant connect to blabbo
> > 2 /
> >
> > Grant succeeded.
> >
> > SQL> alter user blabbo password expire;
> >
> > User altered.
> >
> > SQL> connect blabbo
> > Enter password:
> > ERROR:
> > ORA-00604: error occurred at recursive SQL level 1
> > ORA-06502: PL/SQL: numeric or value error
> > ORA-06512: at line 14
> > ORA-28001: the password has expired
> >
> >
> > Warning: You are no longer connected to ORACLE.
> > SQL> password blabbo
> > Changing password for blabbo
> > New password:
> > Retype new password:
> > ERROR:
> > ORA-00988: missing or invalid password(s)
> >
> >
> > Password unchanged
> > SQL>
> >
> > As this is not what you want (you want the user to be able to change
> > his/her password, not have the DBA change it) what you want is, I
> > believe, impossible.
> >
> >
> > David Fitzjarrell
> >
>
> This is from the Oracle 10g R2 documentation for ALTER USER (sorry, no
> system to test it on right now) :
>
> The following statement causes user sidney's password to expire:
>
> ALTER USER sidney PASSWORD EXPIRE;If you cause a database user's password to
> expire with PASSWORD EXPIRE, then the user (or the DBA) must change the
> password before attempting to log in to the database following the
> expiration. However, tools such as SQL*Plus allow the user to change the
> password on the first attempted login following the expiration.

A trigger to log server errors was preventing the mechanism from working properly; dropping that trigger provided the same output Andy just provided:

SQL> connect blabbo
Enter password:
ERROR:
ORA-28001: the password has expired

Changing password for blabbo
New password:
Retype new password:
Password changed
Connected.
SQL> I retract my original statement that it cannot be done, as it most certainly can.

David Fitzjarrell Received on Tue Sep 19 2006 - 15:35:48 CDT