Word-Based Password Generator
During my experiences with different environments, I have been tasked with maintaining passwords for different information systems. This includes operating system accounts (root, oracle, administrator) and Database accounts (sys, system, dbsnmp).
It can be sometimes difficult to remember many different passwords. I have seen some people overcome this by documenting the passwords, sometimes just in a plaintext file, sometimes encrypted, sometimes just on a ‘Post-It’ Note under the keyboard.
I try and retain the passwords in memory. To aide in this endeavor, I use passwords that are generated from real words, making the passwords complex, but readable and audible. To create a list of potential passwords, I use the same dictionary table data that we use in our password verify function. That function checks for dictionary words in a password, but that is another blog entry all together.
One can obtain a dictionary word file from any Unix or Linux machine, or as a download from the internet. In the *nix environment, the file is usually located in a folder like ‘/usr/share/dict/words’ or something similar.
The easiest way to load the words into the database is to use SQL Loader. Prior to executing SQL Loader, you must create the table:
SQL> create table testuser.words (word varchar2(100));
Now, you can create parameter and control files as:
CONTROL FILE (words.ctl):
load data Infile “<path_to_words.txt_file>” replace into table testuser.words (word char(50))
CONTROL=words.ctl LOG=words.log BAD=words.bad DISCARD=words.dsc ERRORS=0
Now load the words into the database:
Sqlldr control=words.ctl parfile=words.parfile
After you have loaded the words, you may want to create an index:
SQL> create index testuser.words_idx on testuser.words(word);
Now you are ready to generate some passwords. The following code will provide a list of five word-based complex passwords, starting at a random point in the word list. The current configuration provides a list of 8 letter passwords with at least one $ symbol and one number (8 or 3, or both), and one upper case letter ( for 11g forward).
WITH pwd_list AS (SELECT lower(word) word, rownum rn FROM words WHERE LENGTH(word) =10 AND instr(word,'s') > 0 AND instr(word,'e') > 0 AND instr(word,'ate') > 0 AND SUBSTR(word,1,1) NOT IN ('s','e') ) SELECT InitCap(REPLACE(REPLACE(REPLACE(word,'ate','8'),'s','$'),'e','3')) pwd FROM pwd_list WHERE pwd_list.rn >= (SELECT ROUND(dbms_random.value(1, (SELECT COUNT(*) FROM pwd_list )-6)) FROM dual ) AND rownum<=5;
Sample Result Set:
PWD -------------------- Immigr8$ Implic8$ Infuri8$ In$3min8 In$Inu8d 5 rows selected
Modify to meet your specific needs. Any comments or improvements are welcome.