Password Profiles - Oracle

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:

1. Password lifetime   Allows a password to exist for a specific period of time
   
2. Grace period   Time at which Database begins to warn users to change their password
   
3. Reuse time/max   Supports password history and forces users to use new passwords
   
4. Failed login attempts   Locks the account if the incorrect password is given after specified number of times
   
5. Account lockout   Disables the account (combined with failed attempts to help prevent brute force attempts into user accounts)
   
6. 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:

sec_mgr@KNOX10g> CREATE PROFILE strong_pwd LIMIT
  2    PASSWORD_LIFE_TIME 90
  3    PASSWORD_GRACE_TIME 15
  4    PASSWORD_REUSE_TIME 180
  5    PASSWORD_REUSE_MAX UNLIMITED
  6    FAILED_LOGIN_ATTEMPTS 5
  7    PASSWORD_LOCK_TIME .5
  8    PASSWORD_VERIFY_FUNCTION is_password_strong;
Profile created.

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.

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.

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:

sec_mgr@KNOX10g> SELECT resource_name, LIMIT
  2    FROM dba_profiles
  3   WHERE PROFILE = 'DEFAULT'
  4     AND resource_type = 'KERNEL';
RESOURCE_NAME                  LIMIT
------------------------------ ------------
COMPOSITE_LIMIT                UNLIMITED
SESSIONS_PER_USER              UNLIMITED
CPU_PER_SESSION                UNLIMITED
CPU_PER_CALL                   UNLIMITED LOGICAL_READS_PER_SESSION      UNLIMITED LOGICAL_READS_PER_CALL         UNLIMITED IDLE_TIME                      UNLIMITED CONNECT_TIME                   UNLIMITED PRIVATE_SGA                    UNLIMITED
9 rows selected.

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)!
  
  

A best practice is to create a profile for each application or class of users in the database. This includes administrators at all levels.