Securing Your Oracle™ Database With User Profiles

User profiles define a set of limitations for resources and passwords. By default, every user is assigned the "DEFAULT" profile, which by default has no limitations on resources or password use. By altering the default profile, you can set limits for all users in the database. You can also create customized profiles, with limitations for a particular set of users.

Profiles can be used to set limits for:

• The number of sessions per user
• CPU use per session
• CPU use per call
• Connect time
• Idle time
• Logical reads per session
• The amount of private SGA a session can allocate
• A composite resource limit
• Password lifetime
• Password reuse
• Failed login attempts
• Account lockout time for failed login attempts
• Password verification function -- a stored procedure that creates rules for a valid password

Limiting system resources can protect your system from a denial of service attack. It can also protect you from "accidents", where a query might consume 100% of available resources. These settings are very useful for applications that use individual database users for authentication. For applications that use a single account for all database activity, setting limits could prove disastrous. Limiting connect time and idle time are a good idea if the application is designed in such a way that each user creates a session. If the application is designed to forward all requests to a single connection that is created at application startup, limiting connect and idle time would also be disastrous. It is important to understand how the application works before setting these limits.

Setting password limitations is a good idea, no matter how the application works. Most organizations have policies about password lifetime and reuse, and the profile allows you to comply with those policies. The following command gives an example of setting passwords to expire in 90 days:

SQL> alter profile default limit password_life_time 90;

By default, the password validation is not enabled in the database. A script called utlpwdmg.sql can be found in the $ORACLE_HOME/rdbms/admin directory that sets limits for the DEFAULT profile. It sets password lifetime to 60 days, password grace time (the time between expiration and lockout) to 10 days, password reuse time to 1800 days and failed login attempts to 3 before locking the account for 1 minute. The script also creates a very rudimentary password validation procedure that requires a minimum of 4 characters, with at least one alpha character, one numeric character and one special character.
View script

You can alter the script to reflect your organizations password policies. The following script has been customized to meet the organizations policies. The verify_function now requires a minimum of 8 characters.
View script

Privileged users should have stricter limits set. Create a new profile, PRIVUSERS for any account with SYS or DBA privileges.

SQL> create profile privusers limit password_life_time 60;

Use the ALTER PROFILE command to update resource limits.

SQL> alter profile privusers limit failed_login_attempts 5;

To assign the new profile, use the ALTER USER command:

SQL> alter user sys profile privusers;

The following script requires privileged users to have a minimum password length of 10 characters, and sets limits on password lifetime and reuse.
View script

You can query the DBA_PROFILES view to obtain information about a profile:
SQL> select resource_name, limit from dba_profiles where profile = 'DEFAULT'

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
FAILED_LOGIN_ATTEMPTS            UNLIMITED
PASSWORD_LIFE_TIME               UNLIMITED
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               UNLIMITED
PASSWORD_GRACE_TIME              UNLIMITED

16 rows selected.

So, what what are PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX? These two resources work together to limit the number of times a password may be used.

If PASSWORD_REUSE_TIME is unlimited, and PASSWORD_REUSE_MAX has a numeric value, passwords may not be reused.

If PASSWORD_REUSE_MAX is unlimited, and PASSWORD_REUSE_TIME has a numeric value, passwords may not be reused.

If PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME both have numeric values, passwords may be reused the number of times defined by PASSWORD_REUSE_MAX, but only after waiting the number of days defined by PASSWORD_REUSE_TIME.

Setting FAILED_LOGIN_ATTEMPTS is a brilliant idea for end user accounts, and a terrible idea for accounts that are used for automated authentication of applications. When set, this parameter limits the number of failed login attempts that can be made before the account is locked for the value of the PASSWORD_LOCK_TIME setting. For example, if FAILED_LOGIN_ATTEMPTS is set to 5, and a user makes 5 unsuccessful attempts to login, the account will be locked to prevent a possible brute force attack. If the user has made an honest mistake, they will contact you and ask you to unlock the account.

Setting FAILED_LOGIN_ATTEMPTS for accounts that are used to authenticate web applications could result in a Denial of Service attack. The attacker would only need to know the account name. Attempting to login 5 times with a bad password would disable the application for the duration of the PASSWORD_LOCK_TIME setting. Don't make it easy for an attacker. Create a special profile for application accounts that doesn't lock the account for failed login attempts, but does require a longer, stronger password. Be sure to enable auditing of failed login attempts, and monitor the DBA_AUDIT_LOG for attempted break-ins.