Creating a password function v15

When specifying PASSWORD_VERIFY_FUNCTION, you can provide a customized function that specifies the security rules to apply 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 can't 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 in your function:

  • When a database superuser changes their password, the third parameter is always NULL.
  • When a user with the CREATEROLE attribute changes their password, the parameter passes the previous password if the statement includes the REPLACE clause. The REPLACE clause is optional syntax for a user with the CREATEROLE privilege.
  • When a user that isn't a database superuser and doesn't have the CREATEROLE attribute changes their password, the third parameter contains the previous password for the role.

The function returns a Boolean value. If the function returns true and doesn't 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 doesn't 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

This example creates a profile and a custom function. Then, the function is associated with the profile.

This 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 five characters long and then compares the new password to the old password. If the new password contains fewer than five 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 five 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 are lifted:

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

• On this page
• Example