Securing the Database - Checking for Weak or Default Passwords
(Page 4 of 9 )
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 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.
Next: Impossible Passwords >>
More Oracle Articles
More By McGraw-Hill/Osborne
|
 This article 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). Check it out at your favorite bookstore. Buy this book now.
|
|
/ 9 
