February 13, 2009

Profiles

Profiles in OracleProfiles were introduced in Oracle 8.Profiles are, set of resource limits, used to limit system resources a user can use. It allows us to regulate the amount of resources used by each database user by creating and assigning profiles to them.Whenever you create a database, one default profile will be created and assigned to all the users you have created. The name of default profile is DEFAULT.Kernel Resources

sessions_per_user -- Maximum concurrent sessions allowed for a user.

cpu_per_session -- Maximum CPU time limit per session, in hundredth of a second.

cpu_per_call -- Maximum CPU time limit per call, in hundredth of a second. Call being parsed, executed and fetched.

connect_time -- Maximum connect time per session, in minutes.

idle_time -- Maximum idle time before user is disconnected, in minutes.

password_reuse_max -- Minimum of different passwords before password can be reused.

password_reuse_time -- Minimum of days before a password can be reused.

password_lock_time -- Number of days an account is locked after failed login attempts.

password_grace_time -- The number of days after the grace period begins, during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.

password_verify_function -- This function will verify the complexity of passwords.

Notes:

If PASSWORD_REUSE_TIME is set to an integer value, PASSWORD_REUSE_MAX must be set to UNLIMITED and vice versa.

If PASSWORD_REUSE_MAX=DEFAULT and PASSWORD_REUSE_TIME is set to UNLIMITED, then Oracle uses the PASSWORD_REUSE_MAX value defined in the DEFAULT profile and vice versa.

If both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are set to DEFAULT, then Oracle uses whichever value is defined in the DEFAULT profile.

The system resource limits can be enforced at the session level or at the call level or both.

If a session exceeds one of these limits, Oracle will terminate the session. If there is a logoff trigger, it won't be executed.In order to track password limits, Oracle stores the history of passwords for a user in USER_HISTORY$.Creating ProfilesIn Oracle, the default cost assigned to a resource is unlimited. By setting resource limits, you can prevent users from performing operations that will tie up the system and prevent other users from performing operations. You can use resource limits for security to ensure that users log off the system and do not leave the sessions connected for long periods of time.Syntax for CREATE and ALTER command:CREATE/ALTER PROFILE profile-name LIMIT[SESSIONS_PER_USER value|UNLIMITED|DEFAULT][CPU_PER_SESSION value|UNLIMITED|DEFAULT][CPU_PER_CALL value|UNLIMITED|DEFAULT][CONNECT_TIME value|UNLIMITED|DEFAULT][IDLE_TIME value|UNLIMITED|DEFAULT][LOGICAL_READS_PER_SESSION value|UNLIMITED|DEFAULT][LOGICAL_READS_PER_CALL value|UNLIMITED|DEFAULT][COMPOSITE_LIMIT value|UNLIMITED|DEFAULT][PRIVATE_SGA value[K|M]|UNLIMITED|DEFAULT][FAILED_LOGIN_ATTEMPTS expr|UNLIMITED|DEFAULT][PASSWORD_LIFE_TIME expr|UNLIMITED|DEFAULT][PASSWORD_REUSE_TIME expr|UNLIMITED|DEFAULT][PASSWORD_REUSE_MAX expr|UNLIMITED|DEFAULT][PASSWORD_LOCK_TIME expr|UNLIMITED|DEFAULT][PASSWORD_GRACE_TIME expr|UNLIMITED|DEFAULT][PASSWORD_VERIFY_FUNCTION function_name|NULL|DEFAULT]

This will verify passwords for length, content and complexity.The function requires the old and new passwords, so password changes can not be done with ALTER USER. Password changes should be performed with the SQL*Plus PASSWORD command or through a stored procedure that requires the correct inputs.

It's possible to restrict a password's format by creating a PL/SQL procedure that validates passwords. It’ll check for minimum width, letters, numbers, or mixed case, or verifying that the password isn't a variation of the username.