The DBMS must manage excess capacity, bandwidth, or other redundancy to limit the effects of information flooding types of Denial of Service (DoS) attacks.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-61817	O121-C3-019300	SV-76307r1_rule		Low

Description
In the case of application DoS attacks, care must be taken when designing the application to ensure the application makes the best use of system resources. SQL queries have the potential to consume large amounts of CPU cycles if they are not tuned for optimal performance. Web services containing complex calculations requiring large amounts of time to complete can bog down if too many requests for the service are encountered within a short period of time. The methods employed to meet this requirement will vary depending upon the technology the application utilizes. However, a variety of technologies exist to limit, or in some cases, eliminate the effects of application-related DoS attacks. Employing increased capacity and bandwidth combined with specialized application layer protection devices and service redundancy may reduce the susceptibility to some DoS attacks. Databases are particularly susceptible to SQL related DoS attacks. Databases that do not identify long-running SQL queries for review by an administrator may experience dramatic slowdowns from malicious or accidental DoS attacks related to SQL queries.

Description

In the case of application DoS attacks, care must be taken when designing the application to ensure the application makes the best use of system resources. SQL queries have the potential to consume large amounts of CPU cycles if they are not tuned for optimal performance. Web services containing complex calculations requiring large amounts of time to complete can bog down if too many requests for the service are encountered within a short period of time. The methods employed to meet this requirement will vary depending upon the technology the application utilizes. However, a variety of technologies exist to limit, or in some cases, eliminate the effects of application-related DoS attacks. Employing increased capacity and bandwidth combined with specialized application layer protection devices and service redundancy may reduce the susceptibility to some DoS attacks. Databases are particularly susceptible to SQL related DoS attacks. Databases that do not identify long-running SQL queries for review by an administrator may experience dramatic slowdowns from malicious or accidental DoS attacks related to SQL queries.

STIG	Date
Oracle Database 12c Security Technical Implementation Guide	2015-12-21

Details

Check Text ( C-62697r2_chk )
Review DBMS configuration to determine whether DBMS settings to identify long-running SQL queries and alert appropriate personnel are enabled. If DBMS does not identify long-running SQL queries and alert appropriate personnel, this is a finding. Use the following commands to determine the established profiles and the users and resource limits assigned to them. $ sqlplus connect as sysdba set linesize 121 col username format a20 col profile format a20 col resource_name format a25 col resource_type format a14 col limit format a10 select a.username, a.profile, b.resource_name, b.resource_type, b.limit from dba_users a, dba_profiles b where a.profile = b.profile and resource_type='KERNEL' order by a.profile;

Check Text ( C-62697r2_chk )

Review DBMS configuration to determine whether DBMS settings to identify long-running SQL queries and alert appropriate personnel are enabled.

If DBMS does not identify long-running SQL queries and alert appropriate personnel, this is a finding.

Use the following commands to determine the established profiles and the users and resource limits assigned to them.

$ sqlplus connect as sysdba

set linesize 121
col username format a20
col profile format a20
col resource_name format a25
col resource_type format a14
col limit format a10

select a.username,
a.profile,
b.resource_name,
b.resource_type,
b.limit
from dba_users a, dba_profiles b
where a.profile = b.profile and
resource_type='KERNEL'
order by a.profile;

Fix Text (F-67733r5_fix)
Configure DBMS settings to identify long-running SQL queries and alert appropriate personnel. Create a user profile and assign users to the profile. In the profile itself, set resource limits on CPU per session, CPU per call, Number of rows returned or any of the following resources. If a user session exceeds the predefined session limits, a message is provided to the user and also in the audit log. The user profile, ORA_STIG_PROFILE, has been provided (starting with Oracle 12.1.0.2) to satisfy all of the STIG regulations pertaining to the profile parameters. Oracle recommends that this profile be customized with any site-specific requirements and assigned to all users where applicable. SQL> CREATE PROFILE LIMIT resource_parameters\|password_parameters--;-> Resource_parameters (can specify multiple parameters per command): [SESSIONS_PER_USER n\|UNLIMITED\|DEFAULT] [CPU_PER_SESSION n\|UNLIMITED\|DEFAULT] [CPU_PER_CALL n\|UNLIMITED\|DEFAULT] [CONNECT_TIME n\|UNLIMITED\|DEFAULT] [IDLE_TIME n\|UNLIMITED\|DEFAULT] [LOGICAL_READS_PER_SESSION n\|UNLIMITED\|DEFAULT] [LOGICAL_READS_PER_CALL n\|UNLIMITED\|DEFAULT] [COMPOSITE_LIMIT n\|UNLIMITED\|DEFAULT] [PRIVATE_SGA n [K\|M]\|UNLIMITED\|DEFAULT] CREATE PROFILE enduser LIMIT CPU_PER_SESSION 60000 LOGICAL_READS_PER_SESSION 1000 CONNECT_TIME 30 PRIVATE_SGA 102400 CPU_PER_CALL UNLIMITED FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LIFE_TIME 90 PASSWORD_REUSE_TIME 180 PASSWORD_LOCK_TIME 3 PASSWORD_GRACE_TIME 3 PASSWORD_VERIFY_FUNCTION ORA12C_STRONG_VERIFY_FUNCTION; Can assign a profile to a user when creating the user or by altering the user. The syntax to alter the profile for a user is: SQL> ALTER USER PROFILE ; For example: SQL>ALTER USER scott PROFILE appuser; Must have the CREATE PROFILE system privilege to create a profile. To alter a profile, Must be the creator of the profile or have the ALTER PROFILE system privilege. To assign a profile to a user, must have the CREATE USER or ALTER USER system privilege. Profiles and Resource Limits: The default cost assigned to a resource is unlimited. By setting resource limits, can prevent users from performing operations that will tie up the system and prevent other users from performing operations. Can use resource limits for security to ensure that users log off the system and do not leave the session connected for long periods of time. Can also assign a composite cost to each profile. The system resource limits can be enforced at the session level, the call level, or both. The session level is from the time the user logs on to the database until the user exits. The call level is for each SQL command issued. Session-level limits are enforced for each connection. When a session-level limit is exceeded, only the last SQL command issued is rolled back, and no further work can be performed until a commit, rollback, or exit is performed. Note: If using parallel query option (PQO), the resources are applied to each new session, not accumulated over all of the sessions that a parallel operation uses. RESOURCES REGULATED AT THE session-level SYSTEM RESOURCE DEFINITION --------------- ---------- CPU_PER_SESSION total CPU time in hundredths of seconds SESSIONS_PER_USER number of concurrent sessions for a user CONNECT_TIME allowed connection time in minutes IDLE_TIME inactive time on the server in minutes LOGICAL_READS_PER_SESSION number of data blocks read including both physical and logical reads from memory and disk PRIVATE_SGA bytes of SGA used in a database with the multithreaded server (in K or M) Can combine the CPU_PER_SESSION, LOGICAL_READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA to create a COMPOSITE LIMIT. Call-level limits are enforced during the execution of each SQL statement. When a call-level limit is exceeded, the last SQL command issued is rolled back. All the previous statements issued are still valid, and the user can continue to execute other SQL statements. The following system resources can be regulated at the call level: * CPU_PER_CALL for the CPU time for the SQL statement * LOGICAL_READS_PER_CALL for the number of data blocks read for the SQL statement The assignment of a cost to a resource can be performed with the ALTER RESOURCE COST command. Resource limits that are set explicitly for a user take precedence over the resource costs in an assigned profile. The command line syntax for this command is: >-ALTER RESOURCE COST ---------------------------------------------------;--> \|-[CPU_PER_SESSION n\|UNLIMITED\|DEFAULT]-----------\| \|-[CONNECT_TIME n\|UNLIMITED\|DEFAULT]--------------\| \|-[LOGICAL_READS_PER_SESSION n\|UNLIMITED\|DEFAULT]-\| \|-[PRIVATE_SGA n [K\|M]\|UNLIMITED\|DEFAULT]--------\| For example, ALTER RESOURCE COST CONNECT_TIME 100; Use of resource limits is set in the database initialization parameter RESOURCE_LIMIT=TRUE. By default this parameter is set to false. This parameter can be changed interactively with an ALTER SYSTEM command. The DBA_PROFILES view provides information on all the profiles and the resource limits for each profile. The RESOURCE_COST view shows the unit cost associated with each resource. Each user can find information on his resources and limits in the USER_RESOURCE_LIMITS view. Below is a description of these data dictionary views. DBA_PROFILES Column Definition ------ ---------- Profile the name given to the profile Resource_name the name of the resource assigned to the profile Limit the limit placed on the profile RESOURCE_COST Column Definition ------ ---------- Resource_name name of the resource Unit_cost cost assigned USER_RESOURCE_LIMITS Column Definition ------ ---------- Resource_name the name of the resource Limit the limit placed on the user

Fix Text (F-67733r5_fix)

Configure DBMS settings to identify long-running SQL queries and alert appropriate personnel.

Create a user profile and assign users to the profile. In the profile itself, set resource limits on CPU per session, CPU per call, Number of rows returned or any of the following resources. If a user session exceeds the predefined session limits, a message is provided to the user and also in the audit log.

The user profile, ORA_STIG_PROFILE, has been provided (starting with Oracle 12.1.0.2) to satisfy all of the STIG regulations pertaining to the profile parameters. Oracle recommends that this profile be customized with any site-specific requirements and assigned to all users where applicable.

SQL> CREATE PROFILE LIMIT resource_parameters|password_parameters--;->
Resource_parameters (can specify multiple parameters per command):
[SESSIONS_PER_USER n|UNLIMITED|DEFAULT]
[CPU_PER_SESSION n|UNLIMITED|DEFAULT]
[CPU_PER_CALL n|UNLIMITED|DEFAULT]
[CONNECT_TIME n|UNLIMITED|DEFAULT]
[IDLE_TIME n|UNLIMITED|DEFAULT]
[LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT]
[LOGICAL_READS_PER_CALL n|UNLIMITED|DEFAULT]
[COMPOSITE_LIMIT n|UNLIMITED|DEFAULT]
[PRIVATE_SGA n [K|M]|UNLIMITED|DEFAULT]

CREATE PROFILE enduser LIMIT
CPU_PER_SESSION 60000
LOGICAL_READS_PER_SESSION 1000
CONNECT_TIME 30
PRIVATE_SGA 102400
CPU_PER_CALL UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 90
PASSWORD_REUSE_TIME 180
PASSWORD_LOCK_TIME 3
PASSWORD_GRACE_TIME 3
PASSWORD_VERIFY_FUNCTION ORA12C_STRONG_VERIFY_FUNCTION;

Can assign a profile to a user when creating the user or by altering the user. The syntax to alter the profile for a user is:

SQL> ALTER USER PROFILE ;

For example:

SQL>ALTER USER scott PROFILE appuser;

Must have the CREATE PROFILE system privilege to create a profile. To alter a profile, Must be the creator of the profile or have the ALTER PROFILE system privilege. To assign a profile to a user, must have the CREATE USER or ALTER USER system privilege.

Profiles and Resource Limits:
The default cost assigned to a resource is unlimited. By setting resource limits, can prevent users from performing operations that will tie up the system and prevent other users from performing operations. Can use resource limits for security to ensure that users log off the system and do not leave the session connected for long periods of time. Can also assign a composite cost to each profile. The system resource limits can be enforced at the session level, the call level, or both.

The session level is from the time the user logs on to the database until the user exits. The call level is for each SQL command issued. Session-level limits are enforced for each connection. When a session-level limit is exceeded, only the last SQL command issued is rolled back, and no further work can be performed until a commit, rollback, or exit is performed.

Note: If using parallel query option (PQO), the resources are applied to each new session, not accumulated over all of the sessions that a parallel operation uses.

RESOURCES REGULATED AT THE session-level

SYSTEM RESOURCE DEFINITION
--------------- ----------
CPU_PER_SESSION total CPU time in hundredths of seconds
SESSIONS_PER_USER number of concurrent sessions for a user
CONNECT_TIME allowed connection time in minutes
IDLE_TIME inactive time on the server in minutes
LOGICAL_READS_PER_SESSION number of data blocks read including both physical and logical reads from memory and disk
PRIVATE_SGA bytes of SGA used in a database with the multithreaded server (in K or M)

Can combine the CPU_PER_SESSION, LOGICAL_READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA to create a COMPOSITE LIMIT.

Call-level limits are enforced during the execution of each SQL statement. When a call-level limit is exceeded, the last SQL command issued is rolled back. All the previous statements issued are still valid, and the user can continue to execute other SQL statements. The following system resources can be regulated at the call level:

* CPU_PER_CALL for the CPU time for the SQL statement
* LOGICAL_READS_PER_CALL for the number of data blocks read for the SQL statement

The assignment of a cost to a resource can be performed with the ALTER RESOURCE COST command. Resource limits that are set explicitly for a user take precedence over the resource costs in an assigned profile. The command line syntax for this command is:

>-ALTER RESOURCE COST ---------------------------------------------------;-->
|-[CPU_PER_SESSION n|UNLIMITED|DEFAULT]-----------|
|-[CONNECT_TIME n|UNLIMITED|DEFAULT]--------------|
|-[LOGICAL_READS_PER_SESSION n|UNLIMITED|DEFAULT]-|
|-[PRIVATE_SGA n [K|M]|UNLIMITED|DEFAULT]--------|

For example,
ALTER RESOURCE COST CONNECT_TIME 100;

Use of resource limits is set in the database initialization parameter RESOURCE_LIMIT=TRUE. By default this parameter is set to false. This parameter can be changed interactively with an ALTER SYSTEM command.

The DBA_PROFILES view provides information on all the profiles and the resource limits for each profile. The RESOURCE_COST view shows the unit cost associated with each resource. Each user can find information on his resources and limits in the USER_RESOURCE_LIMITS view.

Below is a description of these data dictionary views.

DBA_PROFILES
Column Definition
------ ----------
Profile the name given to the profile
Resource_name the name of the resource assigned to the profile
Limit the limit placed on the profile

RESOURCE_COST
Column Definition
------ ----------
Resource_name name of the resource
Unit_cost cost assigned

USER_RESOURCE_LIMITS
Column Definition
------ ----------
Resource_name the name of the resource
Limit the limit placed on the user