Checking for Weak or Default Passwords - Oracle

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.

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 accounts status:

sec_msr@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.