This article originally appeared in the Oracle e-newsletter. Click

here to subscribe automatically.

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 these two statements:

CREATE USER myuser . . . PROFILE myprofile;
ALTER USER myuser PROFILE myprofile;

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 the 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
(
    userid varchar(30),
    password varchar(30),
    old_password varchar(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;

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software

development. For more of his Oracle tips visit our Oracle Dev Tips

Library.