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
Profile created.
SQL> select limit from dba_profiles
2 where profile = 'ANDREWS_PROFILE'
3 and resource_name = 'PASSWORD_VERIFY_FUNCTION'
4 /
LIMIT
LIMIT
----------------------------------------
DEFAULT
SQL>
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
LIMIT
----------------------------------------
NULL
SQL>
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>
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>
SQL>
No comments:
Post a Comment