8.11 How do I…Manage passwords?Problem
I need to be able to assign password enforcement rules to conform to company standards, such as forcing users to change their passwords every 30 days. Other rules I would like to enforce are to ensure a user does not repeat a recently used password, and that a user is locked out of the database if they enter an invalid password a couple of times. How do I manage passwords?
Technique
Use the CREATE PROFILE command to enforce and modify password management. After creating a profile, assign it to a user account. View password information by querying the DBA_USERS data dictionary view for ACCOUNT_STATUS (unlocked, locked, expired), LOCK_DATE (when the account was locked), and EXPIRY_DATE (date and time when the account expires). You can also query the DBA_PROFILES view to see which password values are set for each profile.
Several new additions are included with the CREATE PROFILE and ALTER PROFILE commands, which are shown in Table 8.2.
Table 8.2 New profile options
Profile Option Description FAILED_LOGIN_ATTEMPTS Locks account after the user enters an incorrect password for FAILED_LOGIN_ATTEMPTS times, cumulative. PASSWORD_GRACE_TIME Number (or fraction) of days that a user can login with an expired password until a password change occurs. PASSWORD_LIFE_TIME Number (or fraction) of days before a password expires. PASSWORD_LOCK_TIME Number (or fraction) of days that the user is locked out of the database after entering invalid passwords, greater than the # defined in FAILED_LOGIN_ATTEMPTS. UNLIMITED can also be specified, in which case the user is locked out of the database indefinitely until it is manually unlocked. PASSWORD_REUSE_MAX Number of times a user must change the password before it can be reused. For example, setting this to 3 requires a user must change the password to three other passwords before a password can be reused. PASSWORD_REUSE_TIME Number (or fraction) of days before a password can be reused. For example, you can set this to 365 so a year must pass before you can reuse a password. PASSWORD_VERIFY_FUNCTION Checks the password to see whether an intruder can easily break the password. You can use either PASSWORD_REUSE_MAX or PASSWORD_REUSE_TIME, but not both. The PASSWORD_VERIFY_FUNCTION comes with a default function, called verify_function, which verifies that each user’s password meets minimum protection from intruders trying to guess it. This includes ensuring the password is greater than four characters, contains at least one digit, one character, and one punctuation mark, and is three characters or more different from the previous password. The password cannot be one of several predefined passwords: abcd, account, computer, database, oracle, password, user, or welcome.
Steps
1. Connect to SQL*Plus as the WAITE user account and run CHP8_37.SQL to create a profile. The profile enforces password control, which is shown in Figure 8.38.
The NORMAL_USERS profile forces the user to change their password after every 30 days. It also locks the account out of the database for one hour if the user tries to connect with an invalid password after three times.
2. Create the sample user account BRENDAN for this How-To by running CHP8_38.SQL, shown here:
SQL> GET CHP8_38.sql
1 CREATE USER BRENDAN
2 IDENTIFIED BY CLOVER
SQL> /
User created.
SQL>
3. Assign the newly created NORMAL_USER profile to the user account by running CHP8_39.SQL, shown here:
SQL> GET CHP8_39.sql
1 ALTER USER BRENDAN
2 PROFILE NORMAL_USERS
SQL> /
User created.
SQL>
Now the BRENDAN user account is assigned to the x-profile, along with all the associated password security.
4. Another password control capability is to expire passwords to enforce users to change them on their first connection to the database. Run CHP8_40.SQL to expire the BRENDAN user’s password:
SQL> GET CHP8_40.sql
1 ALTER USER BRENDAN
2 PASSWORD EXPIRE;
SQL> /
User altered.
SQL>
5. Query the DBA_USERS view to review user password expiration status by running CHP8_41.SQL. Pass BRENDAN for the username, as shown in Figure 8.39.
6. Query the DBA_PROFILES view to see which password values are set for each profile, by running CHP8_42.SQL. The script is shown in Figure 8.40 and the results are shown in Figure 8.41. Pass NORMAL_USER when prompted for profile_name.
How It Works
Step 1 creates the NORMAL_USERS profile with sample password limitations. The profile forces the user to change their password after every 30 days, and locks the account out of the database for one hour if the user tries to connect with an invalid password after three times. Step 2 creates the BRENDAN sample user account. Step 3 associates the NORMAL_USERS profile to the BRENDAN user account. Step 4 expires the BRENDAN account’s password, so that the next time BRENDAN logs in, the password must be changed. Step 5 queries the DBA_USERS view to review user password expiration status. Step 6 queries the DBA_PROFILES view to determine the password enforcement associated with a particular profile.
Comments
The default profile for all users has their password options set to those shown in Table 8.3. For example, the default PASSWORD_LOCK_TIME is 1/1440 and corresponds to 1 minute (1/1440 of a day).
Profile Option Default Value FAILED_LOGIN_ATTEMPTS 3 PASSWORD_GRACE_TIME 10 PASSWORD_LIFE_TIME 60 PASSWORD_LOCK_TIME 1/1440 PASSWORD_REUSE_MAX UNLIMITED PASSWORD_REUSE_TIME 1800 PASSWORD_VERIFY_FUNCTION verify_function