Profiles, Password and Resource Control for Oracle Database XE

In this fifth part of a ten-part series on securing Oracle Database XE, you’ll learn about profiles as they pertain to password and resource control. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Profiles and Password Control

Table 31-3 shows the password-related profile parameters. All units of time are specified in days (e.g., to specify any of these parameters in minutes, divide by 1440):

SQL> create profile lim_lock limit password_lock_time 5/1440;
Profile created.

In this example, an account will only be locked for five minutes after the specified number of login failures.

Table 31-3. Password-Related Profile Parameters  


Password Parameter



The number of failed login attempts before the account is locked.


The number of days the password can be used before it must be changed. If it is not changed within PASSWORD_GRACE_TIME, the password must be changed before logins are allowed.


The number of days a user must wait before reusing a password; this parameter is used in conjunction with PASSWORD_REUSE_MAX.


The number of password changes that have to occur before a password can be reused; this parameter is used in conjunction with PASSWORD_REUSE_TIME.


The number of days the account is locked after FAILED_LOGIN_ ATTEMPTSattempts. After this time period, the account is automatically unlocked.


The number of days after which an expired password must be changed. If it is not changed within this time period, the account is expired and the password must be changed before the user can log in successfully.


A PL/SQL script to provide an advanced password-verification routine. If NULLis specified (the default), no password verification is performed.


A parameter value of UNLIMITED means that there is no limit on how much of the given resource can be used. DEFAULT means that this parameter takes its values from the DEFAULT profile.

The parameters PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX must be used together; setting one without the other has no useful effect. In the following example, we create a profile that sets PASSWORD_REUSE_TIME to 20 days and PASSWORD_REUSE_MAX to 5:

create profile lim_reuse_pass limit  
assword_reuse_time 20
password_reuse_max 5;

Users with this profile can reuse their passwords after 20 days if the password has been changed at least five times. If you specify a value for PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX , and UNLIMITED for the other, a user can never reuse a password.

If you want to provide tighter control over how passwords are created and reused, such as using a mixture of uppercase and lowercase characters in every password, you need to enable the PASSWORD_VERIFY_FUNCTION limit in each applicable profile. Oracle provides a template for enforcing an organization’s password policy. It’s located in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql . The script provides the following functionality for password complexity:

  1. Ensures that the password is not the same as the username
  2. Ensures that the password is at least four characters long 
  3. Checks to make sure the password is not a simple, obvious word, such as ORACLE or DATABASE 
  4. Requires that the password contains one letter, one digit, and one punctuation mark 
  5. Ensures that the password is different from the previous password by at least three characters

To use this policy, the first step is to make your own custom changes to this script. For example, you may wish to have several different verify functions, one for each country or business unit, to match the database password complexity requirements to that of the operating systems in use in a particular country or business unit. Therefore, you can rename this function as VERIFY_FUNCTION_US_WESTCOAST , for example. In addition, you might want to change the list of simple words to include names of departments or buildings at your company.

{mospagebreak title=Profiles and Resource Control}

The list of resource-control profile options that can appear after CREATE PROFILE profilename LIMIT are explained in Table 31-4. Each of these parameters can either be an integer, UNLIMITED , or DEFAULT .

Table 31-4. Resource-Related Profile Parameters


Resource Parameter



Maximum number of sessions a user can simultaneously have


Maximum CPU time allowed per session, in hundredths of a second


Maximum CPU time for a statement parse, execute, or fetch operation, in hundredths of a second


Maximum total elapsed time, in minutes


Maximum continuous inactive time in a session, in minutes, while a query or other operation is not in progress


Total number of data blocks read per session, either from memory or disk


Maximum number of data blocks read for a statement parse, execute, or fetch operation


Total resource cost, in service units, computed as a composite weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_ READS_PER_SESSION, and PRIVATE_SGA


Maximum amount of memory a session can allocate in the shared pool, in bytes, kilobytes, or megabytes


As with the password-related parameters, UNLIMITED means that there is no bound on how much of the given resource can be used. DEFAULT means that this parameter takes its values from the DEFAULT profile.

The COMPOSITE_LIMIT parameter allows you to control a group of resource limits when the types of resources typically used varies widely by type; it allows a user to use a lot of CPU time but not much disk I/O during one session, and vice versa, during another session, without being disconnected by the policy.

By default, all resource costs are zero:

SQL> select * from resource_cost;

RESOURCE_NAME                  UNIT_COST
—————————– ———-
CPU_PER_SESSION                        0 LOGICAL_READS_PER_SESSION              0 CONNECT_TIME                           0 PRIVATE_SGA                            0
4 rows selected.

To adjust the resource cost weights, use the ALTER RESOURCE COST command. In this example, we change the weightings so that CPU_PER_SESSION favors CPU usage over connect time by a factor of 25 to 1; in other words, a user will be disconnected more likely because of CPU usage than because of connect time:

SQL> alter resource cost
  2      cpu_per_session 50
  3      connect_time 2;
Resource cost altered.
SQL> select * from resource_cost;

—————————- ———- 
CPU_PER_SESSION                      50 LOGICAL_READS_PER_SESSION             0 CONNECT_TIME                          2 PRIVATE_SGA                           0
4 rows selected.

The next step is to create a new profile or modify an existing profile to use a composite limit:

SQL> create profile lim_comp_cpu_conn limit
  2      composite_limit 250;
Profile created.

As a result, users assigned to the profile LIM_COMP_CPU_CONN will have their session resources limited using the following formula to calculate cost:

composite_cost = (50 * CPU_PER_SESSION) + (2 * CONNECT_TIME);

In Table 31-5, we provide some examples of resource usage to see if the composite limit of 250 is exceeded.

Table 31-5. Resource Usage Scenarios  


CPU (Seconds)

Connect (Seconds)

Composite Cost




(50 * 5) + (2 * 100) = 450




(50 * 2) + (2 * 30) = 160




(50 * 1) + (2 * 150) = 350




(50 * 2) + (2 * 5) = 110



The parameters PRIVATE_SGA and LOGICAL_READS_PER_SESSION are not used in this particular example, so unless they are specified otherwise in the profile definition, they default to whatever their value is in the DEFAULT profile. The goal of using composite limits is to give users some leeway in the types of queries or DML they run. On some days, they may run a lot of queries that perform numerous calculations but don’t access a lot of table rows; on other days, they may do a lot of full table scans but don’t stay connected very long. In these situations, you don’t want to limit a user by a single parameter, but instead by total resource usage weighted by the availability of each resource on the server.

Please check back next week for the next part of this series.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan