Information on the password character string restrictions that have been specified is stored in the SQL_SYSPARMS dictionary table. To determine the restrictions that are currently in effect, search SQL_SYSPARMS and check the specification information on password character string restrictions. An example of such a search follows.

Example

Check the password restrictions that are currently in effect:

 
SELECT FUNCTION_KEY,PARAM_KEY,INT_VALUE,CHAR_VALUE
    FROM MASTER.SQL_SYSPARAMS
      WHERE FUNCTION_KEY='PASSWORD'
 

Execution results

FUNCTION_KEY PARAM_KEY INT_VALUE CHAR_VALUE ------------------------------------------------ PASSWORD MIN_LENGTH 8 8 PASSWORD USER_IDENTIFIER NULL RESTRICT PASSWORD SIMILAR NULL RESTRICT

Explanation

The following settings are in effect:

• Minimum length (number of bytes) for a password: 8
• Inclusion of the authorization identifier in the password: Prohibited (RESTRICT)
• Use of only one type of characters in a password: Prohibited (RESTRICT)

If not prohibited, UNRESTRICT is displayed.

Evaluate the password restrictions you intend to set or change. For details, see Table 25-3 Restrictions that can be set for passwords.

Check in advance for existing users whose existing password will not conform to the proposed new set of restrictions. Because the nonconforming users will be placed in password-invalid account lock state, they will no longer be permitted to connect to HiRDB. Before establishing a restriction, you should identify the users whose existing password will be in violation of the restriction. For details about the identification procedure, see 25.5 Checking for users who will be placed in password-invalid account lock state.

Reference note

Setting up or changing a password character string restriction might cause some users to be placed in password-invalid account lock state. You should notify all users in advance about the imminent change in password restrictions and inform them that they might have to change their password before the implementation date. Once the implementation date arrives, check for users who are in violation of the new set of restrictions and contact them.

Use a GRANT statement to change the password of a user whose existing password does not conform to the new set of password restrictions. Examples follow.

Example 1

Change the password of USER01 to f51HD7tc:

 
GRANT CONNECT TO USER01 IDENTIFIED BY "f51HD7tc"
 

Reference note

Each user can change his or her own password. Passwords can also be changed by a DBA privilege holder.

Example 2

Change the password of DBA privilege holder ADMIN01 to gd4A@sPL:

 
GRANT DBA TO ADMIN01 IDENTIFIED BY "gd4A@sPL"

Example 3

Change the auditor's password to a0h7Fc3K:

 
GRANT AUDIT IDENTIFIED BY "a0h7Fc3K"
 

The procedure for changing the password character string restrictions follows.

Procedure

1. Use DROP CONNECTION SECURITY to cancel the password character string restrictions. For details about how to cancel the password character string restrictions, see 25.7 Canceling the password character string restrictions.
2. Use CREATE CONNECTION SECURITY to specify the new password character string restrictions.
   

   Reference note

   Even if you are not changing all the password character string restrictions (for example, you wish to change only the minimum number of bytes for a password), you must first use DROP CONNECTION SECURITY to cancel all the password character string restrictions, then use CREATE CONNECTION SECURITY to specify the new set of restrictions.

Check for users in password-invalid account lock state. For details about how perform this check, see 25.4.1(1) Check for users in password-invalid account lock state.