Configuring User Resource Limits

A resource limit defines the amount of system resources that are available for a user.

About User Resource Limits

You can set limits on the amount of system resources available to each user as part of the security domain of that user.

By doing so, you can prevent the uncontrolled consumption of valuable system resources such as CPU time.

This resource limit feature is very useful in large, multiuser systems, where system resources are very expensive. Excessive consumption of these resources by one or more users can detrimentally affect the other users of the database. In single-user or small-scale multiuser database systems, the system resource feature is not as important, because user consumption of system resources is less likely to have a detrimental impact.

You manage user resource limits by using Database Resource Manager. You can set password management preferences using profiles, either set individually or using a default profile for many users. Each Oracle database can have an unlimited number of profiles. Oracle Database allows the security administrator to enable or disable the enforcement of profile resource limits universally.

Setting resource limits causes a slight performance degradation when users create sessions, because Oracle Database loads all resource limit data for each user upon each connection to the database.

Related Topics

Types of System Resources and Limits

You can limit several types of system resources, including CPU time and logical reads, at the session level, call level, or both.

Limits to the User Session Level

When a user connects to a CDB or PDB, a session is created. Sessions use CPU time and memory, on which you can set limits.

You can set several resource limits at the session level. If a user exceeds a session-level resource limit, then Oracle Database terminates (rolls back) the current statement and returns a message indicating that the session limit has been reached. At this point, all previous statements in the current transaction are intact, and the only operations the user can perform are COMMIT, ROLLBACK, or disconnect (in this case, the current transaction is committed). All other operations produce an error. Even after the transaction is committed or rolled back, the user cannot accomplish any more work during the current session.

Limits to Database Call Levels

Each time a user runs a SQL statement, Oracle Database performs several steps to process the statement.

During the SQL statement processing, several calls are made to the database as a part of the different execution phases. To prevent any one call from using the system excessively, Oracle Database lets you set several resource limits at the call level.

If a user exceeds a call-level resource limit, then Oracle Database halts the processing of the statement, rolls back the statement, and returns an error. However, all previous statements of the current transaction remain intact, and the user session remains connected.

Limits to CPU Time

When SQL statements and other calls are made to an Oracle CDB or PDB, CPU time is necessary to process the call.

Average calls require a small amount of CPU time. However, a SQL statement involving a large amount of data or a runaway query can potentially use a large amount of CPU time, reducing CPU time available for other processing.

To prevent uncontrolled use of CPU time, you can set fixed or dynamic limits on the CPU time for each call and the total amount of CPU time used for Oracle Database calls during a session. The limits are set and measured in CPU one-hundredth seconds ( 0.01 seconds) used by a call or a session.

Limits to Logical Reads

Input/output (I/O) is one of the most expensive operations in a database system.

SQL statements that are I/O-intensive can monopolize memory and disk use and cause other database operations to compete for these resources.

To prevent single sources of excessive I/O, you can limit the logical data block reads for each call and for each session. Logical data block reads include data block reads from both memory and disk. The limits are set and measured in number of block reads performed by a call or during a session.

Limits to Other Resources

You can control limits for user concurrent sessions and idle time.

Limits to other resources are as follows:

Values for Resource Limits of Profiles

Before you create profiles and set resource limits, you should determine appropriate values for each resource limit.

You can base the resource limit values on the type of operations a typical user performs. For example, if one class of user does not usually perform a high number of logical data block reads, then use the ALTER RESOURCE COST SQL statement to set the LOGICAL_READS_PER_SESSION setting conservatively.

Usually, the best way to determine the appropriate resource limit values for a given user profile is to gather historical information about each type of resource usage. For example, the database or security administrator can use the AUDIT SESSION clause to gather information about the limits CONNECT_TIME, LOGICAL_READS_PER_SESSION.

In an Oracle Data Guard environment, an active standby database is opened in read-only mode. This allows user connections on it in the same way as on a primary database. Hence, all the password resource-related limits of a given user profile will work independently between them, except for the ones that imply or require a user password change in the standby database; this task cannot be performed in a database that is opened in read-only mode.

You can gather statistics for other limits using the Monitor feature of Oracle Enterprise Manager (or SQL*Plus), specifically the Statistics monitor.

Managing Resources with Profiles

A profile is a named set of resource limits and password parameters that restrict database usage and instance resources for a user.

About Profiles

A profile is a collection of attributes that apply to a user.

The profile is used to enable a single point of reference for multiple users who share these attributes.

You should assign a profile to each user. Each user can have only one profile, and creating a new one supersedes an earlier assignment.

You can create and manage user profiles only if resource limits are a requirement of your database security policy. To use profiles, first categorize the related types of users in a database. Just as roles are used to manage the privileges of related users, profiles are used to manage the resource limits of related users. Determine how many profiles are needed to encompass all categories of users in a database and then determine appropriate resource limits for each profile.

User profiles in Oracle Internet Directory contain attributes pertinent to directory usage and authentication for each user. Similarly, profiles in Oracle Label Security contain attributes useful in label security user administration and operations management. Profile attributes can include restrictions on system resources. You can use Database Resource Manager to set these types of resource limits. Profiles are useful for the administration and operations performed in the container databases (CDBs) and application containers, as well as their associated pluggable databases (PDBs). For both CDB and application containers, if you define a common profile, then the profile applies to the entire container and not outside this container. If you create a local profile, then it applies to that PDB only.

Profile resource limits are enforced only when you enable resource limitation for the associated database. Enabling this limitation can occur either before starting the database (using the RESOURCE_LIMIT initialization parameter) or while it is open (using the ALTER SYSTEM statement).

Though password parameters reside in profiles, they are unaffected by RESOURCE_LIMIT or ALTER SYSTEM and password management is always enabled. In Oracle Database, Database Resource Manager primarily handles resource allocations and restrictions.

Any authorized database user can create, assign to users, alter, and drop a profile at any time (using the CREATE USER or ALTER USER statement). Profiles can be assigned only to users and not to roles or other profiles. Profile assignments do not affect current sessions; instead, they take effect only in subsequent sessions.

To find information about current profiles, query the DBA_PROFILES view.

See Also: Oracle AI Database Administrator’s Guide for detailed information about managing resources

ORA_CIS_PROFILE User Profile

The ORA_CIS_PROFILE user profile is designed for Center for Internet Security (CIS) compliance.

The ORA_CIS_PROFILE user profile addresses CIS requirements such as the need for a password complexity function, maximum failed login attempts, reuse time, and other requirements. The definition for this profile is as follows:

CREATE PROFILE ORA_CIS_PROFILE
   sessions_per_user 10
   failed_login_attempts 5
   password_life_time 90
   password_reuse_time 365
   password_reuse_max 20
   password_lock_time 1
   password_grace_time 5
   inactive_account_time 120
   password_verify_function ora12c_verify_function

ORA_STIG_PROFILE User Profile

The ORA_STIG_PROFILE user profile complies with the Security Technical Implementation Guide’s requirements.

The ORA_STIG_PROFILE user profile addresses STIG requirements such as the need for a password complexity function, maximum failed login attempts, reuse time, and other requirements. The definition for this profile is as follows:

CREATE PROFILE ORA_STIG_PROFILE
  password_life_time        35
  password_grace_time       0
  password_reuse_time       175
  password_reuse_max        5
  failed_login_attempts     3
  password_lock_time        unlimited
  inactive_account_time     35
  idle_time                 15
  password_verify_function  ora12c_stig_verify_function;

Creating a Profile

A profile can encompass limits for a specific category, such as limits on passwords or limits on resources.

To create a profile, you must have the CREATE PROFILE system privilege. To find all existing profiles, you can query the DBA_PROFILES view.

Use the CREATE PROFILE statement to create a profile.

For example, to create a profile that defines password limits:

CREATE PROFILE password_prof LIMIT
  FAILED_LOGIN_ATTEMPTS 6
  PASSWORD_LIFE_TIME 60
  PASSWORD_REUSE_TIME 60
  PASSWORD_REUSE_MAX 5
  PASSWORD_LOCK_TIME 1/24
  PASSWORD_GRACE_TIME 10
  PASSWORD_VERIFY_FUNCTION DEFAULT;

This profile can be created locally in a PDB. If you are creating a common profile, then you must provide the profile name with the c## prefix (for example, c##password_prof).

The following example shows how to create a resource limits profile.

CREATE PROFILE app_user LIMIT
  SESSIONS_PER_USER          UNLIMITED
  CPU_PER_SESSION            UNLIMITED
  CPU_PER_CALL               3500
  CONNECT_TIME               50
  LOGICAL_READS_PER_SESSION  DEFAULT
  LOGICAL_READS_PER_CALL     1200
  PRIVATE_SGA                20K
  COMPOSITE_LIMIT            7500000;

Related Topics

Creating a CDB Profile or an Application Profile

The CREATE PROFILE or ALTER PROFILE statement CONTAINER=ALL clause can create a profile in a CDB or application root.

You cannot create local profiles in the CDB root or the application root. The profile that you create will be applied to all PDBs that are associated with the CDB root or the application root.

To create a profile in a CDB root or an application root, optionally include the CONTAINER=ALL clause in the CREATE PROFILE or ALTER PROFILE statement.

The CONTAINER=ALL clause is optional because it is the default when the statement is processed.

For example:

CREATE PROFILE password_prof LIMIT
  FAILED_LOGIN_ATTEMPTS 6
  PASSWORD_LIFE_TIME 60
  PASSWORD_REUSE_TIME 60
  PASSWORD_REUSE_MAX 5
  PASSWORD_LOCK_TIME 1/24
  PASSWORD_GRACE_TIME 10
  PASSWORD_VERIFY_FUNCTION DEFAULT
  CONTAINER=ALL;

Assigning a Profile to a User

After you create a profile, you can assign it to users.

You can assign a profile to a user who has already been assigned a profile, but the most recently assigned profile takes precedence. When you assign a profile to an external user or a global user, the password parameters do not take effect for that user.

To find the profiles that are currently assigned to users, you can query the DBA_USERS view.

Use the ALTER USER statement to assign the profile to a user.

For example:

ALTER USER psmith PROFILE app_user;

Dropping Profiles

You can drop a profile, even if it is currently assigned to a user.

When you drop a profile, the drop does not affect currently active sessions. Only sessions that were created after a profile is dropped use the modified profile assignments. To drop a profile, you must have the DROP PROFILE system privilege. You cannot drop the default profile.

Use the SQL statement DROP PROFILE to drop a profile. To drop a profile that is currently assigned to a user, use the CASCADE option.

For example:

DROP PROFILE clerk CASCADE;

Any user currently assigned to a profile that is dropped is automatically is assigned to the DEFAULT profile. The DEFAULT profile cannot be dropped.

Related Topics

Common Mandatory Profiles in the CDB Root

You can enforce a minimum password length throughout the CDB and its PDBs without restricting access to database user profiles.

About Common Mandatory Profiles in the CDB Root

The mandatory user profile imposes mandatory profile limits across the entire CDB or for individual PDBs.

The limits that you define in this mandatory user profile can be enforced in addition to the already existing limits in the profile for which the user is currently associated. Hence, you can use mandatory profiles to enforce the password complexity rules for all the user accounts in the database, regardless of the profile limits that are enforced in individual PDBs. For example, if a user profile limit states that the user must have at least 8 characters in the password but the mandatory profile states the user must have 10, then the 10-character limit will take precedence. User profile restrictions that are not in the mandatory profile still take effect. Only password length is enforced in a mandatory profile.

The password complexity verification function of the mandatory profile runs before the password complexity function that is associated with the user account profile (assuming this profile has a password complexity function). The mandatory profile limits apply for all local and common users in the entire CDB, so they can be used to enforce a CDB-wide password policy that is always active.

Because the mandatory profile is a common profile that is created in the CDB root, PDB administrators cannot alter or drop this profile in an attempt to circumvent the mandatory profile’s user restrictions. Only common users who have been commonly granted the ALTER PROFILE system privilege can alter or drop the mandatory profile, and only from the CDB root. Only a common user who has been commonly granted the ALTER SYSTEM privilege or has the SYSDBA administrative privilege can modify the MANDATORY_USER_PROFILE in the init.ora file.

Unlike other user profiles, you cannot assign the mandatory profile to a user. Any attempt to do so will result in an ORA-02384: cannot assign profile_name profile to a user error.

You can create multiple mandatory profiles in the CDB root, which you then can use to configure different mandatory limits at the PDB level.

If you want to apply the mandatory user profile for all PDBs in the CDB, then you must do so in the CDB root using the ALTER SYSTEM statement. If you want to apply the mandatory user profile for individual PDBs, then you must configure it in the init.ora file that is associated with the PDB. The mandatory profile that you set in init.ora takes precedence over the mandatory profile that you set with the ALTER SYSTEM statement in the CDB root. This functionality enables you to have the following use case: suppose you have a CDB with 20 PDBs, two of which must have a different mandatory profile set from the remaining

  1. To accomplish this, do the following:

  2. Create two mandatory profiles, one for the two PDBs and a second mandatory profile for the remaining 18.

  3. For the two PDBs, edit the init.ora file to point to the mandatory profile that you want these PDBs to use.

  4. For the remaining PDBS, run the ALTER SYSTEM statement in the CDB root to point to the mandatory profile that these PDBs need to use

Creating a Common Mandatory Profile in the CDB Root

To create and manage the mandatory profile, you use the CREATE MANDATORY PROFILE and ALTER SYSTEM statements.

  1. Connect to the CDB root as a common user who has the CREATE PROFILE and ALTER SYSTEM system privileges.

    For example:

    CONNECT c##sec_admin
Enter password: password

  2. Create the mandatory profile.

    For example, to create a mandatory profile called c##cdb_profile that will use the cdb_mandatory_function password verification function:

    CREATE MANDATORY PROFILE c##cdb_profile
LIMIT PASSWORD_VERIFY_FUNCTION cdb_mandatory_function
CONTAINER = ALL;

    In this specification:

    • LIMIT restricts the profile so that it only uses a specific password verification function (cdb_mandatory_function).

    • PASSWORD_VERIFY_FUNCTION specifies the user-created password complexity function cdb_mandatory_function. PASSWORD_VERIFY_FUNCTION is the only allowed parameter for CREATE MANDATORY PROFILE.

    • CONTAINER = ALL applies the profile to the entire CDB. If you want to set a different profile (for example, a stricter one) on a PDB in this CDB, then you can still apply a mandatory profile on that PDB to override the one that was set for the entire CDB. In an Oracle Autonomous Data Warehouse (ADW) environment, note that the lockdown profile will be used so that a local administrator cannot set or change the PDB-specific mandatory profile.

    You can create multiple mandatory profiles if you want (for example, one for the entire CDB and others for individual PDBs).

  3. Apply the mandatory profile to either the entire CDB environment or to individual pluggable databases (PDBs) within the CDB.

    To find the current MANDATORY_USER_PROFILE parameter setting, you can use the SHOW PARAMETER command.

    • For all PDBs in the CDB, from the root, run the ALTER SYSTEM statement. For example:

      ALTER SYSTEM SET MANDATORY_USER_PROFILE=c##cdb_profile;

    • For individual PDBs, set the MANDATORY_USER_PROFILE parameter in the init.ora file. For example, assuming that you created a PDB-specific mandatory profile called c##pdb_profile:

      MANDATORY_USER_PROFILE = c##pdb_profile

Example: Function to Enforce Minimum Password Length

You can use the MANDATORY_VERIFY_FUNCTION parameter to create complex functions that perform tasks such as checking the minimum password length of user passwords.

This example shows how to create a common password function and how it works with the CDB root and a PDB.

  1. Connect to the CDB as an administrative user.

    CONNECT sec_admin@cdb_name;
Enter password: password

  2. Create a CDB common mandatory profile.

    CREATE MANDATORY PROFILE c##mand LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Profile created.

  3. Check the profile that you just created.

    SELECT RESOURCE_NAME, LIMIT, PROFILE FROM DBA_PROFILES WHERE PROFILE = 'C##MAND';

RESOURCE_NAME                  LIMIT      PROFILE

------------------------------ ---------- ----------
COMPOSITE_LIMIT                           C##MAND
SESSIONS_PER_USER                         C##MAND
CPU_PER_SESSION                           C##MAND
CPU_PER_CALL                              C##MAND
LOGICAL_READS_PER_SESSION                 C##MAND
LOGICAL_READS_PER_CALL                    C##MAND
IDLE_TIME                                 C##MAND
CONNECT_TIME                              C##MAND
PRIVATE_SGA                               C##MAND
FAILED_LOGIN_ATTEMPTS                     C##MAND
PASSWORD_LIFE_TIME                        C##MAND
PASSWORD_REUSE_TIME                       C##MAND
PASSWORD_REUSE_MAX                        C##MAND
PASSWORD_VERIFY_FUNCTION       NULL       C##MAND
PASSWORD_LOCK_TIME                        C##MAND
PASSWORD_GRACE_TIME            0          C##MAND
INACTIVE_ACCOUNT_TIME                     C##MAND
PASSWORD_ROLLOVER_TIME                    C##MAND

18 rows selected.

  4. Create the my_mandatory_verify_function function, which will enforce the minimum password length.

    CREATE OR REPLACE FUNCTION my_mandatory_verify_function
 ( username     varchar2,
   password     varchar2,
   old_password varchar2)
 return boolean IS
BEGIN

   -- mandatory verify function will always be evaluated regardless of the

   -- password verify function that is associated to a particular profile/user

   -- requires the minimum password length to be 8 characters
   if not ora_complexity_check(password, chars => 8) then

      return(false);
   end if;
   return(true);
END;
/

Function created.

  5. Attach the mandatory_verify_function function to the c##mand profile.

    ALTER PROFILE c##mand LIMIT PASSWORD_VERIFY_FUNCTION my_mandatory_verify_function;

Profile altered.

  6. Set the MANDATORY_USER_PROFILE parameter in the CDB$ROOT so that all the PDBs inherit the same mandatory profile and limits.

    ALTER SYSTEM SET MANDATORY_USER_PROFILE=c##mand;

System altered.

  7. Check the MANDATORY_USER_PROFILE parameter setting for the CDB.

    SHOW PARAMETER MANDATORY_USER_PROFILE

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##MAND

  8. Switch to a PDB.

    You can find the names of PDBs by executing the SELECT PDB_NAME FROM DBA_PDBS query. For example, to switch to PDB hrpdb:

    ALTER SESSION SET CONTAINER=hrpdb;

Session altered.

  9. Check the MANDATORY_USER_PROFILE parameter setting for the PDB.

    SHOW PARAMETER MANDATORY_USER_PROFILE

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------
mandatory_user_profile               string      C##MAND

  10. Check the c##mand profile as it is set for the PDB.

    SELECT RESOURCE_NAME, LIMIT, PROFILE FROM DBA_PROFILES WHERE PROFILE = 'C##MAND';

RESOURCE_NAME                  LIMIT      PROFILE

------------------------------ ---------- ----------
COMPOSITE_LIMIT                           C##MAND
SESSIONS_PER_USER                         C##MAND
CPU_PER_SESSION                           C##MAND
CPU_PER_CALL                              C##MAND
LOGICAL_READS_PER_SESSION                 C##MAND
LOGICAL_READS_PER_CALL                    C##MAND
IDLE_TIME                                 C##MAND
CONNECT_TIME                              C##MAND
PRIVATE_SGA                               C##MAND
FAILED_LOGIN_ATTEMPTS                     C##MAND
PASSWORD_LIFE_TIME                        C##MAND
PASSWORD_REUSE_TIME                       C##MAND
PASSWORD_REUSE_MAX                        C##MAND
PASSWORD_VERIFY_FUNCTION       NULL       C##MAND
PASSWORD_LOCK_TIME                        C##MAND
PASSWORD_GRACE_TIME            0          C##MAND
INACTIVE_ACCOUNT_TIME                     C##MAND
PASSWORD_ROLLOVER_TIME                    C##MAND

18 rows selected.

  11. Return to the CDB root.

    ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

  12. Test the my_mandatory_verify_function function and c##mand profile by attempting to create a user whose password is less than 8 characters.

    CREATE USER c##jack IDENTIFIED BY lame;

    The following error is returned:

    ERROR at line 1:
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 8 characters

  13. Now try creating the common user’s password correctly:

    CREATE USER c##jack IDENTIFIED BY correct_password;

User created.

  14. Try altering c##jack’s password to be of an incorrect length:

    ALTER USER c##jack IDENTIFIED BY lame;

    The following error is returned:

    ERROR at line 1:
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 8 characters

    If user c##jack tries to change their password to be less than 8 characters, then the same errors are returned.

  15. Connect back to PDB.

    ALTER SESSION SET CONTAINER=hrpdb;

Session altered.

  16. Try creating a local user using less than 8 characters for the password.

    CREATE USER jessica IDENTIFIED BY lame;

ERROR at line 1:
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 8 characters

  17. Create user jessica with the correct password requirement.

    CREATE USER jessica IDENTIFIED BY correct_password;

User created.

  18. Create a custom password verify function for the PDB.

    This verify function requires that the password be at least 6 characters long with at least 2 digits.

    CREATE OR REPLACE FUNCTION custom_verify_function
 ( username     varchar2,
   password     varchar2,
   old_password varchar2)
 return boolean IS
BEGIN

   -- requires the password to be at least 6 characters long and minimum

   -- 2 digits be present
   if not ora_complexity_check(password, chars => 6, digit=>2) then

      return(false);
   end if;
   return(true);
END;
/

Function created.

  19. Create a local profile and then associate it with the custom_verify_function function.

    CREATE PROFILE lprofile LIMIT password_verify_function custom_verify_function;

Profile created.

  20. Assign profile lprofile to the local user jessica.

    ALTER USER jessica PROFILE lprofile;

User altered.

  21. Try changing user jessica’s password to one that uses 6 characters.

    ALTER USER jessica IDENTIFIED BY six_66;

ERROR at line 1:
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 8 characters

    Even though user jessica's password meets the requirements of the custom_verify_function function, the common function my_mandatory_verify_function overrides the local function custom_verify_function.