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