how to force user to change his/her password on login? 1

[lhugh]

How do I force my user to change his/her password when the user login?

 

[lewisp]

What application are your users logging on to? SQLPlus, Forms etc?

 

[lhugh]

The application that we use mostly is SQLPlus

All my team members do not change their passwords. I want to force them to change it

Thanks

 

[Corran007]

ALTER USER USERNAME_GOES_HERE PASSWORD EXPIRE;

try that

 

[SantaMufasa]

LHugh,

To force password discipline in Oracle, I use "profile limits". You can enforce password discipline by modifying limits in the DEFAULT profile (which is everyone's standard profile if you have not explicitly assigned a profile to a user), or you can create a different profile (named, "USER_LIMIT_ENFORCEMENT", for example) to which you assign password discipline and assign the profile to the user or users which should be subject to the profile's restrictions.

Here is how to create a profile that focuses on password management:

CREATE PROFILE USER_LIMIT_ENFORCEMENT limit
  FAILED_LOGIN_ATTEMPTS 3 -- 3 chances to get it right
  PASSWORD_LOCK_TIME 1/1440 -- 1 hour lockout from failure
  PASSWORD_LIFE_TIME 30 -- must change password in 30 days
  PASSWORD_GRACE_TIME 10 -- 10 days change grace period
  PASSWORD_VERIFY_FUNCTION pw_verify_function;

To cause a user to be subject to the above restrictions:

ALTER USER <username> PROFILE user_limit_enforcement;

If you wish to use the PASSWORD_VERIFY_FUNCTION option named above, you must create the function in the SYS schema. This function allows some rather complex password-characteristic checking. You may modify the function to behave as you wish. Here are instructions and code to create the "pw_verify_function" appearing above. (Function code is compliments of LLC Systems):

-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is also provided.
-- This function makes the minimum complexity checks like
-- the minimum length of the password, password not same as the
-- username, etc. The user may enhance this function according to
-- the need.
-- This function must be created in SYS schema.
-- connect sys/<password> as sysdba before running the script

CREATE OR REPLACE FUNCTION pw_verify_function
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS 
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52);
BEGIN 
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray:='!"#$%&()''*+,-/:;<=>?_';

   -- Check if the password is same as the username
   IF password = username THEN
     raise_application_error(-20001, 'Password same as user');
   END IF;

   -- Check for the minimum length of the password
   IF length(password) < 4 THEN
      raise_application_error(-20002, 'Password length less than 4');
   END IF;
   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF password IN ('welcome', 'password', 'oracle', 'computer', 'abcd') THEN
      raise_application_error(-20002, 'Password too simple');
   END IF;
  
   -- Check if the password contains at least one letter, one digit and one
   -- punctuation mark.
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP 
       FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;
   IF isdigit = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one digit,
one character and one punctuation');
   END IF;
   -- 2. Check for the character
   <<findchar>>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO findpunct;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one \
              digit, one character and one punctuation');
   END IF;
   -- 3. Check for the punctuation
   <<findpunct>>
   ispunct:=FALSE;
   FOR i IN 1..length(punctarray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(punctarray,i,1) THEN
            ispunct:=TRUE;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ispunct = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one \
              digit, one character and one punctuation');
   END IF;

   <<endsearch>>
   -- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password = '' THEN
      raise_application_error(-20004, 'Old password is null');
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
   differ := length(old_password) - length(password);

   IF abs(differ) < 3 THEN
      IF length(password) < length(old_password) THEN
         m := length(password);
      ELSE
         m := length(old_password);
      END IF;
      differ := abs(differ);
      FOR i IN 1..m LOOP
          IF substr(password,i,1) != substr(old_password,i,1) THEN
             differ := differ + 1;
          END IF;
      END LOOP;
      IF differ < 3 THEN
          raise_application_error(-20004, 'Password should differ by at \
            least 3 characters');
      END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/

So, bottom line, to do what you requested, you can get away with defining and assigning a profile with just the expiry period (and I recommend the grace period):

CREATE PROFILE USER_LIMIT_ENFORCEMENT limit
  PASSWORD_LIFE_TIME 30 -- must change password in 30 days
  PASSWORD_GRACE_TIME 10 -- 10 days change grace period
  ;

If you want these restrictions to apply to everyone in the database, then you can change the profile named DEFAULT:

ALTER PROFILE default LIMIT
  PASSWORD_LIFE_TIME 30 -- must change password in 30 days
  PASSWORD_GRACE_TIME 10 -- 10 days change grace period
  ;

Let us know how all this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:40 (29Sep04) UTC (aka "GMT" and "Zulu"), 17:40 (28Sep04) Mountain Time)