Administering Profiles

Profile administration isn't likely something that will take up a lot of your time as a DBA. After you establish a set of profiles, they are usually stable until you determine that you need to add a new one for a business purpose. This section helps with your administration tasks when you need them.

Creating Profiles

You need to create a profile if you want to centrally maintain password rules in your database.

Setting Password Limits

The following command creates a profile that sets password limits and that can later be assigned to a user group that has been determined to require this given set of limits:

 CREATE PROFILE custom_grace_5 LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME UNLIMITED PASSWORD_LIFE_TIME 90 PASSWORD_REUSE_TIME 360 PASSWORD_VERIFY_FUNCTION my_custom_verification PASSWORD_GRACE_TIME 5; 

The following syntax for the CREATE PROFILE command provides the basics of the creation script for password verification using the profile:

 CREATE PROFILE profilename LIMIT [FAILED_LOGIN_ATTEMPTS maximum_value] [PASSWORD_LIFE_TIME maximum_value] [ {PASSWORD_REUSE_TIME |PASSWORD_REUSE_MAX} maximum_value] [PASSWORD_LOCK_TIME maximum_value] [PASSWORD_GRACE_TIME maximum_value] [PASSWORD_VERIFY_FUNCTION {custom function|NULL|DEFAULT|VERIFY_FUNCTION} ] 

And the definitions of the CREATE PROFILE script follow:

• Profile name is the name of the profile that you are creating.

• FAILED_LOGIN_ATTEMPTS is the number of failed attempts to log in before the user account becomes locked.

• PASSWORD_LIFE_TIME is the number of days that the user can maintain the same password. After this period, if no PASSWORD_GRACE_TIME is specified, the user account is locked.

• PASSWORD_REUSE_TIME is the number of days that must elapse before a password can be reused. If you set this parameter to an integer, you must set PASSWORD_REUSE_MAX to UNLIMITED.

• PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused. If you set PASSWORD_REUSE_MAX to an integer value, you have to set PASSWORD_REUSE_TIME to UNLIMITED.

• PASSWORD_LOCK_TIME is the number of days the account will be locked after the specified number of failed login attempts locks the account. After this period, the account will automatically unlock.

• PASSWORD_GRACE_TIME is the number of days after the grace period begins in which the user has to change the account password. If the password is not changed during this time, the password expires.

• PASSWORD_VERIFY_FUNCTION can be set to either DEFAULT, NULL (none), the Oracle provided VERIFY_FUNCTION, or to the custom function that you create to verify passwords for the profile.

Assigning Profiles

After a profile is created, you can assign it to one or more database users. Each user can be assigned only one profile at any given time. If a user is assigned a second profile, that profile overwrites the previously assigned profile.

You can assign profiles to users with either the CREATE USER command or the ALTER USER command.

Altering Profiles

You can alter any profile that you have created with the ALTER PROFILE command. If you want to set a parameter to less than a day, you can use the following fractions or similar fractional values that will give you exactly the granularity you want (2 hours, 13 hours, 28 minutes).

• 1 hour is 1/24.

• 10 min is 10/1440.

• 1 min is 1/1400.

• 12 hours is 12/24.

Changes made to a profile take effect only on subsequent logins; currently connected sessions are not affected.

Dropping Profiles

You can drop profiles as follows when they are determined to no longer be needed:

 DROP PROFILE profile_name; 

or

 DROP PROFILE profile_name CASCADE; 

Using the CASCADE keyword revokes the profile from all the users to whom it was assigned and automatically reassigns those users to the DEFAULT profile. Use of this keyword is the only way to drop a profile that has users assigned to it. When a profile is dropped, the changes apply only to sessions that connect after the drop. Connected sessions are not affected.

You can never drop the DEFAULT profile.