Impossible Passwords - Oracle

The Oracle database user's password verifier is stored as a 16-character hexadecimal string. If you query the DBA_USERS view, you'll see some passwords that aren't hexadecimals. For example, there's a database user named ANONYMOUS with a password of anonymous. How can that be? If you created a user by the name of ANONYMOUS with a password of anonymous, the password verifier wouldn't say anonymous; it would be a hexadecimal representation of a hash of the password, not a plaintext string. You can check the passwords table previously created to verify this:

sec_mgr@KNOX10g> select * from passwords
2    where username = 'ANONYMOUS'
  3    and pwd = 'ANONYMOUS';
USERNAME   PWD                  VERIFIER
---------- ---------------- ---------------- ANONYMOUS  ANONYMOUS       FE0E8CE7C92504E9

The reason the string anonymous is present, as opposed to the verifier you see above, is that  the user wasn't created with the standard CREATE USER syntax but with the identified by values clause, as shown here:

SQL> CREATE USER anonymous IDENTIFIED BY VALUES 'anonymous';
User created.
SQL> select username, password
  2  from dba_users
  3 where username = 'ANONYMOUS'
  4 /
USERNAME            PASSWORD
------------------- ------------------------
ANONYMOUS           anonymous

This is a simple trick you can use to ensure users don't log in to an account. Its similar to creating a very strong password, but it's better because you can't log in to the account with anonymous or any other string in the universe!

The reason that no password is possible is because Oracle, on authentication, will compute the password verifier, which will be some 16-character hexadecimal string. This is compared with the one stored for the user. Because you know the password verifiers are stored in hexadecimal format, any values outside of the hexadecimal set (0--9, A--F) will not match the one computed. The result: there is no password the user can provide that will allow them to log in.

Anytime you are creating a database schema to which no one should connect, you should use an impossible password. The account also should be locked and privileges to connect to the database should not be given.

Passwords are the most prevalent form of authentication to Oracle Databases. Oracle provides the ability to enforce the choice of good, strong passwords through the use of password complexity routines. Oracle also provides a way to ensure good password management practices are also being followed through password profile enforcement.

Oracle supports user-defined password complexity routines that allow you to validate the strength of passwords when they are set. Password complexity routines are critical to ensuring that password best practices are obeyed. The complexity routine technically implements the official password policy in your organization (assuming you have such a policy, and you should). You can check for many things within the routine. The biggest exception is case-sensitivity. Database authenticated user passwords are case insensitive. Here are a few common best practice checks you can administer within the complexity routine:

1. Password isn't the same as the username
   
2. Password contains at least one digit
   
3. Password is greater than some specified length
   
4. Password isnt the same as the old password
   
5. Password isn't an easy to guess word, such as manager, oracle, or your company's name

The function that administers the password check has to be implemented in the SYS schema. The password complexity function returns a Boolean value. The value TRUE means the password is okay. However, a good trick is to raise an exception in the function to notify the user of exactly what condition failed during their password change. Otherwise, they will get a generic error. A sample function that implements some of the above checks would look as follows:

sys@KNOX10g> CREATE OR REPLACE FUNCTION is_password_strong (
  2    p_username      VARCHAR2,
  3    p_new_password  VARCHAR2,
  4    p_old_password  VARCHAR2)
  5    -- return TRUE if password is strong enough
  6  RETURN BOOLEAN
  7  AS
  8   l_return_val BOOLEAN := TRUE;
  9  BEGIN
 10    -- Check to be sure password is not the same as username
 11   IF UPPER (p_new_password) = UPPER (p_username)
 12   THEN
 13     l_return_val := FALSE;
 14     raise_application_error
 15                     (-20001,
 16                      'Password same as user name');
 17   END IF;
 18
 19   -- force user to change password to something new
 20   IF UPPER (p_new_password) =
 21                             UPPER (p_old_password)
 22   THEN
 23     l_return_val := FALSE;
 24     raise_application_error
 25       (-20004,
 26        'Password has to be different than old password');
 27   END IF;
 28
 29   -- Check for list of predictable passwords
 30   IF LOWER (p_new_password) IN
 31        ('manager',
 32         'change_on_install',
 33         'oracle',
 34         'password')
 35   THEN
 36     l_return_val := FALSE;
 37     raise_application_error
 38                    (-20002,
 39                     'Password is too predictable');
 40   END IF;
 41
 42   -- make sure password contains at least one digit
 43   IF (regexp_like (p_new_password, '[0123456789]') =
 44       FALSE)
 45   THEN
 46     l_return_val := FALSE;
 47     raise_application_error
 48              (-20003,
 49               'Password needs at least one digit');
 50   END IF;
 51
 52   -- make sure password is at least six characters
 53   IF LENGTH (p_new_password) <= 6
 54   THEN
 55     l_return_val := FALSE;
 56     raise_application_error
 57                          (-20005,
 58                           'Password is too short');
 59   END IF;
 60
 61   RETURN l_return_val;
 62 END;
 63 /
Function created.

To enforce the password complexity routine, assign it to a Password Profile and then assign the profile to the user(s). Examples are shown in the following section.