In Oracle Database, predefined functions are available to enforce password complexity rules, which can be applied through user profiles.
Searching and Activating Predefined Functions
To view profiles and the corresponding password verification functions:
select profile, resource_name, limit
from dba_profiles
where resource_name = 'PASSWORD_VERIFY_FUNCTION';
Output:
PROFILE RESOURCE_NAME LIMIT
------------------------------ -------------------------------- ---------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION NULL
ORA_STIG_PROFILE PASSWORD_VERIFY_FUNCTION ORA12C_STIG_VERIFY_FUNCTION
If this is not present by default, we can use the following script in the database:
@?/rdbms/admin/utlpwdmg.sql
After execution, the output will change as follows:
PROFILE RESOURCE_NAME LIMIT
------------------------------ -------------------------------- ---------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION
ORA_STIG_PROFILE PASSWORD_VERIFY_FUNCTION ORA12C_STIG_VERIFY_FUNCTION
Examples of Oracle’s Predefined Functions
ORA12C_VERIFY_FUNCTION
-
Minimum of 8 characters
-
Must contain a letter, a number, and a special character
-
Must not include the username, database name, or the word "oracle"
-
Must differ from the previous password by at least 3 characters
Creating Custom Password Verification Functions
We can create custom functions to enforce specific password policies. Example:
CREATE OR REPLACE FUNCTION vahiddb_19c_verify_function (
username VARCHAR2,
password VARCHAR2,
old_password VARCHAR2)
RETURN BOOLEAN IS
differ INTEGER;
lang VARCHAR2(512);
message VARCHAR2(512);
ret NUMBER;
BEGIN
lang := sys_context('userenv','lang');
lang := substr(lang, 1, instr(lang, '_') - 1);
IF NOT ora_complexity_check(password, chars => 12, uppercase => 1, lowercase => 1,
digit => 1, special => 1) THEN
RETURN FALSE;
END IF;
IF old_password IS NOT NULL THEN
differ := ora_string_distance(old_password, password);
IF differ < 8 THEN
ret := utl_lms.get_message(28211, 'RDBMS', 'ORA', lang, message);
raise_application_error(-20000, utl_lms.format_message(message, 'eight'));
END IF;
END IF;
RETURN TRUE;
END;
/
Testing and Assigning the Profile
Creating a new profile:
CREATE PROFILE app_profile LIMIT
PASSWORD_VERIFY_FUNCTION vahiddb_19c_verify_function
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 3;
Assigning the profile and testing:
CREATE USER test IDENTIFIED BY test PROFILE app_profile;
Output:
ORA-28003: password verification for the specified password failed
ORA-20000: password length less than 12 bytes
This indicates that the function is active and enforcing the defined password complexity policy.