If you work with Oracle databases, you will want to know how to secure them. This article focuses on a number of steps you can take, representing the best practices used in organizations today, to secure an Oracle database. It is excerpted from chapter 2 of the book Effective Oracle Database 10g Security by Design, written by David C. Knox (McGraw-Hill/Osborne, 2004; ISBN: 0072231300).
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:
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.
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.