Most Oracle database users create user accounts with the
default profile. Since Oracle 8, it’s possible to lock an account by creating a
profile and assigning it to a user with either of the two statements in
Listing A.

A typical attempt to break into a database account is to try
several commonly used passwords, such as “welcome” or the username.
You can prevent multiple failed attempts at logging in by using the profile tag
FAILED_LOGIN_ATTEMPTS:

CREATE PROFILE myprofile LIMIT
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME 1;

Users assigned to this profile will be locked out of their
accounts after five login attempts with an incorrect password. The account will
be inaccessible for one day or until a DBA issues this command:

ALTER USER <username> ACCOUNT UNLOCK.

Even after several years, I’ve found that my old password
still works on previous projects. This makes a good case for placing a limit on
a password’s lifetime so it will expire after a certain period (e.g., at the
end of a contract). There’s also an option to allow a specific grace period,
which is useful for projects that aren’t used very often. If the user doesn’t
log in until after the password expires, the user can still connect, but a
warning will display until the grace period expires. Use the PASSWORD_LIFE_TIME
and PASSWORD_GRACE_TIME tags on a profile to enable these features.

ALTER PROFILE myprofile LIMIT
    PASSWORD_LIFE_TIME 30
    PASSWORD_GRACE_TIME 3;

Users assigned to that profile will be locked out of their
accounts 30 days after the last time the password is changed. After 30 days,
attempting to log in will result in warning messages for three more days before
the account is locked.

Many users will see these limits and simply try to reset
their passwords to what they were previously using rather than using a new
password each time. You can prevent users from reusing a password with the
PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX tags.

ALTER PROFILE myprofile LIMIT
    PASSWORD_REUSE_TIME 30
    PASSWORD_REUSE_MAX 100;

Users with this profile will not be able to reuse a password
for 30 days, or until after they change the password 100 times.

Finally, some users will use passwords that are easy to
guess. It’s possible to restrict a password’s format (such as checking for a
minimum width, letters, numbers, or mixed case, or verifying that the password
isn’t a variation of the username) by creating a PL/SQL procedure that
validates passwords. You must format the procedure like this:

CREATE OR REPLACE FUNCTION verify_password
(
    useridvarchar(30),
    password varchar(30),
    old_passwordvarchar(30)
) RETURN BOOLEAN

You can assign this function (which can be any name, but it
must be owned by the SYS account) with the following:

ALTER PROFILE myprofile LIMIT
PASSWORD_VERIFY_FUNCTION verify_password;

TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!