Creating a Password Function v12

When specifying PASSWORD_VERIFY_FUNCTION, you can provide a customized function that specifies the security rules that will be applied when your users change their password. For example, you can specify rules that stipulate that the new password must be at least n characters long, and may not contain a specific value.

The password function has the following signature:

<function_name> (<user_name> VARCHAR2,
                 <new_password> VARCHAR2,
                 <old_password> VARCHAR2) RETURN boolean

Where:

• user_name is the name of the user.

• new_password is the new password.

• old_password is the user's previous password. If you reference this parameter within your function:

When a database superuser changes their password, the third parameter will always be NULL.

When a user with the CREATEROLE attribute changes their password, the parameter will pass the previous password if the statement includes the REPLACE clause. Note that the REPLACE clause is optional syntax for a user with the CREATEROLE privilege.

When a user that is not a database superuser and does not have the CREATEROLE attribute changes their password, the third parameter will contain the previous password for the role.

The function returns a Boolean value. If the function returns true and does not raise an exception, the password is accepted; if the function returns false or raises an exception, the password is rejected. If the function raises an exception, the specified error message is displayed to the user. If the function does not raise an exception, but returns false, the following error message is displayed:

ERROR: password verification for the specified password failed

The function must be owned by a database superuser, and reside in the sys schema.

Example:

The following example creates a profile and a custom function; then, the function is associated with the profile. The following CREATE PROFILE command creates a profile named acctg_pwd_profile:

CREATE PROFILE acctg_pwd_profile;

The following commands create a (schema-qualified) function named verify_password:

CREATE OR REPLACE FUNCTION sys.verify_password(user_name varchar2,
new_password varchar2, old_password varchar2)
RETURN boolean IMMUTABLE
IS
BEGIN
  IF (length(new_password) < 5)
  THEN
    raise_application_error(-20001, 'too short');
  END IF;

  IF substring(new_password FROM old_password) IS NOT NULL
  THEN
    raise_application_error(-20002, 'includes old password');
  END IF;

  RETURN true;
END;

The function first ensures that the password is at least 5 characters long, and then compares the new password to the old password. If the new password contains fewer than 5 characters, or contains the old password, the function raises an error.

The following statement sets the ownership of the verify_password function to the enterprisedb database superuser:

ALTER FUNCTION verify_password(varchar2, varchar2, varchar2) OWNER TO
enterprisedb;

Then, the verify_password function is associated with the profile:

ALTER PROFILE acctg_pwd_profile LIMIT PASSWORD_VERIFY_FUNCTION
verify_password;

The following statements confirm that the function is working by first creating a test user (alice), and then attempting to associate invalid and valid passwords with her role:

CREATE ROLE alice WITH LOGIN PASSWORD 'temp_password' PROFILE
acctg_pwd_profile;

Then, when alice connects to the database and attempts to change her password, she must adhere to the rules established by the profile function. A non-superuser without CREATEROLE must include the REPLACE clause when changing a password:

edb=> ALTER ROLE alice PASSWORD 'hey';
ERROR:  missing REPLACE clause

The new password must be at least 5 characters long:

edb=> ALTER USER alice PASSWORD 'hey' REPLACE 'temp_password';
ERROR:  EDB-20001: too short
CONTEXT:  edb-spl function verify_password(character varying,character
varying,character varying) line 5 at procedure/function invocation statement

If the new password is acceptable, the command completes without error:

edb=> ALTER USER alice PASSWORD 'hello' REPLACE 'temp_password';
ALTER ROLE

If alice decides to change her password, the new password must not contain the old password:

edb=> ALTER USER alice PASSWORD 'helloworld' REPLACE 'hello';
ERROR:  EDB-20002: includes old password
CONTEXT:  edb-spl function verify_password(character varying,character
varying,character varying) line 10 at procedure/function invocation statement

To remove the verify function, set password_verify_function to NULL:

ALTER PROFILE acctg_pwd_profile LIMIT password_verify_function NULL;

Then, all password constraints will be lifted:

edb=# ALTER ROLE alice PASSWORD 'hey';
ALTER ROLE