2 Replies Latest reply on May 13, 2013 8:28 AM by DK2010

    Password_Verification_Function

    1008372
      Hi all,

      I am new to oracle DB security and need your help to sort out my issue with Password_Verification_Function. I wrote a Password_Verification_Function as below:

      CREATE OR REPLACE FUNCTION verify_function_11g_mis001
      (username varchar2,
      password varchar2,
      old_password varchar2)
      RETURN boolean IS
      nLength number;
      specialchararray varchar2(10);
      digitarray varchar2(10);
      ischar boolean;
      digitcount number;
      BEGIN
      nLength := length(password);
      specialchararray := '$_#';
      ischar := false;
      digitcount := 0;
      digitarray := '0123456789';
      --checking character length
      IF nLength < 8 THEN
      raise_application_error(-20001, 'Password length less than 8');
      END IF;
      -- checking against given words
      IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'oracle123', 'password1', 'abc123', 'ucscmsc', 'mis123') THEN
      raise_application_error(-20006, 'Password too simple');
      END IF;
      -- checking for special character
      ischar := FALSE;
      FOR i IN 1..length(specialchararray) LOOP
      FOR j IN 1..nLength LOOP
      IF substr(password,j,1) = substr(specialchararray,i,1) THEN
      ischar := TRUE;
      GOTO finddigit;
      END IF;
      END LOOP;
      END LOOP;
      IF ischar = FALSE THEN
      raise_application_error(-20009, 'Password must contain at least one special character');
      END IF;
      -- checking for 2 digits
      <<finddigit>>
      FOR i IN 1..10 LOOP
      FOR j IN 1..nLength LOOP
      IF digitcount >= 2 THEN
      GOTO endsearch;
      END IF;
      IF substr(password,j,1) = substr(digitarray,i,1) THEN
      digitcount := digitcount + 1;
      END IF;
      END LOOP;
      END LOOP;
      <<endsearch>>
      -- Everything is fine; return TRUE ;
      RETURN(TRUE);
      END;
      /

      i am able to compile with out any compilation errors. "FUNCTION VERIFY_FUNCTION_11G_MIS001 compiled"
      But when I try following gives me an error

      CREATE profile Mis001Profile LIMIT
      FAILED_LOGIN_ATTEMPTS 3
      PASSWORD_LOCK_TIME 1
      PASSWORD_LIFE_TIME 180
      PASSWORD_GRACE_TIME 3
      PASSWORD_VERIFY_FUNCTION verify_function_11g_mis001 ;

      Error:
      Error at Command Line:66 Column:1
      Error report:
      SQL Error: ORA-07443: function VERIFY_FUNCTION_11G_MIS001 not found
      07443. 00000 - "function %s not found"
      *Cause:    An invalid function name was specified.
      *Action:   Use a valid function name.

      What is the issue here? I am using oracle 11g express edition (academic work)

      Thanks
      Udeshika