VERIFY_PASSWORD_FUNCTION option

Specifies a user-supplied authentication function that can be used to implement password rules. The function is called on a GRANT CONNECT TO userid IDENTIFIED BY password statement.

String

Can be set temporary, per user, or for the PUBLIC group. DBA authority is required to set the option. This option takes effect immediately.

'' (the empty string). (No function is called on GRANT CONNECT.)

When the VERIFY_PASSWORD_FUNCTION option value is set to a valid string, the statement GRANT CONNECT TO userid IDENTIFIED BY password calls the function specified by the option value.

The option value requires the form owner.function_name to prevent users from overriding the function.

The function takes two parameters:

• user_name VARCHAR(128)

• new_pwd VARCHAR(255)

It returns a value of type VARCHAR(255).

If the VERIFY_PASSWORD_FUNCTION option is set, you cannot specify more than one userid and password with the GRANT CONNECT statement.

For example, this statement creates a function that requires the password to be different from the user name:

CREATE FUNCTION DBA.f_verify_pwd
( user_name varchar(128), 
new_pwd varchar(255) )
RETURNS varchar(255)
BEGIN
-- enforce password rules
IF new_pwd = user_name then
RETURN('Password cannot be the same as the user name' );
END IF;
-- return success
RETURN( NULL );
END;
ALTER FUNCTION DBA.f_verify_pwd set hidden;
GRANT EXECUTE on DBA.f_verify_pwd to PUBLIC;
SET OPTION PUBLIC.VERIFY_PASSWORD_FUNCTION = 'DBA.f_verify_pwd';

To turn the option off, set it to the empty string:

SET OPTION PUBLIC.VERIFY_PASSWORD_FUNCTION = ''