Saturday, October 13, 2012

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>

No comments: