Oracle Passwords

Oracle Passwords

The Oracle Database stores user passwords in the data dictionary. For database authenticated users, the values stored aren’t actually the plaintext passwords themselves but the password verifiers. Password verifiers are hashed (see Chapter 13 for more details on hashing) representations of plaintext passwords. The value is stored in a hexadecimal representation (numbers 0–9 and letters A–F).

The authentication process is performed by computing a password verifier for the plaintext password a user has submitted for authentication and comparing the resulting value with the one stored in the data dictionary. If they match, the user has supplied the same password and is authenticated.

Application Password Authentication Using Oracle’s Native Password Store

Application user authentication is an important step to ensuring security for database applications. There are times when the application requires authentication but a default mechanism isn’t provided. There are three possible methods for implementing your own user authentication for your application. First, you could build, maintain, and/or synchronize your own password repository and authentication scheme. You’ll see an example of how to implement a password authentication solution in Chapter 13, which discusses the use of the new DBMS_CRYPTO package.

Second, assuming the application users are also database users, the application could authenticate users by trying to connect to the database as the respective user(s). This is a bad alternative because it can be costly—from a time and performance perspective—to create and destroy database connections just to authenticate.

The final method, which also assumes the application users are database users, utilizes the database’s internal password store. Unfortunately, Oracle supplies no password verifier program for developers to use, so you must build your own interface.

You can do this with a simple trick, commonly referred to as identified by values, that uses a syntactical variation of the ALTER USER DDL. The algorithm is similar to the one the database uses to authenticate its users. You simply need to compute a password verifier for the plaintext password a user submits and compare that value to the one stored in the database.

The following function is based on a program originally written by Tom Kyte. To start the function, obtain the user’s current password verifier from the data dictionary:

sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION is_auth_password   
2    p_username IN VARCHAR2, 
3    p_password IN VARCHAR2)  
4    RETURN BOOLEAN   
5  AS   
6      l_orig_password_verifier dba_users.PASSWORD%TYPE; 
7      l_new_password_verifier dba_users.PASSWORD%TYPE; 
8  BEGIN   
9     SELECT PASSWORD   
10      INTO l_orig_password_verifier   
11      FROM dba_users   
12    WHERE username = UPPER (p_username); 
 

Next, alter the user’s password and set it to the password you want to verify. When you do this, the database recomputes the password verifier based on this password. The result is stored in the database dictionary:

13     EXECUTE IMMEDIATE    'alter user ' 
14                       || p_username   
15                       || ' identified by ' 
16                       || p_password; 
 

Select this second verifier out and compare it to the original that you’ve stored. If they match, the passwords must have been the same.

17     SELECT PASSWORD   
18       INTO l_new_password_verifier  
19       FROM dba_users   
20     WHERE username = UPPER (p_username); 
 

This works well when the passwords are identical. However, the problem is that if the passwords don’t match, you’ve changed the user’s password (perhaps to one that they don’t know). To resolve this, set the password back to its original value. Since you don’t have the original plaintext password, you can’t use the traditional ALTER USER syntax. Instead, reset the password by issuing an ALTER USER <username> IDENTIFIED BY VALUES ‘<original password verifier>’. Passing the original password verifier in single quotes after the values clause resets the password back to the user’s original password. The database sees the word “values” and doesn’t recompute the password verifier, but it stores the value specified in the quotes directly in the password column for the user.

21     EXECUTE IMMEDIATE     'alter user ' 
22                       || p_username   
23                       || ' identified by values ''' 
24                       || l_orig_password_verifier   
25                       || ''''; 
26     RETURN l_orig_password_verifier = 
27                              l_new_password_verifier; 
28   END; 
29 /     Function created. 
 

To test the program, check the Boolean return value of the function:

sec_mgr@KNOX10g> BEGIN      
  2    IF (is_auth_password ('scott', 'tiger') = TRUE) 
  3    THEN     
  4      DBMS_OUTPUT.put_line ('scott/tiger is valid'); 
  5    ELSE     
  6      DBMS_OUTPUT.put_line    
  7                        ('scott/tiger is NOT valid'); 
  8    END IF;  
  9  
  10   IF (is_auth_password ('scott', 'lion') = TRUE) 
  11   THEN    
  12     DBMS_OUTPUT.put_line ('scott/lion is valid'); 
  13    ELSE    
  14     DBMS_OUTPUT.put_line    
  15                         ('scott/lion is NOT valid'); 
  16    END IF; 
  17  END; 
  18 /   scott/tiger is valid   scott/lion is NOT valid   

This function allows you to authenticate users against the database password store without having to know or manage the user’s actual password.

Checking for Weak or Default Passwords

Passwords are often the weak link in the security chain. A poorly chosen password, or well-known default password that has not been changed, is one of the greatest security risks to a database. To help manage this risk use the following program, which compares a list of known usernames and password verifiers to the users and verifiers actually being used in the database. The list is created by a helper program.

To start, a table is created that stores usernames, their plaintext passwords, and the respective computed password verifiers:

sec_mgr@KNOX10g> CREATE TABLE passwords  
  2 ( 
  3 username VARCHAR2(30), 
  4 passwd VARCHAR2(30), 
  5 verifier VARCHAR2(30) 
  6 )  
  7 / 

Table created. 

sec_mgr@KNOX10g> ALTER TABLE PASSWORDS ADD (  
  2 CONSTRAINT PWD_PK PRIMARY KEY (PASSWD, USERNAME)); 

Table altered. 

sec_mgr@KNOX10g> CREATE INDEX VERIFIER ON PASSWORDS  
  2 (USERNAME, VERIFIER); 

Index created. 
 

To populate the table, a procedure is created that utilizes the identified by values clause introduced in the IS_AUTH_PASSWORD function shown earlier. The procedure takes a password parameter. The program iterates through the DBA_USERS view and sets the passwords for all the users to the value passed as the parameter. The program then inserts the resulting password verifier into the PASSWORDS table. When the parameter is null, the password is set to the username. This has the benefit of allowing you to check for passwords that are the same as the username.

sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE populate_passwords_tab (  
  2     p_password IN VARCHAR2 DEFAULT NULL) 
  3 AS 
  4     l_new_password_verifier   dba_users.PASSWORD%TYPE; 
  5     l_password                dba_users.PASSWORD%TYPE 
  6                                := UPPER (p_password); 
  7  BEGIN 
  8     FOR rec IN (SELECT username, password 
  9                   FROM dba_users) 
  10    LOOP 
  11      IF (p_password IS NULL) 
  12      THEN 
  13       -- password is either passed as parameter  
  14       -- or set to user's name 
  15       l_password := rec.username; 
  16     END IF; 
  17  
  18       -- create new password verifier 
  19       EXECUTE IMMEDIATE     'alter user ' 
  20                         || rec.username 
  21                         || ' identified by ' 
  22                         || l_password; 
  23       -- retrieve new verifier 
  24       SELECT password 
  25         INTO l_new_password_verifier 
  26         FROM dba_users 
  27       WHERE username = rec.username; 
  28       -- insert value into passwords table 
  29       INSERT INTO passwords 
  30           VALUES (rec.username, 
  31                   l_password, 
  32                   l_new_password_verifier); 
  33       -- set password back to its original value 
  34       EXECUTE IMMEDIATE       'alter user ' 
  35                          || rec.username 
  36                          || ' identified by values ''' 
  37                          || rec.password 
  38                          || ''''; 
  39     END LOOP; 
  40   END; 
  41 / 
 
Procedure created. 
 

Next, execute the above procedure to seed the table first with usernames equal to passwords. Since the procedure modifies user passowords, you should consider running this on a non-production database so this process doesn’t interfere with your production applications. Then try some common passwords associated with the default Oracle accounts (there may be additional common and default usernames within your organization). Any standard applications you’ve installed on Oracle may also carry default schemas and well-known default passwords, so you should consider all default schemas and their passwords, too. Oracle Metalink (http://metalink.oracle.com) lists some of the default usernames and passwords in Note:160861.1.

sec_mgr@KNOX10g> -- sets all passwords to that of user's name 
sec_mgr@KNOX10g> EXEC populate_passwords_tab 
 
PL/SQL procedure successfully completed. 
 
sec_mgr@KNOX10g> -- check for manager, a common password for DBA accounts 
sec_mgr@KNOX10g> EXEC populate_passwords_tab ('manager') 
 
PL/SQL procedure successfully completed. 
 
sec_mgr@KNOX10g> -- check for your company's name here 
sec_mgr@KNOX10g> EXEC populate_passwords_tab ('oracle')  
PL/SQL procedure successfully completed. 

sec_mgr@KNOX10g> -- SCOTT's default password 
sec_mgr@KNOX10g> EXEC populate_passwords_tab ('tiger') 
 
PL/SQL procedure successfully completed. 
 
sec_mgr@KNOX10g> -- SYS' default password 
sec_mgr@KNOX10g> EXEC populate_passwords_tab ('change_on_install') 
 
PL/SQL procedure successfully completed. 
 
sec_mgr@KNOX10g> -- common password people use 
sec_mgr@KNOX10g> EXEC populate_passwords_tab ('password') 
 
PL/SQL procedure successfully completed. 
 
sec_mgr@KNOX10g> COMMIT ; 
 
Commit complete. 
 

Now that you have a data set of precomputed password verifiers, you’re ready to run checks against your production database.

Note 

Once the password table has been created, the same data can be used against any Oracle Database because the password verifiers for the usernames are always the same in every Oracle Database.

For the procedure that actually performs the checking, simply iterate through the database users comparing the password verifier computed there with the one actually stored in the PASSWORDS table. When you find a match, print the matched value and the account’s status:

sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE check_passwords  
  2  AS 
  3  BEGIN 
  4   FOR rec IN (SELECT username, 
  5                      PASSWORD, 
  6                      account_status 
  7               FROM dba_users)  
  8  LOOP 
  9    FOR irec IN (SELECT * 
 10                 FROM passwords 
 11                WHERE username = rec.username 
 12                 AND verifier = rec.PASSWORD) 
 13  LOOP 
 14    DBMS_OUTPUT.put_line 
 15               ('------------------------------'); 
 16    DBMS_OUTPUT.put_line (  'Password for ' 
 17                          || rec.username  
 18                          || ' is ' 
 19                          || irec.passwd); 
 20    DBMS_OUTPUT.put_line 
 21                         (  'Account Status is ' 
 22                          || rec.account_status); 
 23    END LOOP; 
 24  END LOOP; 
 25 END; 
 26 / 
 
Procedure created. 
 
sec_mgr@KNOX10g> set timing on 
sec_mgr@KNOX10g> SET serveroutput on 
sec_mgr@KNOX10g> EXEC check_passwords 
--Password for CTXSYS is CHANGE_ON_INSTALL 
Account Status is LOCKED 
--Password for DIP is DIP 
Account Status is LOCKED 
--Password for OLAPSYS is MANAGER 
Account Status is LOCKED 
 
PL/SQL procedure successfully completed. 
 
Elapsed: 00:00:00.03 
 

The output from the procedure’s execution was truncated in the previous output. It simply shows that the procedure was successful in finding passwords (not all successful findings are printed). Because changing default passwords is a best practice, this program can assist you in ensuring your Oracle Databases are compliant with best practices and are thus better secured.

Impossible Passwords

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. It’s 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.

Managing and Ensuring Good Passwords

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.

Password Complexity

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:

  • Password isn’t the same as the username

  • Password contains at least one digit

  • Password is greater than some specified length

  • Password isn’t the same as the old password

  • 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.

Password Profiles

Oracle allows you to create Password Profiles that govern the behavior of the database with respect to passwords and authentication. To do this, create a profile with values set for the attributes you wish to use. The profile then can be enforced on your users. Oracle supports the following attributes for password profiles:

  • Password lifetime Allows a password to exist for a specific period of time

  • Grace period Time at which Database begins to warn users to change their password

  • Reuse time/max Supports password history and forces users to use new passwords

  • Failed login attempts Locks the account if the incorrect password is given after specified number of times

  • Account lockout Disables the account (combined with failed attempts to help prevent brute force attempts into user accounts)

  • Password Verify Function Defines the password complexity function that will be called when the user changes the password

Chapter 7 of the Oracle Database Security Guide gives more detailed explanations of these attributes. A sample profile that sets values for these attributes and assigns the complexity function defined previously would look as follows:

sec_mgr@KNOX10g> CREATE PROFILE strong_pwd LIMIT  
 2   PASSWORD_LIFE_TIME 90 
 3   PASSWORD_GRACE_TIME 15 
 4   PASSWORD_REUSE_TIME 180 
 5   PASSWORD_REUSE_MAX UNLIMITED 
 6   FAILED_LOGIN_ATTEMPTS 5 
 7   PASSWORD_LOCK_TIME .5 
 8   PASSWORD_VERIFY_FUNCTION is_password_strong; 
 
Profile created. 
 

Now assign this profile to your users. Testing this on the user SCOTT yields the following results:

sec_mgr@KNOX10g> -- assign profile 
sec_mgr@KNOX10g> ALTER USER scott PROFILE strong_pwd; 
 
User altered. 
 
sec_mgr@KNOX10g> -- test profile 
sec_mgr@KNOX10g> -- reset scott's password 
sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY scott; 
ALTER USER scott IDENTIFIED BY scott 
*  
ERROR at line 1: 
ORA-28003: password verification for the specified password failed 
ORA-20001: Password same as user name 
 
sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY manager; 
ALTER USER scott IDENTIFIED BY manager 
*  
ERROR at line 1: 
ORA-28003: password verification for the specified password failed 
ORA-20002: Password is too predictable 
 
sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY nodigit; 
ALTER USER scott IDENTIFIED BY nodigit 
*  
ERROR at line 1: 
ORA-28003: password verification for the specified password failed 
ORA-20003: Password needs at least one digit  
sec_mgr@KNOX10g> -- since this is not SCOTT, old password is null 
sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY tiger;  
ALTER USER scott IDENTIFIED BY tiger 
*  
ERROR at line 1: 
ORA-28003: password verification for the specified password failed 
ORA-20003: Password needs at least one digit 
 
sec_mgr@KNOX10g> ALTER USER scott IDENTIFIED BY short1; 
ALTER USER scott IDENTIFIED BY short1 
*  
ERROR at line 1: 
ORA-28003: password verification for the specified password failed 
ORA-20005: Password is too short 
 

Normally, after creating a password profile, you’ll force your users to change their passwords to ensure all passwords being used comply with the profile. To do this, you expire their existing password. Upon their next login, the database prompts them to reset their password. The new password is checked against the complexity routine and the other password profile values will also be enforced.

When administering this for SCOTT, set the new password to tiger, which you can’t see when looking at the output below. You may have noticed in the previous example that the complexity function didn’t indicate that the new tiger password matched the old password. The following output shows a different behavior when SCOTT changes his password. The complexity routine now informs him that the new password is the same as the old password. Note that this is done for security. If a user other than SCOTT received this message, then the database would have divulged the user’s password.

sec_mgr@KNOX10g> ALTER USER scott PASSWORD EXPIRE; 
 
User altered. 
 
sec_mgr@KNOX10g> conn scott/tiger   
ERROR: 
ORA-28001: the password has expired      
Changing password for scott   
New password: 
Retype new password: 
ERROR: 
ORA-28003: password verification for the specified password failed   
ORA-20004: Password has to be different than old password      

Password unchanged   
Warning: You are no longer connected to ORACLE.  

Keep the Password Policies Practical

A password profile is a great way to ensure that good password management practices are being used. Once again, however, you have to balance security with usability. While using password profiles is generally a good idea, it can backfire. For example, forcing users to choose a new password each week (that is, expiring passwords too frequently) may in fact force the user to use easy passwords or worse, write down their passwords.

As another example, you may decide after three failed logins, you’ll lock the user account for a day. There are unintended consequences to this. The failed login and account locking can aid someone launching a denial of service (DoS) attack. The attack is made easy because a malicious person can intentionally lock all the database accounts by simply providing an incorrect password for each database user.