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
In Closing
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.