SkillBuilders | Oracle 9i Administration | Lesson 9: Password Management

Instructor's Notes

(If applicable)

Lesson 9: Oracle 9i Password Management
This lesson will teach you how to use the user password-related features available with the Oracle database (most of these features were introduced with 8i).
Reminder: I strongly recommend you have the Oracle documentation available as you take this course. It is available free at http://Technet.Oracle.Com.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Password aging allows the DBA to set a lifespan on a password. The password lifespan starts:

Upon password change
First login after profile assigned

A grace period can be specified which allows the user some amount of time to change the password. The grace period starts upon first login after password expiration. A warning message issued about impending expiration. Note that SQL*Plus has built-in routines to prompt for new password.
Most password management features are implemented via a profile. A profile is an Oracle object that can be associated with a user to control passwords and put limits on resources such as CPU time. Profiles are created (as demonstrated above) with the CREATE PROFILE statement. The user is the assigned to the profile with the ALTER USER statement. Refer to the Oracle9i SQL Reference for complete descriptions of these statements.
Supplemental Notes
- All Oracle databases have a default profile (aptly named “DEFAULT”). Adjusting the parameters (LIMITS) in the default profile affects all users that are not directly associated with a profile limit that overrides the DEFAULT profile limit.

Other Text:
(Examples or comments displayed on slide, if any).

CREATE PROFILE pwd LIMIT
PASSWORD_LIFE_TIME 30
PASSWORD_GRACE_TIME 2;

ALTER USER dave PROFILE pwd;

Query DBA_PROFILES to see all profiles

Instructor's Notes

(If applicable)

The DBA can also explicitly force the expiration of a password with the ALTER USER statement. This can simplify things for the DBA. For example, the DBA can create a new user with password “WELCOME”, but expires the password. Upon first login, the user knows his or her initial password is WELCOME, but is forced to change the password.

Mini-Workshop
1. Create a new user.
2. Expire the password of the new user.
3. Grant the new user CREATE SESSION privilege.
4. Connect to the new user with SQL*Plus. What happens?

Other Text:
(Examples or comments displayed on slide, if any).

ALTER USER dave PASSWORD EXPIRE;

Do the mini-workshop

Instructor's Notes

(If applicable)

Another password management feature is the ability to lock an account after some number of failed login attempts. This helps prevent hacking into the database with a program that keeps trying passwords until it finds the correct password.
The PASSWORD_LOCK_TIME specifies the amount of time (in days) the account is locked due to failed login attempts. In the example above, we expire the password after 3 attempts; it will be locked for 1 minute. Alternatively, you could choose PASSWORD_LOCK_TIME UNLIMITED, in which case the account is locked until explicitly unlocked with the ALTER USER command.

Other Text:
(Examples or comments displayed on slide, if any).

CREATE PROFILE x LIMIT
FAILED_LOGIN_ATTEMPTS 3 -- 3 attempts
PASSWORD_LOCK_TIME 1/1440; -- 1 minute
ALTER USER dave PROFILE x;
ALTER USER username ACCOUNT [LOCK|UNLOCK]

Instructor's Notes

(If applicable)

The password history feature prevents a user from reusing the same password repeatedly. There are two variations: reuse time and reuse number. Reuse time specifies the number of days that must pass before a password can be reused again. Reuse number specifies the number of different passwords the user must use before they can reuse the a password. Note that PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are mutually exclusive. One must always be UNLIMITED.
For example, if PASSWORD_LIFE_TIME is 30 and PASSWORD_REUSE_MAX is 5, then the user must change his or her password at least once every 30 days, and must use at least 5 different passwords before he or she may reuse a previously used password.

Other Text:
(Examples or comments displayed on slide, if any).

CREATE PROFILE x LIMIT
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX UNLIMITED;

CREATE PROFILE x LIMIT
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX 5;

Instructor's Notes

(If applicable)

The final password management feature is password complexity verification. This feature allows us to create a PL/SQL function to intercept and review all new password requests. We can then insure some level of complexity in the password. For example, we can prevent users from using their username as a password, using 1 or 2 character strings as a password or force the password to be at least 4 characters. The possibilities are endless.
You must connect to SYS as SYSDBA before you create the PASSWORD_VERIFY_FUNCTION.
Note that Oracle supplies sample routine called $ORACLE_HOME/RDBMS/ADMIN/UTLPWDMG.SQL. Use this routine as a template and customize to your own needs.

Other Text:
(Examples or comments displayed on slide, if any).

Must connect as SYSDBA

connect system/dave as sysdba

CREATE OR REPLACE FUNCTION verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS

Return TRUE if new password is OK

Associate user with verification routine

create profile pwd limit
password_verify_function verify_function;

alter user dave profile pwd;

Instructor's Notes

(If applicable)

User Passwords Workshop
1. Install the supplied function VERIFY_FUNCTION. The code to create this function is in the VERIFY_PASSWORD.SQL file supplied.
2. Create a new profile called PWD that specifies VERIFY_FUNCTION for the PASSWORD_VERIFY_FUNCTION limit.
3. Associate your username with this profile.
4. Connect with your username and use the SQL*Plus password command to change your password. Use only 4 characters as the password. The verify routine should reject your new password.
5. Change your password to “welcome”. The verify routine should reject your new password.
6. Change your password to 5 or more characters. The verify routine should accept your new password (unless you use one of the simple words that the routine rejects).

Other Text:
(Examples or comments displayed on slide, if any).