Creating a password function v17
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 theREPLACE
clause. TheREPLACE
clause is optional syntax for a user with theCREATEROLE
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.
- When a database superuser changes their password, the third parameter is always
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