module13-password_profile.htm; updated July 1, 2013; Some figures shown in these notes are from Oracle document D11321GC11.

Module 13 Profiles and Resources

Objectives

        Create, alter, and administer profiles.

        Manage passwords by using profiles.

        Control resource usage with profiles.

        Obtain information about profiles and resources from the data dictionary.

 

Profiles

 

Profile is a database object a named set of resource limits to:

        Restrict database usage by a system user profiles restrict users from performing operations that exceed reasonable resource utilization. Examples of resources that need to be managed:

o   Disk storage space.

o   I/O bandwidth to run queries.

o   CPU power.

o   Connect time.

        Enforce password practices how user passwords are created, reused, and validated.

        Profiles are assigned to users as part of the CREATE USER or ALTER USER commands (User creation is covered in Module 14).

o   User accounts can have only a single profile.

o   A default profile can be created a default already exists within Oracle named DEFAULT it is applied to any user not assigned another profile.

o   Assigning a new profile to a user account supersedes any earlier profile.

o   Profiles cannot be assigned to roles or other profiles.

 

Profiles only take effect when resource limits are "turned on" for the database as a whole.

        Specify the RESOURCE_LIMIT initialization parameter.

 

RESOURCE_LIMIT = TRUE

 

        Use the ALTER SYSTEM statement to turn on resource limits.

 

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

 

        Resource limit specifications pertaining to passwords are always in effect.

 

Profile Specifications

 

Profile specifications include:

        Password aging and expiration

        Password history

        Password complexity verification

        Account locking

        CPU time

        Input/output (I/O) operations

        Idle time

        Connect time

        Memory space (private SQL area for Shared Server only)

        Concurrent sessions

 

System users not assigned a specific profile are automatically assigned the DEFAULT profile. The DEFAULT profile has only one significant restriction it doesn't specify a password verification function.

 

This query lists the resource limits for the DEFAULT profile.

 

COLUMN profile FORMAT A10;

COLUMN resource_name FORMAT a30;

COLUMN resource FORMAT a8;

COLUMN limit FORMAT a15;

SELECT * FROM DBA_PROFILES

WHERE PROFILE = 'DEFAULT';

 

PROFILE RESOURCE_NAME RESOURCE LIMIT

---------- ------------------------------ -------- ---------------

DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED

DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED

DEFAULT CPU_PER_SESSION KERNEL UNLIMITED

DEFAULT CPU_PER_CALL KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED

DEFAULT IDLE_TIME KERNEL UNLIMITED

DEFAULT CONNECT_TIME KERNEL UNLIMITED

DEFAULT PRIVATE_SGA KERNEL UNLIMITED

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL

DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED

 

16 rows selected.

 

Creating a Profile

 

A DBA creates a profile with the CREATE PROFILE command.

        This command has clauses that explicitly set resource limits.

        A DBA must have the CREATE PROFILE system privilege in order to use this command.

        Example:

 

CREATE PROFILE accountant LIMIT

 SESSIONS_PER_USER 4
 CPU_PER_SESSION unlimited
 CPU_PER_CALL 6000
 LOGICAL_READS_PER_SESSION unlimited
 LOGICAL_READS_PER_CALL 100
 IDLE_TIME 30
 CONNECT_TIME 480
 PASSWORD_REUSE_TIME 1
 PASSWORD_LOCK_TIME 7
 PASSWORD_REUSE_MAX 3; 
 
Profile created.

 

Resource limits that are not specified for a new profile inherit the limit set in the DEFAULT profile. These clauses are covered in detail later in these notes.

 

 

Assigning Profiles

 

Profiles can only be assigned to system users if the profile has first been created. Each system user is assigned only one profile at a time. When a profile is assigned to a system user who already has a profile, the new profile replaces the old one the current session, if one is taking place, is not affected, but subsequent sessions are affected. Also, you cannot assign a profile to a role or another profile (Roles are covered in Module 16).

 

As was noted above, profiles are assigned with the CREATE USER and ALTER USER command. An example CREATE USER command is shown here this command is covered in more detail in Module 14.

 

CREATE USER USER349
    IDENTIFIED BY secret
    PROFILE Accountant
    PASSWORD EXPIRE;

 

User created.

 

 

SELECT username, profile FROM dba_users

WHERE username = 'USER349';

 

USERNAME PROFILE

------------------------------ ----------

USER349 ACCOUNTANT

 

 

Altering Profiles

 

Profiles can be altered with the ALTER PROFILE command.

        A DBA must have the ALTER PROFILE system privilege to use this command.

        When a profile limit is adjusted, the new setting overrides the previous setting for the limit, but these changes do not affect current sessions in process.

        Example:

 

ALTER PROFILE Accountant LIMIT

 CPU_PER_CALL default
 LOGICAL_READS_PER_SESSION 20000
 SESSIONS_PER_USER 1;
 
Test this limit by trying to connect 
twice with the account user349.

 

 

Dropping a Profile

 

Profiles no longer required can be dropped with the DROP PROFILE command.

        The DEFAULT profile cannot be dropped.

        The CASCADE clause revokes the profile from any user account to which it was assigned the CASCADE clause MUST BE USED if the profile has been assigned to any user account.

        When a profile is dropped, any user account with that profile is reassigned the DEFAULT profile.

        Examples:

 

DROP PROFILE Accountant;

ERROR at line 1:

ORA-02382: profile ACCOUNTANT has users assigned, cannot drop without CASCADE

 

DROP PROFILE accountant CASCADE;

 

Profile dropped.

 

SELECT username, profile FROM dba_users

WHERE username = 'USER349';

 

USERNAME PROFILE

------------------------------ ----------

USER349 DEFAULT

 

        Changes that result from dropping a profile only apply to sessions that are created after the change current sessions are not modified.

 

 

Password Management

 

Password management can be easily controlled by a DBA through the use of profiles.

 

 

 

Enabling Password Management

 

Password management is enabled by creating a profile and assigning the profile to system users when their account is created or by altering system user profile assignments.

 

Password limits set in this fashion are always enforced. When password management is in use, an existing user account can be locked or unlocked by the ALTER USER command.

 

Password Account Locking: This option automatically locks a system user account if the user fails to execute proper login account name/password entries after a specified number of login attempts.

 

 

        The FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME parameter are specified as part of a profile.

        The FAILED_LOGIN_ATTEMPTS is specified as an integer. The PASSWORD_LOCK_TIME is specified as days.

        The database account can be explicitly locked with the ALTER USER command. When this happens, the account is not automatically unlocked.

 

Password Expiration/Aging: Specifies the lifetime of a password after the specified period, the password must be changed.

 

 

        The PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME parameters are specified as part of a profile.

        PASSWORD_LIFE_TIME specifies the maximum life of a password.

        If the PASSWORD_GRACE_TIME is exceeded, the account automatically locks.

        Both of these parameters are specified in days.

 

Password History: This option ensures that a password is not reused within a specified period of time or number of password changes.

 

 

        If either PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX are set to a value other than DEFAULT or UNLIMITED, the other parameter must be set to UNLIMITED.

        PASSWORD_REUSE_TIME is specified in days.

        PASSWORD_REUSE_MAX is an integer value specifying the number of password changes required before a password can be reused.

        If you set PASSWORD_REUSE_TIME to an integer value, then you must set PASSWORD_REUSE_MAX to UNLIMITED.

        If you set PASSWORD_REUSE_MAX to an integer value, then you must set PASSWORD_REUSE_TIME to UNLIMITED

 

Password Complexity Verification: This option ensures that a password is complex this helps provide protection against system intruders who attempt to guess a password.

 

        This is implemented by use of a password verification function. A DBA can write such a function or can use the default function named VERIFY_FUNCTION.

        The function that is used for password complexity verification is specified with the profile parameter, PASSWORD_VERIFY_FUNCTION.

        If NULL is specified (the default), no password verification is performed.

 

 

        The default VERIFY_FUNCTION has the characteristics shown in the figure below.

 

 

When a DBA connected as the user SYS executes the utlpwdmg.sql script (located at $ORACLE_HOME/rdbms/admin/utlpwdmg.sql) , the Oracle Server creates the VERIFY_FUNCTION . The script also executes the ALTER PROFILE command given below the command modifies the DEFAULT profile.

 

Example of executing the utlpwdmg.sql script.

 

SQL> Connect SYS as SYSDBA

SQL> start $ORACLE_HOME/rdbms/admin/utlpwdmg.sql

 

Function created.

 

Profile altered.

 

This ALTER PROFILE command is part of the utlpwdmg.sql script and does not need to be executed separately.

 

-- This script alters the default parameters for Password Management

-- This means that all the users on the system have Password Management

-- enabled and set to the following values unless another profile is

-- created with parameter values set to different value or UNLIMITED

-- is created and assigned to the user.

ALTER PROFILE DEFAULT LIMIT

PASSWORD_LIFE_TIME 60

PASSWORD_GRACE_TIME 10

PASSWORD_REUSE_TIME 1800

PASSWORD_REUSE_MAX UNLIMITED

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME 1/1440

PASSWORD_VERIFY_FUNCTION Verify_Function;

 

 

Creating a Profile with Password Protection: The figure shown below provides an example CREATE PROFILE command.

 

 

Use these parameters values when setting parameters to values that are less than a day:

        1 hour: PASSWORD_LOCK_TIME = 1/24

        10 minutes: PASSWORD_LOCK_TIME = 10/1400

        5 minutes: PASSWORD_LOCK_TIME = 5/1440

 

 

Resource Management

 

Enabling Resource Limits

 

As noted earlier, resource limits are enabled by setting the RESOURCE_LIMIT initialization parameter to TRUE (the default is FALSE) or by enabling the parameter with the ALTER SYSTEM command.

 

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE

 

System altered.

 

 

Setting User Session Resource Limits

 

Resource limits can also be managed through use of a Profile object.

 

This table describes the resource limit parameters for a Profile.

        Parameters can be either an integer value, or the keyword UNLIMITED or DEFAULT.

        DEFAULT specifies the limit from the DEFAULT profile.

        UNLIMITED specifies no limit on the resource is enforced.

        The COMPOSITE_LIMIT parameter enables controlling a group of resource limits example a system user may use a lot of CPU time, but not much disk I/O during a session, or vice versa during another session this keeps the policy from disconnecting the user.

 

Resource

Description

CPU_PER_SESSION

Total CPU time measured in hundredths of seconds

CPU_PER_CALL

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

SESSIONS_PER_USER

Maximum number of concurrent sessions allowed for each user name

CONNECT_TIME

Maximum total elapsed connect time measured in minutes

IDLE_TIME

Maximum continuous inactive time in a session measured in minutes when a query or other operation is not in progress.

LOGICAL_READS_

PER_SESSION

Number of data blocks (physical and logical reads) read per session from either memory or disk.

LOGICAL_READS_PER_CALL

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

COMPOSITE_LIMIT

Total Resource cost, in service units, as a composite weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.

PRIVATE_SGA

Maximum amount of memory a session can allocate in the shared pool of the SGA measured in bytes, kilobytes, or megabytes (applies to Shared Server only).

 

        Profile limits enforced at the session level are enforced for each connection where a system user can have more than one concurrent connection.

 

        If a session-level limit is exceeded, then the Oracle Server issues an error message such as ORA-02391: exceeded simultaneous SESSION_PER_USER limit, and then disconnects the system user.

 

        Resource limits can also be set at the Call-level, but this applies to PL/SQL programming limitations and we do not cover setting these Call-level limits in this course.

 

Adjusting Resource Cost Weights

 

The ALTER RESOURCE COST command is used to adjust weightings for resource costs. This can affect the impact of the COMPOSITE_LIMIT parameter.

 

Example: Here the weights are changed so CPU_PER_SESSION favors CPU usage over connect time by a factor of 50 to 1. This means it is much more likely that a system user will be disconnected from excessive CPU usage than from the use of excessive connect time.

 

        Step 1. Alter the resource cost for these two parameters.

 

ALTER RESOURCE COST

CPU_PER_SESSION 50

CONNECT_TIME 1;

 

Resource cost altered.

 

SELECT * FROM Resource_Cost;

 

RESOURCE_NAME UNIT_COST

-------------------------------- ----------

CPU_PER_SESSION 50

LOGICAL_READS_PER_SESSION 0

CONNECT_TIME 1

PRIVATE_SGA 0

 

        Step 2. Create a new profile or modify an existing profile to use a COMPOSITE_LIMIT parameter. Here the Accountant profile is recreated based on the command given earlier in these notes, then altered to set the COMPOSITE_LIMIT to 300. We also ensure that user349 is assigned this profile.

 

CREATE PROFILE Accountant LIMIT

 SESSIONS_PER_USER 4
 CPU_PER_SESSION unlimited
 CPU_PER_CALL 6000
 LOGICAL_READS_PER_SESSION unlimited
 LOGICAL_READS_PER_CALL 100
 IDLE_TIME 30
 CONNECT_TIME 480
 PASSWORD_REUSE_TIME 1
 PASSWORD_LOCK_TIME 7

PASSWORD_REUSE_MAX 3;

 

ALTER PROFILE Accountant LIMIT

COMPOSITE_LIMIT 300;

 

Profile altered.

 

ALTER USER user349 PROFILE Accountant;

 

User altered.

 

 

        Step 3. Test the new limit. The COMPOSITE_COST can be computed. This is the formula. This table compares high/low values for CPU and CONNECT usage to compute the composite cost and indicates if the resource limit is exceeded.

 

Composite_Cost = (50 * CPU_PER_SESSION) + (1 * CONNECT_TIME)

 

 

 

CPU (Seconds)

Connect (Seconds)

Composite Cost

Exceeded

Limit of 300

High CPU

High Connect

 

0.06

 

250

(50 * 6) + (1 * 250) = 300 + 250 = 490

Yes

Medium CPU

Low Connect

 

0.05

 

40

(50 * 5) + (1 * 40) = 250 + 40 = 290

No

Low CPU

Medium Connect

 

0.02

 

175

(50 * 2) + (1 * 175) = 100 + 175 = 275

No

Low CPU

Low Connect

 

0.02

 

40

(50 * 2) + (1 * 40) = 100 + 40 = 140

No

 

 

 

The Database Resource Manager

 

The Database Resource Manager can provide the Oracle server more control over resource management decisions; thus, avoiding problems from inefficient operating system management.

 

Oracle Database Resource Manager (the Resource Manager) enables you to manage multiple workloads within a database through the creation of resource plans and resource groups, and the allocation of individual user accounts to resource groups that are, in turn, allocated resource plans.

 

Generally the operating system handles resource management. However, within an Oracle database, this can result in a number of problems:

        Excessive overhead from operating system context switching between Oracle Database server processes when the number of server processes is high.

        Inefficient scheduling because the O/S may deschedule database servers while they hold latches, which is inefficient.

        Inappropriate allocation of resources by not prioritizing tasks properly among active processes.

        Inability to manage database-specific resources, such as parallel execution servers and active sessions

Example: Allocate 80% of available CPU resources to online users leaving 20% for batch users and jobs.

 

The Resource Manager enables you to classify sessions into groups based on session attributes, and to then allocate resources to those groups in a way that optimizes hardware utilization for your application environment.

 

The elements of the Resource Manager include:

        Resource consumer group Sessions grouped together based on the resources that they require the resource manager allocates resources to consumer groups, not individual sessions.

        Resource plan this is a database object a container for resource directives on how resources should be allocated.

        Resource plan directive this associates a resource consumer group to a resource plan.

 

You can use the DBMS_RESOURCE_MANAGER PL/SQL package to create and maintain these elements. The objects created are stored in the data dictionary.

 

Some special consumer groups always exist in the data dictionary and cannot be modified or deleted:

        SYS_GROUP the initial consumer group for all sessions created by SYS or SYSTEM.

        OTHER_GROUPS this group contains all sessions not assigned to a consumer group. Any resource plan must always have a directive for the OTHER_GROUPS.

 

This figure from your readings shows a simple resource plan for an OLTP and reporting set of applications.

        The plan is named DAYTIME.

        It allocates CPU resources among three resource consumer groups named OLTP, REPORTING, and OTHER_GROUPS.

 

Description of Figure 27-1 follows

 

 

Oracle provides a predefined procedure named CREATE_SIMPLE_PLAN so that a DBA can create simple resource plans.

 

A resource plan can reference subplans. This figure illustrates a top plan and all descending plans and groups.

 

Description of Figure 27-2 follows

 

 

 

In order to administer the Resource Manager, a DBA must have the ADMINISTER_RESOURCE_MANAGER system privilege this privilege is part of the DBA role along with the ADMIN option.

        The DBA can execute all procedures.

        The DBA can grant or revoke privileges to other system managers.

        The DBA can grant privileges to the user named HR an internal user for Oracle human resources software.

 

The Resource Manager is not enabled by default. This command (or init.ora file parameter) by the DBA actives the Resource Manager and sets the top plan.

 

RESOURCE_MANAGER_PLAN = DAYTIME.

 

Activate or deactivate the Resource Manager dynamically or change plans with the ALTER SYSTEM command.

 

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = Alternate_Plan;

 

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ;

 

 

Note: The Database Resource Manager is covered further in the Oracle course Oracle Performance Tuning.

 

 

 

Using the Data Dictionary

 

Information about password and resource limits can be obtained by querying the following views:

        DBA_USERS

        DBA_PROFILES

 

COLUMN username FORMAT A15;

COLUMN password FORMAT A20;

COLUMN account_status FORMAT A30;

SELECT username, password, account_status

FROM dba_users;

 

USERNAME PASSWORD ACCOUNT_STATUS

--------------- -------------------- ------------------------------

OUTLN 4A3BA55E08595C81 OPEN

USER350 2D5E5DB47A5419B2 OPEN

DBOCK 0D25D10037ACDC6A OPEN

SYS DCB748A5BC5390F2 OPEN

SYSTEM EED9B65CCECDB2E9 OPEN

USER349 E6677904C9407D8A EXPIRED

TSMSYS 3DF26A8B17D0F29F EXPIRED & LOCKED

DIP CE4A36B8E06CA59C EXPIRED & LOCKED

DBSNMP E066D214D5421CCC EXPIRED & LOCKED

ORACLE_OCM 6D17CF1EB1611F94 EXPIRED & LOCKED

 

10 rows selected.

 

COLUMN profile FORMAT A16;

COLUMN resource_name FORMAT A26;

COLUMN resource_type FORMAT A13;

COLUMN limit FORMAT A10;

SELECT profile, resource_name, resource_type, limit

FROM dba_profiles

WHERE resource_type = 'PASSWORD';

 

 

PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT

---------- -------------------------- ------------- ----------

ACCOUNTANT FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 3

ACCOUNTANT PASSWORD_LIFE_TIME PASSWORD DEFAULT

DEFAULT PASSWORD_LIFE_TIME PASSWORD 60

ACCOUNTANT PASSWORD_REUSE_TIME PASSWORD 1

DEFAULT PASSWORD_REUSE_TIME PASSWORD 1800

ACCOUNTANT PASSWORD_REUSE_MAX PASSWORD 3

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

ACCOUNTANT PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUN

ACCOUNTANT PASSWORD_LOCK_TIME PASSWORD 7

DEFAULT PASSWORD_LOCK_TIME PASSWORD .0006

ACCOUNTANT PASSWORD_GRACE_TIME PASSWORD DEFAULT

DEFAULT PASSWORD_GRACE_TIME PASSWORD 10

 

14 rows selected.

 

 

 

END OF NOTES