ORA-04050

This was tested on Oracle 11.2. When you create a profile, its PASSWORD_VERIFY_FUNCTION (PVF) will be shown as DEFAULT if you do not specify it explicitly:

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> create profile andrews_profile

  2  limit password_life_time 60

  3  /

Profile created.

SQL> select limit from dba_profiles

  2  where profile = 'ANDREWS_PROFILE'

  3  and resource_name = 'PASSWORD_VERIFY_FUNCTION'

  4  /

LIMIT

----------------------------------------

DEFAULT 

SQL> 

This does not mean that it has a PVF called DEFAULT. It means that it has the same PVF as the DEFAULT profile. In this database the DEFAULT profile's PVF is NULL, which means it has no PVF:

SQL> select limit from dba_profiles

  2  where profile = 'DEFAULT'

  3  and resource_name = 'PASSWORD_VERIFY_FUNCTION'

  4  /

LIMIT

----------------------------------------

NULL 

SQL> 

You can create a function to use as a PVF.  Here is a very simple example:

SQL> create or replace function andrews_verify_function(

  2    username     varchar2,

  3    password     varchar2,

  4    old_password varchar2)

  5    return boolean as

  6  begin

  7    if length(password) < 4 then

  8      return false;

  9    else

 10      return true;

 11    end if;

 12  end andrews_verify_function;

 13  /

Function created. 

SQL> 

You can make a profile use it like this:

SQL> alter profile andrews_profile limit

  2  password_verify_function andrews_verify_function

  3  /

 

Profile altered.

 

SQL>

... and the name of the function will be stored in DBA_PROFILES:

SQL> select limit from dba_profiles

  2  where profile = 'ANDREWS_PROFILE'

  3  and resource_name = 'PASSWORD_VERIFY_FUNCTION'

  4  /

 

LIMIT

----------------------------------------

ANDREWS_VERIFY_FUNCTION

 

SQL>

Then I saw a potential problem. If I had a function called DEFAULT, and a profile with PVF set to DEFAULT, how would Oracle know if I was referring to the function called DEFAULT or the PVF used by the DEFAULT profile? Fortunately the clever people at Oracle have thought of that. If you try to create a function called DEFAULT, you get an error:

SQL> create or replace function default(

  2    username     varchar2,

  3    password     varchar2,

  4    old_password varchar2)

  5    return boolean as

  6  begin

  7    if length(password) < 4 then

  8      return false;

  9    else

 10      return true;

 11    end if;

 12  end default;

 13  /

create or replace function default(

                           *

ERROR at line 1:

ORA-04050: invalid or missing procedure, function, or

package name

SQL>

Reply to: Reply to Andrew Reid

Send

Password_Verify_Function (Part 1)

This example was tested on Oracle 10 on Linux. It shows you how to create a simple password verify function (PVF). First connect as the SYS user because a PVF must be owned by SYS:

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> 

Create a profile to use the PVF:

SQL> create profile andrews_profile
  2  limit password_life_time 60
  3  /

Profile created.

SQL>

Try to give my new profile a PVF called my_verify_function. This fails as it has not been created yet:

SQL> alter profile andrews_profile limit
  2  password_verify_function my_verify_function
  3  /
alter profile andrews_profile limit
*
ERROR at line 1:
ORA-07443: function MY_VERIFY_FUNCTION not found

SQL>

Create a function with the required name. A PVF must have the parameters shown. It should be obvious what this PVF is supposed to do:

SQL> create or replace function my_verify_function (
  2  username     varchar2,
  3  password     varchar2,
  4  old_password varchar2)
  5  return boolean as
  6  begin
  7  if length(password) < 4 then
  8    return false;
  9  else
 10    return true;
 11  end if;
 12  end my_verify_function;
 13  /

Function created.

SQL>

Change the new profile to use this PVF:

SQL> alter profile andrews_profile limit
  2  password_verify_function my_verify_function
  3  /

Profile altered.

SQL>

Create a user and assign it the new profile:

SQL> create user andrew identified by reid1
  2  profile andrews_profile
  3  /

User created.

SQL>

Change the user’s password. The new value is passed to the PVF and rejected:

SQL> alter user andrew identified by rei
  2  /
alter user andrew identified by rei
*
ERROR at line 1:
ORA-28003: password verification for the specified
password failed
ORA-28003: password verification for the specified
password failed

SQL>

The error message was not very helpful so change the PVF accordingly:

SQL> create or replace function my_verify_function (
  2  username     varchar2,
  3  password     varchar2,
  4  old_password varchar2)
  5  return boolean as
  6  begin
  7  if length(password) < 4 then
  8   raise_application_error
  9   (-20000, 'Password < 4 characters long');
 10  else
 11   return true;
 12  end if;
 13  end my_verify_function;
 14  /

Function created.

SQL>

Try to change the password again. This time the reason for rejection is clear:

SQL> alter user andrew identified by rei
  2  /
alter user andrew identified by rei
*
ERROR at line 1:
ORA-28003: password verification for the specified
password failed
ORA-20000: Password < 4 characters long

SQL>

Fix the problem and try again:

SQL> alter user andrew identified by reid2
  2  /

User altered.

SQL>