Profiles in oracle with examples

In Oracle Database, a profile is a set of limits on database resources and password policies that you can assign to users. Profiles help manage database security and resource allocation. You can use profiles to control things like password expiration, failed login attempts, CPU usage, and more.

Creating a Profile

To create a profile, use the CREATE PROFILE statement. Here's an example:

CREATE PROFILE example_profile
LIMIT
  SESSIONS_PER_USER          10
  CPU_PER_SESSION            10000
  CPU_PER_CALL               1000
  CONNECT_TIME               60
  IDLE_TIME                  30
  LOGICAL_READS_PER_SESSION  10000
  LOGICAL_READS_PER_CALL     1000
  COMPOSITE_LIMIT            5000000
  PASSWORD_LIFE_TIME         30
  PASSWORD_REUSE_TIME        60
  PASSWORD_REUSE_MAX         5
  PASSWORD_VERIFY_FUNCTION   NULL
  PASSWORD_LOCK_TIME         1
  FAILED_LOGIN_ATTEMPTS      3;

Assigning a Profile to a User

Once a profile is created, you can assign it to a user using the ALTER USER statement. Here's an example:

ALTER USER scott PROFILE example_profile;

Viewing Profiles and Their Limits

You can view existing profiles and their settings using the DBA_PROFILES view. For example:

SELECT * FROM DBA_PROFILES WHERE PROFILE = 'EXAMPLE_PROFILE';

Example Profiles and Their Uses

1. Security-Focused Profile

This profile emphasizes strong security measures, such as frequent password changes and account locking after failed attempts.

CREATE PROFILE security_profile
LIMIT
  PASSWORD_LIFE_TIME         30
  PASSWORD_GRACE_TIME        7
  PASSWORD_REUSE_TIME        365
  PASSWORD_REUSE_MAX         5
  PASSWORD_LOCK_TIME         1
  FAILED_LOGIN_ATTEMPTS      3
  PASSWORD_VERIFY_FUNCTION   ora12c_verify_function;

Assign to a user:

ALTER USER john PROFILE security_profile;

2. Resource Management Profile

This profile limits the CPU time and logical reads a session can consume, which is useful for managing system resources.

CREATE PROFILE resource_profile
LIMIT
  CPU_PER_SESSION            10000
  CPU_PER_CALL               1000
  LOGICAL_READS_PER_SESSION  5000
  LOGICAL_READS_PER_CALL     1000;

Assign to a user:

ALTER USER jane PROFILE resource_profile;

3. Session Management Profile

This profile limits the number of sessions a user can have and the idle time for those sessions.

CREATE PROFILE session_profile
LIMIT
  SESSIONS_PER_USER  5
  IDLE_TIME          10
  CONNECT_TIME       120;

Assign to a user:

ALTER USER mike PROFILE session_profile;

Example: Custom Password Policy Profile

This profile sets up custom password policies, including password complexity and expiration settings.

CREATE PROFILE custom_password_policy
LIMIT
  PASSWORD_LIFE_TIME         60
  PASSWORD_GRACE_TIME        10
  PASSWORD_REUSE_TIME        365
  PASSWORD_REUSE_MAX         10
  PASSWORD_LOCK_TIME         1
  FAILED_LOGIN_ATTEMPTS      5
  PASSWORD_VERIFY_FUNCTION   custom_verify_function;

Assign to a user:

ALTER USER alice PROFILE custom_password_policy;

In the examples above, profiles are created with specific limits and then assigned to users. By doing so, you can ensure that users adhere to your organization's security and resource management policies.

Here is a detailed explanation of each parameter you can set in an Oracle profile:

Resource Parameters

1. SESSIONS_PER_USER

   • Description: Limits the number of concurrent sessions a user can have.
   • Example: SESSIONS_PER_USER 10 allows up to 10 concurrent sessions for the user.

2. CPU_PER_SESSION

   • Description: Limits the CPU time (in hundredths of a second) a session can use.
   • Example: CPU_PER_SESSION 10000 allows 100 seconds of CPU time per session.

3. CPU_PER_CALL

   • Description: Limits the CPU time (in hundredths of a second) a single call can use.
   • Example: CPU_PER_CALL 1000 allows 10 seconds of CPU time per call.

4. CONNECT_TIME

   • Description: Limits the total elapsed time (in minutes) for a session.
   • Example: CONNECT_TIME 60 allows a session to last for 60 minutes.

5. IDLE_TIME

   • Description: Limits the time (in minutes) a session can be idle.
   • Example: IDLE_TIME 30 allows a session to be idle for 30 minutes before being disconnected.

6. LOGICAL_READS_PER_SESSION

   • Description: Limits the number of data blocks read in a session.
   • Example: LOGICAL_READS_PER_SESSION 10000 allows up to 10,000 block reads per session.

7. LOGICAL_READS_PER_CALL

   • Description: Limits the number of data blocks read in a single call.
   • Example: LOGICAL_READS_PER_CALL 1000 allows up to 1,000 block reads per call.

8. PRIVATE_SGA

   • Description: Limits the amount of private space a session can allocate in the SGA (in bytes).
   • Example: PRIVATE_SGA 1024K allocates 1 MB of private space in the SGA.

9. COMPOSITE_LIMIT

   • Description: Limits the total resource cost for a session, calculated as a weighted sum of CPU, connect time, logical reads, and private SGA.
   • Example: COMPOSITE_LIMIT 5000000 sets the total resource cost limit to 5,000,000.

Password Parameters

1. PASSWORD_LIFE_TIME

   • Description: Specifies the number of days a password is valid before it expires.
   • Example: PASSWORD_LIFE_TIME 30 sets the password expiration period to 30 days.

2. PASSWORD_GRACE_TIME

   • Description: Specifies the number of days a user has to change their password after it expires.
   • Example: PASSWORD_GRACE_TIME 7 allows 7 days for a user to change their expired password.

3. PASSWORD_REUSE_TIME

   • Description: Specifies the number of days before a user can reuse a previously used password.
   • Example: PASSWORD_REUSE_TIME 365 prevents password reuse for 365 days.

4. PASSWORD_REUSE_MAX

   • Description: Specifies the number of different passwords a user must use before they can reuse an old password.
   • Example: PASSWORD_REUSE_MAX 5 requires 5 different passwords before an old one can be reused.

5. PASSWORD_VERIFY_FUNCTION

   • Description: Specifies a PL/SQL function to verify password complexity.
   • Example: PASSWORD_VERIFY_FUNCTION ora12c_verify_function uses Oracle's built-in password verification function.

6. PASSWORD_LOCK_TIME

   • Description: Specifies the number of days an account will be locked after the specified number of failed login attempts.
   • Example: PASSWORD_LOCK_TIME 1 locks the account for 1 day after too many failed login attempts.

7. FAILED_LOGIN_ATTEMPTS

   • Description: Specifies the number of failed login attempts allowed before the account is locked.
   • Example: FAILED_LOGIN_ATTEMPTS 3 locks the account after 3 failed login attempts.

8. PASSWORD_GRACE_TIME

   • Description: Specifies the grace period (in days) before a password expires.
   • Example: PASSWORD_GRACE_TIME 7 gives users 7 days to change their password before it expires.

Example Profile Creation

Here's a more comprehensive example that includes all the parameters explained:

CREATE PROFILE comprehensive_profile
LIMIT
  SESSIONS_PER_USER          5
  CPU_PER_SESSION            5000
  CPU_PER_CALL               1000
  CONNECT_TIME               120
  IDLE_TIME                  30
  LOGICAL_READS_PER_SESSION  5000
  LOGICAL_READS_PER_CALL     1000
  PRIVATE_SGA                1024K
  COMPOSITE_LIMIT            1000000
  PASSWORD_LIFE_TIME         60
  PASSWORD_GRACE_TIME        10
  PASSWORD_REUSE_TIME        365
  PASSWORD_REUSE_MAX         10
  PASSWORD_VERIFY_FUNCTION   ora12c_verify_function
  PASSWORD_LOCK_TIME         1
  FAILED_LOGIN_ATTEMPTS      3;

In this example, the profile comprehensive_profile sets various limits on user sessions, CPU usage, logical reads, and password policies. Each parameter is configured to enforce specific resource and security constraints.