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).
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.
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.
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 isnt 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, '') = 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.