We are currently using Oracle's password function (utlpwdmg.sql, edited) verify_function as part of security in a 9i database. I would like to go further and disallow several hundred obvious passwords (e.g., password#1) that could still meet verification standards. The obvious way is to add these words to the -20002 section that checks if it is too simple. However the function also says that a dictionary of words can be maintained, and to that end I have created a table. Can you assist and show me how I reference this from the verify_function instead of the usual (password) IN ('welcome', 'blah1', 'blah2')?
You'll need to do a little recoding of your password verification function. Store all of your words in a table in the database. I'm going to assume that these words are in the BAD_WORD column of the BAD_PASSWORDS table. In the declaration section of the password verification function declare a NUMBER variable that we'll call BAD_COUNT. Now for the easy part; add code similar to the following to your password verification function:
-- Get the number of times the new password is found in the BAD_PASSWORDS table
SELECT COUNT(*) INTO bad_count FROM bad_passwords
WHERE bad_word = new_password;
-- If count = 0, then new password not in the table, else count at least 1.
IF bad_count > 0 THEN RAISE APPLICATION ERROR (-20002,'Cannot use a simple word for the password');
To add a new "bad" word, simply insert the word into the table. There will be no need to recode the password verification function.
Dig Deeper on Oracle database design and architecture