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).
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:
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, youll 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.
Limiting Database Resources
Whether intentional or malicious, a computer's resources can be monopolized without much effort. Generally, when this is done maliciously, it is known as a denial of service (DoS) attack.
DoS attacks are easy to implement and hard to defend against. The defense challenge arises from the fact that there are numerous ways to trigger such attacks. The result is simple: exhaust computing resources to the point that the database can no longer provide adequate service. Fortunately, there are some actions you can take in the database to mitigate the risk of DoS attacks.
In addition to the password profile capabilities, Oracle supports the use of resource profiles to limit the use of precious database resources. Resource limits help ensure that the application or user doesn't intentionally, or inadvertently, take over the database and system's resources. You can view the various resources that can be managed as well as their values by querying the DBA_PROFILES view:
Notice that the default values are all set to unlimited. A best practice is to actually define as many of these values as possible. Some general guidelines on the parameters are as follows:
Set the SESSIONS_PER_USER to the size of your application server connection pool. If you aren't using a connection pool (or have no idea what that means), then set the value to something reasonable. You should consider that an application may lock or a computer may freeze with the connection open, so a value of one may be too restrictive.
It's possible to create a denial-of-service attack by utilizing the CREATE SESSION privilege and connecting to the database over and over until the database server exhausts all memory. Setting this parameter helps to ensure this will not happen.
IDLE_TIME can be set to help ensure that users don't leave a connected terminal in the database while they step out for a lunch break. If their machine is left unlocked, then someone can simply walk up and start accessing the user's data without having to worry about breaking passwords or subverting privileges. This value is more applicable to client-server applications than to web applications; if the latter is using a connection pool, the server shouldn't disconnect the pooled connections.
CPU_PER_CALL is a hard parameter to guess, but it helps to ensure the availability of the database. Often CPU monopolization occurs not by a malicious user, but by a bad programmer who inadvertently sends the database into a recursive loop (I'm speaking from experience here)!
Refer to the “Create Profile” section in the Oracle Database SQL Reference 10g document for specific definitions on all the settings. Setting the profile parameters to the logical and correct values may take a few tries before the best values are selected. Start with a least privilege mentality by setting the values very conservatively. If you find that you need to relax a few privileges for legitimate reasons, do so only after you have determined the values need to be relaxed.
A best practice is to create a profile for each application or class of users in the database. This includes administrators at all levels.