Trivia question of the day: Did Alan Ludden, the host of the 1970's game show Password, ever use "password" as the secret password? If you missed out on that TV game show classic, not to worry. Oracle has a password game you can play. At one level, the rounds are easy, but in the bonus round, the game gets harder. The easy rounds occur when you use Oracle's own password function and external authentication. "Passwording" becomes more difficult when you use or implement advanced security features.
This article will review the use of Oracle's internal password function and the use of a SQL script for verifying password complexity. A future article in this series will cover external identification.
Passwording the regular way
There is not a lot to say about "create user scott identified by tiger," or is there? Can you break the 16-character hashed value of "F894844C34402B67?"
SQL> select username, password 2 from dba_users; USERNAME PASSWORD ------------------------- ------------------- SYS 8A8F025737A9097A SYSTEM D4DF7931AB130E37 DBSNMP E066D214D5421CCC SCOTT F894844C34402B67 SH 54B253CBBAAA8C48 HR 4C6D73C3E8B0F0DA STECAL BB5AEC0D7D7AB22B
If you could, what a major security flaw that would be! Aside from poor personal security, (a user leaving his password under the keyboard, or using something easily guessed that is personal in nature), your only real option in cracking an Oracle-generated password is via a brute force attempt (more on that in just a bit).
Can you reverse engineer the process or function Oracle uses? "Aha! I'll turn on tracing and capture the "create user" (or "alter user") statement." Let's see if that works. We will turn on tracing, issue the alter user statement, and then turn off tracing.
SQL> alter session set sql_trace = true; Session altered. SQL> alter user scott identified by lion; User altered. SQL> alter session set sql_trace = false; Session altered.
An extract from the trace file is shown below.
===================== PARSING IN CURSOR #1 len=35 dep=0 uid=5 oct=43 lid=5 tim=1946713635851 hv=3135717156 ad='66f0bc08' alter user scott identifi END OF STMT PARSE
Note the line from the trace file, where it shows "alter user scott identifi." Oracle truncates the statement so you cannot see the complete statement. So much for that idea.
What about creating another user and use the same password?
SQL> create user mary identified by lion; User created. SQL> select username, password 2 from dba_users 3 where username='MARY'; USERNAME PASSWORD ------------------------------ ----------------- MARY DA46FC0F06BCECEA
Even though Scott and Mary share the same password, the hashed values are entirely different.
If you are new to Oracle, one topic or trick you should research is the "identified by values" option. As a DBA (or other privileged user), how would you log in as a user should the need arise? You can take the 16-character password string and save it off somewhere, issue an alter user statement where you create a new password, log in as the user, and then reset the user's password with
alter user username identified by values 'the 16-character string from before';
What about a brute force attack on cracking an Oracle password? Just to pick one program at random off a Google search, the estimated time to crack a password can range from a few moments to well, practically, an infinite amount of time (http://www.password-crackers.com/crack2.html). If a user creates a simple password, the likelihood of a brute force password cracker's success increases. As the DBA, you can encourage users to create difficult to guess passwords, but can you enforce that rule?
Oracle's Password Complexity Verification
The utlpwdmg.sql script found in the rdbms/admin directory provides a good starting point to enforce password complexity. You can also modify the code found in the Database Administrator's Guide. The SQL script provides the following functions or checks:
- The password has a minimum length of four.
- The password is not the same as the username.
- The password has at least one alpha, one numeric, and one punctuation mark character.
- The password is not a simple or obvious word, such as welcome, account, database, or user.
- The password differs from the previous password by at least 3 characters.
Several of these pre-defined checks are easily modified. For example, the minimum length can be increased to six by simply changing 4 to 6 in the code extract shown below.
-- Check for the minimum length of the password IF length(password) < 4 THEN
You can increase the number of simple or obvious words to check by adding more words to the list ("guest" is missing; that would be my first addition to the list). Likewise, you can require more numeric values and more of a difference via very simple modifications to the script. So, the answer to "can you enforce password complexity" is a resounding yes. Keep in mind that what is complex enough for you may be overwhelming for most of your users (at least eight characters, no vowels, at least three numbers, no two alphabetically consecutive letters, ad nauseum). Remember, sometimes "good enough" is good enough, so keep the password scheme simple enough to be, well, good enough.
For Oracle Forms developers, consider using the utlpwdmg.sql script as cut and paste code in a program unit for a logon form. For DBAs, whose users are primarily SQL*Plus users, how would you enable password verification? If you have never used this before, how do you think this is implemented? (Answer appears next time.)
What else could you check for in a password? How about spaces and null values (nulls apply to Forms; SQL*Plus will not let you use a null value).
SQL> alter user scott identified by ''; alter user scott identified by '' * ERROR at line 1: ORA-00988: missing or invalid password(s) SQL> alter user scott identified by null; alter user scott identified by null * ERROR at line 1: ORA-00988: missing or invalid password(s) SQL> alter user scott identified by; alter user scott identified by * ERROR at line 1: ORA-00988: missing or invalid password(s) SQL> alter user scott identified by ""; alter user scott identified by "" * ERROR at line 1: ORA-01741: illegal zero-length identifier
There are many valid reasons why you should use Oracle's built-in password function. It is simple, easy to use, and can be easily beefed up with respect to complexity requirements. If you are managing hundreds of users, then there are hundreds of opportunities for users to forget their passwords, especially if they are infrequent users of SQL*Plus, or if they are required to change passwords every 90 days, and so on. If you find yourself in this situation, it is time to place the authentication burden on the operating system, and that is the subject of the next article in this series. Remember, you are the all-powerful and knowing Oracle DBA. Let the Windows admin deal with lost passwords because as the DBA, you have infinitely more important things to do with your time.