VERIFY_PASSWORD_FUNCTION Option

Specifies a user-supplied authentication function that can be used to implement password rules.

Allowed Values

String

Scope

Can be set temporary for an individual connection or for the PUBLIC group. DBA authority is required to set the option. This option takes effect immediately.

Default

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

Description

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)

The return value type is VARCHAR(255).
Note: Perform an ALTER FUNCTION function-name SET HIDDEN on the function to ensure that a user cannot step through it using the procedure debugger.

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

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 = ''
Related reference
ALTER FUNCTION Statement
GRANT Statement