Re: Reseting after the first login

<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. Received on Tue Sep 19 2006 - 15:30:53 CDT