Authenticating users in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize how to implement database security by configuring user profiles.

1. Standard password security features

You can use user profiles to implement password management in Oracle Database 10g.

Note

When you create a user, the default profile is assigned to them unless you specify a different profile.

User profiles can provide many standard security features.

• Password account locking
• Password expiration and aging
• Password history
• Password complexity verification

Password account locking

When users fail to log in to the system in a specified number of attempts, you can automatically lock their accounts for a set duration.

Password expiration and aging

Each user's password has a set lifetime. When that lifetime is over, the passwords must be changed.

Password history

New passwords can be checked to ensure that they are not reused for a certain amount of time or a certain number of password changes.

Password complexity verification

Password complexity verification checks whether the password meets certain rules. This ensures the password is complex enough to provide protection against intruders who might try to break into the system by guessing the password.

Question

What standard security features do user profiles provide?

Options:

1. Password account locking
2. Password expiration and aging
3. Password history
4. Username complexity verification

Answer

The standard security features provided by user profiles include password account locking, password expiration and aging, and password history.

Option 1 is correct. Password account locking enables automatic locking of accounts for a set duration when users fail to log in to the system in the specified number of attempts.

Option 2 is correct. Password expiration and aging enables user passwords to have a lifetime, after which they expire and must be changed.

Option 3 is correct. Password history checks the new password to ensure that an old password is not reused for a specified amount of time or a specified number of password changes.

Option 4 is incorrect. User profiles do not provide username complexity verification as a standard security feature - however, they do provide password complexity verification. This checks whether the password is complex enough to provide protection against intruders who might try to break into the system by guessing the password.

Suppose you are using Enterprise Manager to configure password security settings for the default profile.

To view the default profile, you first click Profiles.

Then you select the DEFAULT profile entry and click View.

On the View Profile page, because you want to alter your settings, you click Edit.

Next you select the Password tab.

The Password tabbed page allows you to set expiry and lock limits, history logs, complexity settings and failed login information. You can choose either DEFAULT or UNLIMITED as the setting for the profile, or enter a numerical value.

You want the password to expire in 90 days and be locked for 5 days once it has expired, so you enter 90 in the Expire in (days) field and 5 in the Lock (days past expiration) field.

You want to keep passwords valid for 30 days, so you enter 30 in the Number of days to keep for field.

You accept the default value - NULL - in the Complexity function field.

Now, you wish to change the number of times a user can fail to log in before their account is locked, and specify the duration for which the account will be locked. You then want to view the SQL used to generate these settings.

You enter 4 in the Number of failed login attempts to lock after field and 1 in the Number of days to lock for field and click Show SQL.

The SQL code that implements your chosen settings is displayed.

You then click Return to go back to the Edit Profile page.

On the Edit Profile page, you click Apply to implement your chosen settings.

Enterprise Manager displays an update message confirming that you have set the parameters successfully.

Question

Suppose you are using Enterprise Manager to configure password security features for the DEFAULT profile. You need to set passwords to expire every 60 days. You also need to configure accounts to be locked automatically after four failed logins.

Which sequence of steps would you use to complete this task?

Options:

1. You enter 60 in the Expire in (days) field. Then you enter 4 in the Number of failed login attempts to lock after field and click Apply.
2. You enter 60 in the Expire in (days) field. Then you enter 4 in the Number of days to lock for field and click Apply.

Answer

To set passwords to expire every 60 days, you enter 60 in the Expire in (days) field. Then, to lock accounts automatically after four failed logins, you enter 4 in the Number of days to lock for field and click Apply.

2. Password parameters

There are a number of parameters you can configure when implementing password security.

The FAILED_LOGIN_ATTEMPTS parameter determines how many times a user can fail to log in before the Oracle server automatically locks their account.

The account is automatically unlocked after a specified time determined by the value of the PASSWORD_LOCK_TIME parameter. If you want to unlock the account before the PASSWORD_LOCK_TIME value has been reached, you can do so using the ALTER USER command.

After an account has been explicitly locked with the ALTER USER command or by using Enterprise Manager, you need to unlock it manually.

You can specify a grace period using the PASSWORD_GRACE_TIME parameter. The grace period begins after the user's first attempt to log in to the database after their password has expired.

During this grace period, every time the user tries to log in, a warning message is displayed. If a user does not change their password within the grace period, their account is locked.

If you want to expire a user's account manually, you can set the password to expired.

Password history checks limit the reuse of old passwords.

You can implement these checks using the following parameters:

• PASSWORD_REUSE_TIME
• PASSWORD_REUSE_MAX

PASSWORD_REUSE_TIME

PASSWORD_REUSE_TIME specifies the number of days before a password can be reused.

PASSWORD_REUSE_MAX

PASSWORD_REUSE_MAX specifies the number of password changes required before the current password can be reused.

The PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters are mutually exclusive, so when one parameter is set to a certain value, the other parameter must be set to UNLIMITED.

The Oracle server provides a PL/SQL function to verify the validity of a new password.

To use this, you run the SQL script located at $ORACLE_HOME/rdbms/admin/utlpwdmg.sql.

Alternatively, you can write a custom PL/SQL function that meets your individual security requirements.

The Oracle server also provides a complexity-verification function named VERIFY_FUNCTION. This function is created with the script $ORACLE_HOME/rdbms/admin/utlpwdmg.sql.

Note

You need to create the password-verification function in the SYS schema.

Question

Match these password parameters to their functionality.

Options:

1. PASSWORD_GRACE_TIME
2. PASSWORD_REUSE_TIME
3. VERIFY_FUNCTION

Targets:

1. Specifies the number of days during which login is still allowed and a warning is issued
2. Specifies the number of days before a password can be reused
3. Makes a password-complexity check before a password is assigned

Answer

PASSWORD_GRACE_TIME specifies the number of days during which login is still allowed. PASSWORD_REUSE_TIME specifies amount of time before a password can be used again. And VERIFY_FUNCTION is used to check the complexity of the password before it is assigned.

If a user does not change their password within the grace period, their account is locked.

When this parameter is set to a certain value, the PASSWORD_REUSE_MAX parameter must be set to UNLIMITED.

VERIFY_FUNCTION is created with the utlpwdmg script.

3. Creating a password profile

You can use Enterprise Manager to create new password profiles or edit existing ones.

Say, for instance, you want to create a new profile. You first need to access the Profiles page.

You click Profiles to do this.

On the Profiles page, you click the Create button.

The Create Profile page displays.

You need to enter a name for your profile, so you decide to call it "MyProfile".

You then click the Password tab to modify the password settings associated with the profile.

You can set password parameters by entering DEFAULT, UNLIMITED or a numerical value in the appropriate field.

You enter 90 in the Expire in (days) field.

You enter 10 in the Lock (days past expiration) field.

You enter UNLIMITED in the Number of passwords to keep field.

You enter 120 in the Number of days to keep for field.

You enter 3 in the Number of failed login attempts to lock after field.

All time periods are expressed in days, but can be expressed as fractions. For example, there are 1440 minutes in a day, so 5/1440 is equal to five minutes.

In the Number of days to lock for field, you enter 5/1440 to lock the account for 5 minutes after 3 failed logins.

To view the SQL code that implements your chosen settings, you then click Show SQL.

The SQL code that implements your chosen settings is displayed.

You click Return to go back to the Create Profile page.

To save your password profile settings, you then click OK.

Enterprise Manager displays an update message confirming that you have set the parameters correctly.

Question

You want to create a new password profile called "MyProfile" that has unlimited password expiry.

Which sequence of steps would you use to complete this task?

Options:

1. Click Create. Enter MyProfile in the Name field. Click Password. Type UNLIMITED in the Expire in (days) field. Click OK.
2. Click Create. Enter MyProfile in the Name field. Type UNLIMITED in the Number of days to keep for field. Click OK.

Answer

You click Create and enter MyProfile in the Name field. Then you click Password and type UNLIMITED in the Expire in (days) field. Finally, you click OK.

4. Assigning users to a password profile

A user can only have one profile in effect at a time. If users are already logged in when you change their profile, the change does not take effect until their next login.

Note

User accounts can be locked or expired from the Edit User page.

Suppose you want to assign the MyProfile profile you've just created to Andrea Horner.

On the Administration tabbed page of Enterprise Manager, you click Users.

Andrea Horner is selected by default, so you click the Edit button to edit her profile.

The Edit User page is displayed. This page allows you to select the profile that you want to apply to the user. The user's profile is currently set to DEFAULT.

You want to assign the MyProfile password profile to Andrea Horner.

You click the down-pointing arrow in the Profile field and select MYPROFILE.

Then, you click the Apply button.

The MyProfile profile is now assigned to Andrea Horner.

Question

You now want to assign the MyProfile profile to the user Andrew Chung.

Which sequence of steps would you use to complete this task?

Options:

1. Select the Andrew Chung user and click Edit. Click the down-pointing arrow in the Profile field and select MYPROFILE. Click Apply.
2. Select the Andrew Chung user and click Create. Click the down-pointing arrow in the Profile field and select DEFAULT. Click Apply.

Answer

You select the Andrew Chung user and click Edit. Then you click the down-pointing arrow in the Profile field and select MYPROFILE. Finally, you click Apply.

Summary

To manage passwords in Oracle Database 10g, you use user profiles. You can lock accounts after failed logins, expire passwords, and limit the reuse of old passwords. A new password can also be checked automatically to ensure that it is not easy to guess.

There are a number of parameters you can use to implement password security. For example, PASSWORD_GRACE_TIME specifies the number of days during which login is still allowed, and PASSWORD_REUSE_TIME specifies the amount of time before a password can be used again.

You can use Enterprise Manager to create new password profiles. You can set password parameters by entering DEFAULT, UNLIMITED, or a numerical value in the relevant fields.

Users can have only one profile in effect at any given time. If they are already logged in when you change their profile, the change won't take effect until their next login. You can assign a password profile to a user by editing their profile in Enterprise Manager.