module13-password_profile.htm; updated July 1, 2013; Some figures shown in these notes are from Oracle document D11321GC11.
Module 13 – Profiles and Resources
· 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.
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 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.
CREATE PROFILE accountant LIMIT
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.
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.
IDENTIFIED BY secret
SELECT username, profile FROM dba_users
WHERE username = 'USER349';
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.
ALTER PROFILE Accountant LIMIT
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.
DROP PROFILE Accountant;
ERROR at line 1:
ORA-02382: profile ACCOUNTANT has users assigned, cannot drop without CASCADE
DROP PROFILE accountant CASCADE;
SELECT username, profile FROM dba_users
WHERE username = 'USER349';
· Changes that result from dropping a profile only apply to sessions that are created after the change – current sessions are not modified.
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
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
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
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
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.
Total CPU time – measured in hundredths of seconds
Maximum CPU time allowed for a statement parse, execute, or fetch operation, in hundredths of a second.
Maximum number of concurrent sessions allowed for each user name
Maximum total elapsed connect time measured in minutes
Maximum continuous inactive time in a session measured in minutes when a query or other operation is not in progress.
Number of data blocks (physical and logical reads) read per session from either memory or disk.
Maximum number of data blocks read for a statement parse, execute, or fetch operation.
Total Resource cost, in service units, 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 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
Resource cost altered.
SELECT * FROM Resource_Cost;
· 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
ALTER PROFILE Accountant LIMIT
ALTER USER user349 PROFILE Accountant;
· 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)
Limit of 300
(50 * 6) + (1 * 250) = 300 + 250 = 490
(50 * 5) + (1 * 40) = 250 + 40 = 290
(50 * 2) + (1 * 175) = 100 + 175 = 275
(50 * 2) + (1 * 40) = 100 + 40 = 140
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.
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.
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:
COLUMN username FORMAT A15;
COLUMN password FORMAT A20;
COLUMN account_status FORMAT A30;
SELECT username, password, account_status
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
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