iLAMP

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 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.

# posted by MiDBA @ 12/27/2004 10:37:00 AM