Most companies will have some standard for password complexity they follow and the Oracle database has long supported enforcement of those standards by attaching password verification functions, owned by SYS to profiles. These standards are so common and so important Oracle not only allows; but forces you to put those user-defined objects into their SYS schema.
Common rules include minimum password lengths, combinations of lower- and upper-case letters, numeric digits, as well as non-alphanumeric symbols such as punctuation symbols. These rules are not particularly difficult to enforce individually but a quick Google search will reveal many, many different questions and solutions as to how to implement these rules in combinations.
Fortunately, in 12c Oracle provided some built-in examples, such as the ora12c_stig_verify_function and, more importantly, the ORA_COMPLEXITY_CHECK function which consolidates some of these common rules into a single call.
sys.ora_complexity_check(
password VARCHAR2,
chars INTEGER:= NULL,
letter INTEGER:= NULL,
uppercase INTEGER:= NULL,
lowercase INTEGER:= NULL,
digit INTEGER:= NULL,
special INTEGER:= NULL)
RETURN BOOLEAN
With a single call to this function you can check if a password has a minimum number of characters, letters, numbers, or special symbol characters, as well as enforcing combinations of lower-case and upper-case letters.
Following are two simple examples where the complexity rules state the password must be at least 8 characters long, with at least one upper case letter, one lower case letter, one number, and one special character. Note the IF only checks for the TRUE condition. The function will either return TRUE, or raise an exception and fail. It does not return FALSE.
SQL> BEGIN
2 IF sys.ora_complexity_check(
3 password => 'secret$1234',
4 chars => 8,
5 letter => NULL,
6 uppercase => 1,
7 lowercase => 1,
8 digit => 1,
9 special => 1)
10 THEN
11 DBMS_OUTPUT.put_line('Passed');
12 END IF;
13 END;
14 /
BEGIN
*
ERROR at line 1:
ORA-20000: password must contain 1 or more uppercase characters
ORA-06512: at "SYS.ORA_COMPLEXITY_CHECK", line 79
ORA-06512: at line 2
SQL> BEGIN
2 IF sys.ora_complexity_check(
3 password => 'Secret$1234',
4 chars => 8,
5 letter => NULL,
6 uppercase => 1,
7 lowercase => 1,
8 digit => 1,
9 special => 1)
10 THEN
11 DBMS_OUTPUT.put_line('Passed');
12 END IF;
13 END;
14 /
Passed
Unlike most Oracle functions, ORA_COMPLEXITY_CHECK source code is not wrapped. Instead, it is exposed with the intent that it be used as an example to build your own functions. Looking through the code and reading the comments we find some unusual features due to its support of multiple languages and characters beyond the simple ASCII alphanumerics and symbols.
Digraphs and Roman numerals in particular can create unexpected results. If a someone were to write the Roman numeral 4, it is “IV”, which is visually two characters, an “I” and a “V”; but you can also write it as a single unicode character 2163 – Ⅳ. This single character has the unusual property within ORA_COMPLEXITY_CHECK of being counted as an upper case letter as well as a numeric digit, and only counting as one character toward the overall length. There are other Roman numeral combinations as well as lower case versions, all share these unusual properties.
Some might find the the Roman numeral example a little far-fetched but many languages have digraph characters, such as the Hungarian “Dz” which, when written or read in English, is 2 letters, but within the Hungarian alphabet it is a single letter. It has its own unicode characters depending on how the capitalization is applied 01F1 “DZ”, 01F2 “Dz”, 01F3 “dz”. The 01F2 version is particularly odd because it is a single character; but it counts as both an upper case letter as well as a lower case letter.
In the example below I’ve reduced my complexity rules to just looking for at least 1 upper case and at least one lower case letter and the single digraph character is able to satisfy both clauses.
BEGIN
IF sys.ora_complexity_check(
password => 'Dz',
uppercase => 1,
lowercase => 1
)
THEN
DBMS_OUTPUT.put_line('Passed');
END IF;
END;
/
passed
This functionality make sense when you take into account the digraph support of unicode and they are all documented in the comments of the function. However, for many places these quirks may not fit the spirit of their complexity standards. Also, you may have standards stating the passwords don’t have minimums for each type of character; but must have some minimum number of types represented. So rather than a minumum for upper, a minumum for lower, and minimum for digits, and a minimum for symbols, your standard says the password must be 8 characters or more in length and have characters from at least 3 of those categories. So, upper/lower/numeric would be ok, upper/lower/symbol would be ok, lower/symbol/numeric would also be ok, but a password of only upper and lower case letters would not be valid.
Another issue you might run into is a database might still be using 11g or older and won’t have the ORA_COMPLEXITY_CHECK function at all.
So, for those databases here is an alternative…
The function below can support any and all of the checks described above, depending on the parameters passed in.
CREATE OR REPLACE FUNCTION
alt_complexity_check(
p_password IN VARCHAR2,
p_min_length IN NUMBER DEFAULT NULL,
p_types IN vctab DEFAULT NULL,
p_type_minimums IN numtab DEFAULT NULL,
p_min_type_count IN NUMBER DEFAULT NULL,
p_restricted IN VARCHAR2 DEFAULT 'N')
RETURN BOOLEAN
IS
v_stripped VARCHAR2(32767);
v_type_count PLS_INTEGER := 0;
v_valid BOOLEAN := TRUE;
v_index PLS_INTEGER := 1;
v_password_length PLS_INTEGER;
BEGIN
--Check for the minimum length of the password
v_password_length := NVL(LENGTH(p_password), 0);
v_valid := v_password_length >= NVL(p_min_length,0);
IF v_valid AND p_types IS NOT NULL
THEN
-- If types have been specified with minimums
--then ensure they have the same number of values
IF p_type_minimums IS NOT NULL
AND p_type_minimums.COUNT != p_types.COUNT
THEN
RAISE VALUE_ERROR;
END IF;
--Check for character membership in each of
--the defined categories
WHILE v_valid AND v_index <= p_types.COUNT
LOOP
-- Strip the password of each type of
-- characters with TRANSLATE.
-- Translate the characters of the
-- type string to nothing.
v_stripped :=
TRANSLATE(p_password,
CHR(0) || p_types(v_index),
CHR(0));
-- If the stripped string is not equal to
-- the original then at least one character
-- was removed, meaning the password contained
-- one or more of those characters.
-- This assumes CHR(0) - the null character,
-- will not be part of the password
-- Oracle does not allow the null character
-- in identifiers so this assumption will be safe.
IF NVL(p_password, CHR(0)) != NVL(v_stripped, CHR(0))
THEN
v_type_count := v_type_count + 1;
END IF;
-- If minimums are defined, check if minimum number
-- of characters for the type were stripped
IF p_type_minimums IS NOT NULL
THEN
v_valid := NVL(p_type_minimums(v_index), 0) <= (v_password_length - LENGTH(v_stripped));
END IF;
v_index := v_index + 1;
END LOOP;
-- Check the password has characters from enough types
-- to be considered sufficiently "complex"
IF v_valid AND p_min_type_count IS NOT NULL
THEN
v_valid := (v_type_count >= p_min_type_count);
END IF;
-- If the password characters are restricted
-- confirm that every character in the password
-- is a member of the restricted types
IF v_valid AND UPPER(p_restricted) IN ('Y','YES')
THEN
v_stripped := p_password;
v_index := 1;
WHILE v_stripped IS NOT NULL
AND v_index <= p_types.COUNT
LOOP
v_stripped :=
TRANSLATE(v_stripped,
CHR(0) || p_types(v_index),
CHR(0));
v_index := v_index + 1;
END LOOP;
v_valid := (v_stripped IS NULL);
END IF;
END IF;
RETURN v_valid;
END;
/
The function does require 2 supporting collection objects. If you’ve implemented some of my other tools you may already have these, or similar objects available.
CREATE OR REPLACE TYPE vctab AS TABLE OF VARCHAR2(4000); CREATE OR REPLACE TYPE numtab IS TABLE OF NUMBER;
ALT_COMPLEXITY_CHECK is not intended to be a drop-in replacement for ORA_COMPLEXITY_CHECK. Probably the most important difference is the lack of any built-in character lists. That is, the function doesn’t include pre-defined sets of expected letters, numbers, or symbols. If the user wants to include rules for specific types of characters, those must be provided along with their corresponding minimums (if any.)
This function also provides the ability to restrict the choices of characters to a certain subset instead of the full unicode population. While the Oracle database can support these characters; supporting restrictions may be desirable when building a complexity checker for other applications that may not have the same level of support.
In addition to the different parameters, this alternative function will return FALSE instead of raising an exception when a password doesn’t satisfy the rules.
At its simplest, the function can be used to check minimum length:
SQL> begin
2 dbms_output.put_line(
3 case when
4 alt_complexity_check('ab7cde!fg',
5 5
6 )
7 then 'Verified'
8 else 'Not verified'
9 end
10 );
11 end;
12 /
Verified
SQL> begin
2 dbms_output.put_line(
3 case when
4 alt_complexity_check('ab7cde!fg',
5 10
6 )
7 then 'Verified'
8 else 'Not verified'
9 end
10 );
11 end;
12 /
Not verified
Adding the character types it starts to get more interesting. The example below checks if the password has at least 4 letters and at least one symbol.
SQL> begin
2 dbms_output.put_line(
3 case when
4 alt_complexity_check('ab7cde!fg',
5 5,
6 vctab('abcdefghijklmnopqrstuvwxyz',
7 '1234567890',
8 '!@#$'),
9 numtab(4,null,1)
10 )
11 then 'Verified'
12 else 'Not verified'
13 end
14 );
15 end;
16 /
Verified
Getting a little more complicated, the next examples have no explicit minimums but requires elements from all 4 of the character categories. The first one fails because it is missing upper-case characters. The second succeeds because it has elements from all.
SQL> begin
2 dbms_output.put_line(
3 case when
4 alt_complexity_check(p_password => 'ab7cde!fg',
5 p_types => vctab('abcdefghijklmnopqrstuvwxyz',
6 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
7 '1234567890',
8 '!@#$'
9 ),
10 p_min_type_count => 4
11 )
12 then 'Verified'
13 else 'Not verified'
14 end
15 );
16 end;
17 /
Not verified
SQL> begin
2 dbms_output.put_line(
3 case when
4 alt_complexity_check(p_password => 'ab7cXde!fg',
5 p_types => vctab('abcdefghijklmnopqrstuvwxyz',
6 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
7 '1234567890',
8 '!@#$'
9 ),
10 p_min_type_count => 4
11 )
12 then 'Verified'
13 else 'Not verified'
14 end
15 );
16 end;
17 /
Verified
Finally, we’ll add restrictions that all of the charactes must be from within the provided types. The first will pass, the second will not because it will contain an asterisk (*).
SQL> begin
2 dbms_output.put_line(
3 case when
4 alt_complexity_check(p_password => 'ab7cXde!fg',
5 p_types => vctab('abcdefghijklmnopqrstuvwxyz',
6 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
7 '1234567890',
8 '!@#$'
9 ),
10 p_restricted => 'Y'
11 )
12 then 'Verified'
13 else 'Not verified'
14 end
15 );
16 end;
17 /
Verified
SQL> begin
2 dbms_output.put_line(
3 case when
4 alt_complexity_check(p_password => 'ab*7cXde!fg',
5 p_types => vctab('abcdefghijklmnopqrstuvwxyz',
6 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
7 '1234567890',
8 '!@#$'
9 ),
10 p_restricted => 'Y'
11 )
12 then 'Verified'
13 else 'Not verified'
14 end
15 );
16 end;
17 /
Not verified
Using combinations of any or all of the parameters can evaluate numerous various of password complexity with a single call. Hopefully this tool will help simplify the code in your applications. Questions and comments, as always, are welcome.