Managing Profiles in Oracle Database 12c

1. Understanding Profiles

In Oracle Database 12c, a profile is a set of resource limits and password management rules that can be assigned to database users. Profiles help in controlling the use of system resources and enforcing password policies, ensuring that users adhere to predefined limits and security standards.

2. Key Concepts

Resource Limits

Resource limits define the maximum amount of CPU time, logical reads, and other system resources that a user can consume. These limits help prevent any single user from monopolizing the database resources, ensuring fair usage across all users.

Example: If you set a CPU time limit of 10 seconds for a user profile, any query executed by a user with that profile will automatically terminate if it exceeds 10 seconds, preventing it from hogging the CPU resources.

Password Management

Password management rules in profiles include settings for password complexity, expiration, and lockout policies. These rules enhance security by ensuring that users create strong passwords and change them periodically.

Example: A profile might require users to create passwords with at least 8 characters, including uppercase, lowercase, numbers, and special characters. It might also mandate that passwords expire every 90 days and lock the account after 3 failed login attempts.

3. Creating and Assigning Profiles

Creating a Profile

To create a profile, you use the CREATE PROFILE statement. This statement allows you to specify resource limits and password management rules for the profile.

Example: The following SQL statement creates a profile named "UserProfile" with a CPU time limit of 10 seconds and a password expiration of 90 days:

CREATE PROFILE UserProfile LIMIT
    CPU_PER_SESSION 10000
    PASSWORD_LIFE_TIME 90;

Assigning a Profile to a User

Once a profile is created, it can be assigned to a user during the user creation process or later using the ALTER USER statement.

Example: The following SQL statement assigns the "UserProfile" to a user named "JohnDoe":

ALTER USER JohnDoe PROFILE UserProfile;

4. Monitoring and Managing Profiles

Monitoring Profile Usage

Oracle provides several dynamic performance views and data dictionary views to monitor profile usage. These views help in tracking resource consumption and ensuring that profiles are effectively managing user activities.

Example: You can use the V$SESSION view to monitor the current session resource usage and verify if any sessions are approaching their resource limits.

Modifying and Dropping Profiles

Profiles can be modified using the ALTER PROFILE statement to update resource limits or password rules. If a profile is no longer needed, it can be dropped using the DROP PROFILE statement.

Example: The following SQL statement modifies the "UserProfile" to increase the CPU time limit to 20 seconds:

ALTER PROFILE UserProfile LIMIT
    CPU_PER_SESSION 20000;

To drop the profile, you would use:

DROP PROFILE UserProfile;

By understanding and effectively managing profiles, you can ensure that your Oracle Database 12c environment is secure, resource-efficient, and compliant with organizational policies.