In this post, I aim to depict some practical utilizations of assorted Oracle string functions as an antidote to bland, often pointless examples given in SQL documentation. Security and reliable user authentication is ever in the news these days, and many studies confirm that most users continue to choose facile and predictable userids and/or passwords. This article summarizes recent user habits and hacker trends regarding passwords with some basic recommendations, while these two illustrate some of the most common (thus insecure) passwords.

For our purposes, let’s assume we are authenticating within the database rather than relying upon an external identity manager, and that we already posess hundreds of thousands or more users whose credentials we’d like to survey. Those found to be insufficiently difficult to guess can be flagged for an update dialog upon their user’s subsequent login. Here’s some basic flaws we want to flag as violations:

1. Password aging has been violated (greater than 180 days)
2. Password length is less than 8 (or 10) characters
3. Password is identical with or similar to userid
4. Password is identical with or reverse of a known list of the most common passwords
5. Password lacks at least one lowercase alpha, uppercase alpha, numeric, or special character
6. Password closely mimics dictionary word masking S/s with $, A/a with @, O/o with 0, or I/i with 1

These are arranged roughly in order of increasing complexity. Of course, DBAs and management will set up their own policy and priorities according to their security needs.

Logistics
If I were designing this task I would likely first build a work table or view which collected the existing userids and passwords in one place (from their actual table locations) and de-hashed or decrypted everything as necessary. Each strength test will influence the password’s strength score. Afterwards, we can decide which cutoff score would merit updating the change dialog flag. The uniq_id is a system-generated value which will serve as a primary key into the work table.
w h i t e s p a c e

I’d probably place all the code into a PL/SQL package with separate functions for each of the tests, but recalling that the point of this article is to illustrate some string functions, I am not going to actually build the whole package here. Instead I will indicate the various tests and techniques along the way with straight SQL.

Basic Strength Tests
First, let’s handle password aging. All security experts agree that leaving a password unchanged for too long represents a risk regardless of the strength; most suggest changing it several times a year. Let’s be ‘lenient’ and impose a 6-month aging limit. And while eight used to be de rigeur, minimum length recommendations of ten for passwords are now common:
w h i t e s p a c e

w h i t e s p a c e

w h i t e s p a c e

Many users succumb to equating their password with their userid, but we would like to go beyond that and flag as violations those passwords which are merely similar to the userid. There are various kinds of ‘similarity’ in practice. The password might contain the userid, perhaps appending or prepending some numerics or other characters. Or the password might consist of or contain the userid backwards. Or else, the two may be identical except for differences in the case of some characters: gunther becomes gUnThEr.
w h i t e s p a c e

w h i t e s p a c e

To illustrate these a bit further, in both cases below the ‘disguised’ userid substring is located; the first at position 1 within the password string, and the second at position 4:
w h i t e s p a c e

w h i t e s p a c e
This last example may seem to be overkill, rejecting a reasonably obscure password. This is where an overall scoring policy comes into play. For example, a later test could increase the strength score by finding examples of upper, lower, numeric, and special characters, and so the final strength score could be deemed acceptable.

For the fourth test, let’s reference the zip file linked in the article revealing a list of the 10,000 most frequently used English passwords. (I used the link which also contains frequency data.) The author, Mark Burnett, states that over 98% of all users choose a password within this meager list! Hackers know this too, so we gain much by flagging the entire list, and perhaps lose much by not. The approach we’ll take is to set the list (a text file) up as an external table:
w h i t e s p a c e

w h i t e s p a c e
Now it’s a simple matter to drive the test off the external table. We’ll enhance this test by adding a check for the reverse of any password in the top 10K list as well.
w h i t e s p a c e

w h i t e s p a c e

Note: if you’re thinking ‘reusable tool’ instead of ‘one-off’ testing here, then you may want to transfer the data inside the database as then you’d be able to build indexes, including a function-based one for the reverse search. We’ll see this approach further below.

More Complex Strength Tests
Websites have long recommended that browser clients include at least one instance each of mixed case letters, numbers, and special characters in their passwords. But it wasn’t too long ago that Oracle began recognizing mixed case letters in passwords. Oracle has forever suggested limiting special characters within passwords to these three: _ and $ and #. Various people have demonstrated over time however that there are many allowable special characters in Oracle passwords. Part of the controversy is because some characters will be treated as meta-characters by various scripting tools or network utilities. For our purposes, let’s allow: ~ ! @ # $ % ^ * ( ) _ +

The test below relies upon the fact that any four numbers mutiplied together will produce zero if any of the numbers are zero; instr() returns a number indicating the starting position of the located string, or a zero when nothing is found. The translate() string function maps all occurrences of the characters in the 2^nd argument with the character in the corresponding position in the 3^rd argument. So in our example, numbers will translate to N, uppercase alphas to A, lowercase alphas to a, and special characters to $.
w h i t e s p a c e

w h i t e s p a c e

Our final test arises because it has become fairly common for savvy users to disguise passwords by spelling out dictionary words but switching some letters for characters which resemble the original letter. Here’s a few examples:
    Pa$$w0rd ; r@t10n@l ; Suz1eL0vesMe
The hacking community uses various web-accessible wordlists to drive brute force guessing attempts. Suppose we’ve chosen as our goal to flag all passwords consisting of, or containing, a dictionary word, case-independent, which has been modified with any of these substitutions: @ for A/a ; 1 for I/i ; 0 for O/o ; and $ for S/s. We’ll need a good English wordlist, such as this one. 

You’ll notice this wordlist could use some cleanup, namely stripping away lexical entries which do not begin with alpha characters, plus those which are compound words. We should also remove the arbitrarily short words, say four characters or less, because they would inadvertantly match numerous strings. Probably all this is best done with Unix utilities before importing it. But since we’re exploring string functions, we’ll do it with SQL. External tables are readonly, so we’ll construct a work table in order to effect the cleanup, also affording us the chance to build indexes.
 

w h i t e s p a c e
                                                                                                             read more on REGEXP functions 

The REGEXP string functions used above were added to Oracle in Release 10g. They employ the regular expression syntax long familiar to Unix or Linux shell programmers, and permit the construction of more powerful matching predicates than were ordinarily available.
w h i t e s p a c e

w h i t e s p a c e
So, about half of the original ‘words’ have been filtered out. Now we can perform the desired password strength test:
w h i t e s p a c e

w h i t e s p a c e

Final Words
The resulting strength_score values after these tests are completed can be expressed as a binary number so that ‘0’ would mean that the respective test showed a violation, while ‘1’ would indicate a passed test. That’s the reason the score increments were chosen in increasing powers of two, and also why they are additive. So, for example, if the final score for a password were 21, the binary conversion is 010101. Meaning only the first, third, and fifth tests succeeded: password-aging, password similarity with userid, and the mixed alpha plus numeric plus special character type requirement. In this way, various kinds of analysis could be done on your pool of passwords. You are free to devise your own tests and arrange their scoring as wished.

If you are looking around for a decimal-to-binary conversion function in Oracle, I was surprised none was included in the product too. Here’s a pretty elegant one coded in PL/SQL, public domain.

One area of expansion to consider is checking against a list of given names. A decent English list of over 21000 names, from the U.S. and the U.K. can be found here. A good approach might be to simply merge this list into our existing English wordlist table. Depending on things you might really need to zoom in on romanizations of Hindi or Russian names. Your oyster is out there! 

Release 11g provides a basic complexity verification routine for passwords, called verify_function_11g, which is available via the installation script utlpwdmg.sql in $ORACLE_HOME/rdbms/admin/. It’s not as stringent as some of the tests we’ve just looked at, but I mention it for completeness sake.
 
~RS