Re: Reseting after the first login

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 Received on Tue Sep 19 2006 - 14:10:50 CDT