Profiles, Password and Resource Control for Oracle Database XE

Profiles and Password Control

Table 31-3 shows the password-related profile parameters. All units of time are specified in days (e.g., to specify any of these parameters in minutes, divide by 1440):

SQL> create profile lim_lock limit password_lock_time 5/1440;
Profile created.

In this example, an account will only be locked for five minutes after the specified number of login failures.

Table 31-3. Password-Related Profile Parameters  

A parameter value ofUNLIMITEDmeans that there is no limit on how much of the given resource can be used.DEFAULT means that this parameter takes its values from theDEFAULTprofile.

The parametersPASSWORD_REUSE_TIMEandPASSWORD_REUSE_MAXmust be used together; setting one without the other has no useful effect. In the following example, we create a profile that setsPASSWORD_REUSE_TIMEto 20 days andPASSWORD_REUSE_MAXto 5:

create profile lim_reuse_pass limit  
     password_reuse_time 20
     password_reuse_max 5;

Users with this profile can reuse their passwords after 20 days if the password has been changed at least five times. If you specify a value forPASSWORD_REUSE_TIMEorPASSWORD_REUSE_MAX, andUNLIMITEDfor the other, a user can never reuse a password.

If you want to provide tighter control over how passwords are created and reused, such as using a mixture of uppercase and lowercase characters in every password, you need to enable thePASSWORD_VERIFY_FUNCTIONlimit in each applicable profile. Oracle provides a template for enforcing an organization’s password policy. It’s located in$ORACLE_HOME/rdbms/admin/utlpwdmg.sql. The script provides the following functionality for password complexity:

1. Ensures that the password is not the same as the username
   
2. Ensures that the password is at least four characters long 
    
3. Checks to make sure the password is not a simple, obvious word, such asORACLE orDATABASE 
    
4. Requires that the password contains one letter, one digit, and one punctuation mark 
    
5. Ensures that the password is different from the previous password by at least three characters

To use this policy, the first step is to make your own custom changes to this script. For example, you may wish to have several different verify functions, one for each country or business unit, to match the database password complexity requirements to that of the operating systems in use in a particular country or business unit. Therefore, you can rename this function asVERIFY_FUNCTION_US_WESTCOAST, for example. In addition, you might want to change the list of simple words to include names of departments or buildings at your company.