14Copyright Oracle Corporation, 2002. All rights reserved.Managing Password Securityand Resources14-2Copyright Oracle Corporation, 2002. All rights reserved.ObjectivesAfter completing this lesson, you should be able to do the following:Manage passwords using profilesAdminister profilesControl use of resources using profilesObtain password and resource limit information14-3Copyright Oracle Corporation, 2002. All rights reserved.ProfilesA profile is a named set of password and resource limits.Profiles are assigned to users by the CREATE USER or ALTER USER command.Profiles can be enabled or disabled.Profiles can relate to the DEFAULT profile.14-5Copyright Oracle Corporation, 2002. All rights reserved.UserPassword expiration and agingPassword verificationPassword historyAccount lockingSetting up profilesPassword Management14-6Copyright Oracle Corporation, 2002. All rights reserved.Set up password management by using profiles and assigning them to users.Lock, unlock, and expire accounts using the CREATE USER or ALTER USER command.Password limits are always enforced.To enable password management, run the utlpwdmg.sql script as the user SYS.Enabling Password Management14-7Copyright Oracle Corporation, 2002. All rights reserved.Password Account LockingParameterNumber of failed login attempts before lockout of the accountNumber of days the account is locked after the specified number of failed login attemptsFAILED_LOGIN_ATTEMPTSPASSWORD_LOCK_TIME Description14-8Copyright Oracle Corporation, 2002. All rights reserved.ParameterLifetime of the password in days after which the password expiresGrace period in days for changing the password after the first successful login after the password has expiredPASSWORD_LIFE_TIMEPASSWORD_GRACE_TIMEParameterPassword Expiration and Aging14-9Copyright Oracle Corporation, 2002. All rights reserved.Password HistoryParameterNumber of days before a password can be reusedMaximum number of changes required before a password can be reusedPASSWORD_REUSE_TIMEPASSWORD_REUSE_MAXDescription14-10Copyright Oracle Corporation, 2002. All rights reserved.Password VerificationParameterPL/SQL function that performs a password complexity check before a password is assignedPASSWORD_VERIFY_FUNCTIONDescription14-11Copyright Oracle Corporation, 2002. All rights reserved.User-Provided Password FunctionThis function must be created in the SYS schema and must have the following specification:function_name( userid_parameter IN VARCHAR2(30), password_parameter IN VARCHAR2(30), old_password_parameter IN VARCHAR2(30)RETURN BOOLEAN14-12Copyright Oracle Corporation, 2002. All rights reserved.Password Verification Function VERIFY_FUNCTIONMinimum length is four characters.Password should not be equal to username.Password should have at least one alphabetic, one numeric, and one special character.Password should differ from the previous password by at least three letters.14-13Copyright Oracle Corporation, 2002. All rights reserved.CREATE PROFILE grace_5 LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME UNLIMITED PASSWORD_LIFE_TIME 30 PASSWORD_REUSE_TIME 30 PASSWORD_VERIFY_FUNCTION verify_function PASSWORD_GRACE_TIME 5;Creating a Profile:Password Settings14-17Copyright Oracle Corporation, 2002. All rights reserved.ALTER PROFILE default LIMITFAILED_LOGIN_ATTEMPTS 3PASSWORD_LIFE_TIME 60PASSWORD_GRACE_TIME 10;Altering a Profile: Password SettingUse ALTER PROFILE to change password limits.14-19Copyright Oracle Corporation, 2002. All rights reserved.DROP PROFILE developer_prof;DROP PROFILE developer_prof CASCADE;Dropping a Profile: Password SettingDrop the profile using DROP PROFILE command.DEFAULT profile cannot be dropped.CASCADE revokes the profile from the user to whom it was assigned.14-21Copyright Oracle Corporation, 2002. All rights reserved.Resource ManagementResource management limits can be enforced at the session level, the call level, or both.Limits can be defined by profiles using the CREATE PROFILE command.Enable resource limits with the:RESOURCE_LIMIT initialization parameterALTER SYSTEM command14-22Copyright Oracle Corporation, 2002. All rights reserved.Enabling Resource LimitsSet the initialization parameter RESOURCE_LIMIT to TRUE.Enforce the resource limits by enabling the parameter with the ALTER SYSTEM command.ALTER SYSTEM SET RESOURCE_LIMIT=TRUE; 14-23Copyright Oracle Corporation, 2002. All rights reserved.ResourceCPU_PER_SESSION SESSIONS_PER_USERCONNECT_TIME IDLE_TIME LOGICAL_READS_PER _SESSIONPRIVATE_SGA DescriptionTotal CPU time measured in hundredths of secondsNumber of concurrent sessions allowed for each usernameElapsed connect time measured in minutesPeriods of inactive time measured in minutesNumber of data blocks (physical and logical reads)Private space in the SGA measured in bytes (for Shared Server only)Setting Resource Limitsat Session Level14-24Copyright Oracle Corporation, 2002. All rights reserved.ResourceCPU_PER_CALLLOGICAL_READS_PER_CALLDescriptionCPU time per call in hundredths of secondsNumber of data blocks that can be read per callSetting Resource Limitsat Call Level14-25Copyright Oracle Corporation, 2002. All rights reserved.CREATE PROFILE developer_prof LIMIT SESSIONS_PER_USER 2 CPU_PER_SESSION 10000 IDLE_TIME 60 CONNECT_TIME 480;Creating a Profile:Resource Limit14-28Copyright Oracle Corporation, 2002. All rights reserved.Managing Resources Using theDatabase Resource ManagerProvides the Oracle server with more control over resource management decisionsElements of the Database Resource Manager:Resource consumer groupResource planResource allocation methodResource plan directivesUses the DBMS_RESOURCE_MANAGER package to create and maintain elementsRequires ADMINISTER_RESOURCE_MANAGER privilege14-29Copyright Oracle Corporation, 2002. All rights reserved.Managing Resources Using theDatabase Resource ManagerResource plans specify the resource consumer groups belonging to the plan.Resource plans contain directives for how to allocate resources among consumer groups.14-31Copyright Oracle Corporation, 2002. All rights reserved.Resource Plan DirectivesThe Database Resource Manager provides several means of allocating resources:CPU methodActive session pool and queuingDegree of parallelism limitAutomatic consumer group switchingMaximum estimated execution timeUndo quota14-33Copyright Oracle Corporation, 2002. All rights reserved.Obtaining Password and Resource Limit InformationInformation about password and resource limits can be obtained by querying the following views:DBA_USERSDBA_PROFILES14-35Copyright Oracle Corporation, 2002. All rights reserved.SummaryIn this lesson, you should have learned how to:Administer passwordsAdminister profilesObtain password and resource limit information14-36Copyright Oracle Corporation, 2002. All rights reserved.Practice 14 OverviewThis practice covers the following topics:Enabling password managementDefining profiles and assigning to usersDisabling password management