Oracle User Profile
Default Profiles
Oracle User Profiles control Password expiration and complexity, and resource limits.
Oracle ships with some default Profiles:
DEFAULT
MONITORING_PROFILE
In 12.2+ we also get...
ORA_STIG_PROFILE
In 21c+ we also get...
ORA_CIS_PROFILE
Check
SELECT profile,
resource_name,
resource_type,
limit
FROM dba_profiles
ORDER BY profile,
resource_type,
resource_name;
Create
Custom Profile
CREATE PROFILE custom_profile LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME 7
PASSWORD_LIFE_TIME 53
PASSWORD_REUSE_MAX 3
PASSWORD_REUSE_TIME 365
PASSWORD_VERIFY_FUNCTION CUSTOM_VERIFY_FUNCTION;
Example Profile with non-expiring password
CREATE PROFILE noexpire LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME 7
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION CUSTOM_VERIFY_FUNCTION;
CUSTOM_VERIFY_FUNCTION
User Profiles rely on a "verify" Function to enforce password complexity. If you want to change the password complexity rules from the Oracle default you can update the default Function or create your own (recommended).
CREATE OR REPLACE FUNCTION CUSTOM_VERIFY_FUNCTION
(username VARCHAR2,
password VARCHAR2,
old_password VARCHAR2)
RETURN BOOLEAN IS
n BOOLEAN;
m INTEGER;
differ INTEGER;
isdigit BOOLEAN;
ischar BOOLEAN;
ispunct BOOLEAN;
digitarray VARCHAR2(20);
punctarray VARCHAR2(25);
chararray VARCHAR2(52);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';
-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username)
THEN
RAISE_APPLICATION_ERROR(-20001, 'Password same as or similar to user');
END IF;
-- Check for the minimum length of the password
IF LENGTH(password) < 8
THEN
RAISE_APPLICATION_ERROR(-20002, 'Password length less than 8');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd')
THEN
RAISE_APPLICATION_ERROR(-20002, 'Password too simple');
END IF;
-- Check if the password contains at least one letter, one digit and one
-- punctuation mark.
-- 1. Check for the digit
isdigit:=FALSE;
m := LENGTH(password);
FOR i IN 1..10
LOOP
FOR j IN 1..m
LOOP
IF SUBSTR(password,j,1) = SUBSTR(digitarray,i,1)
THEN
isdigit:=TRUE;
GOTO findchar;
END IF;
END LOOP;
END LOOP;
IF isdigit = FALSE
THEN
RAISE_APPLICATION_ERROR(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 2. Check for the character
<<findchar>>
ischar:=FALSE;
FOR i IN 1..LENGTH(chararray)
LOOP
FOR j IN 1..m
LOOP
IF SUBSTR(password,j,1) = SUBSTR(chararray,i,1)
THEN
ischar:=TRUE;
GOTO findpunct;
END IF;
END LOOP;
END LOOP;
IF ischar = FALSE
THEN
RAISE_APPLICATION_ERROR(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
-- 3. Check for the punctuation
<<findpunct>>
ispunct:=FALSE;
FOR i IN 1..length(punctarray)
LOOP
FOR j IN 1..m
LOOP
IF SUBSTR(password,j,1) = SUBSTR(punctarray,i,1)
THEN
ispunct:=TRUE;
GOTO endsearch;
END IF;
END LOOP;
END LOOP;
IF ispunct = FALSE
THEN
RAISE_APPLICATION_ERROR(-20003, 'Password should contain at least one digit, one character and one punctuation');
END IF;
<<endsearch>>
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL
THEN
differ := LENGTH(old_password) - LENGTH(password);
IF ABS(differ) < 3
THEN
IF LENGTH(password) < LENGTH(old_password)
THEN
m := LENGTH(password);
ELSE
m := LENGTH(old_password);
END IF;
differ := ABS(differ);
FOR i IN 1..m
LOOP
IF SUBSTR(password,i,1) != SUBSTR(old_password,i,1)
THEN
differ := differ + 1;
END IF;
END LOOP;
IF differ < 3
THEN
RAISE_APPLICATION_ERROR(-20004, 'Password should differ by at least 3 characters');
END IF;
END IF;
END IF;
-- Everything is fine; return TRUE ;
RETURN(TRUE);
END;
/
Drop
DROP PROFILE custom_profile;