Word-Based Password Generator

articles: 

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))

PARAMETER FILE:

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.

Thanks.

Comments

Or the lazy man's way to load the data...

cat words | sed "s/^/insert into testuser.words values ('/g;s/$/')\;/g"

-- generates this
insert into testuser.words values ('10th');
insert into testuser.words values ('1st');
insert into testuser.words values ('2nd');
insert into testuser.words values ('3rd');
...

load data
Infile “
replace into table scott.words
(word char(50))

When I execute the above code it replies with ora-00900 invalid sql statement.

You have to put that text in your control file, not enter it directly into sqlplus.