Profiles in Oracle Database

User Profiles in Oracle Database

Profiles 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 the 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 the user is disconnected, in minutes.
• logical_reads_per_session -- Maximum blocks read per session.
• logical_reads_per_call -- Maximum blocks read per call.
• private_sga -- Maximum amount of private space in SGA.
• composite_limit -- Sum of cpu_per_session, connect_time, logical_reads_per_session and private_sga.

In order to enforce the above kernel resource limits, init parameter resource_limit must be set to true.

SQL> ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH;

Password limiting functionality is not affected by this parameter.

Password Resources

• failed_login_attempts -- Maximum failed login attempts.
  Query to count failed login attempts
  SQL> SELECT name, lcount FROM USER$ WHERE lcount <> 0;
• password_life_time -- Maximum time a password is valid.
• password_reuse_max -- Minimum of different passwords before the 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 Profiles
In 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]

e.g:
SQL> CREATE PROFILE onsite LIMIT
PASSWORD_LIFE_TIME 45
PASSWORD_GRACE_TIME 12
PASSWORD_REUSE_TIME 3
PASSWORD_REUSE_MAX 5
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 2
CPU_PER_CALL 5000
PRIVATE_SGA 250K
LOGICAL_READS_PER_CALL 2000;

Following is the create profile statement for DEFAULT profile (with all default values):
SQL> CREATE PROFILE "DEFAULT" LIMIT
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
CONNECT_TIME UNLIMITED
IDLE_TIME UNLIMITED
SESSIONS_PER_USER UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
PRIVATE_SGA UNLIMITED
COMPOSITE_LIMIT UNLIMITED
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_LOCK_TIME 1
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_VERIFY_FUNCTION NULL;

SQL> ALTER PROFILE onsite LIMIT FAILED_LOGIN_ATTEMPTS 3;

Assigning Profiles
By default, when you create a user, they are assigned to the DEFAULT profile. If you don't want that
CREATE USER user-name IDENTIFIED BY password PROFILE profile-name;
e.g. SQL> CREATE USER satya IDENTIFIED BY satya PROFILE onsite;

You can alter the existing user's profiles by
ALTER USER user-name PROFILE profile-name ;
e.g. SQL> ALTER USER surya PROFILE offshore;

Dropping Profiles
The syntax for dropping a profile, without dropping users.

DROP PROFILE profile-name
e.g. SQL> DROP PROFILE onsite;

Syntax for dropping a profile with CASCADE clause, all the users who are having this profile will be deleted.

DROP PROFILE profile-name CASCADE
e.g. SQL> DROP PROFILE offshore CASCADE;

Password Verify Function in Oracle
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.

CREATE/ALTER PROFILE profile-name LIMIT PASSWORD_VERIFY_FUNCTION function-name;

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 verify that the password isn't a variation of the username.

If you want to remove this password verify function, assign NULL value to PASSWORD_VERIFY_FUNCTION.
SQL> ALTER PROFILE profile-name LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Related Oracle Profile Views
profile$
profname$
DBA_PROFILES
RESOURCE_COST     (shows the unit cost associated with each resource)
USER_RESOURCE_LIMITS (each user can find information on his resources and limits)


Other Oracle DB Articles:  Block Change Tracking file     ORADEBUG tool