Execute CREATE CONNECTION SECURITY with the TEST option specified. When the TEST option is specified, a violation type code is set in the PASSWORD_TEST column of the SQL_USERS dictionary table on the line for any user who would be in violation of a proposed restriction specified in the same CREATE CONNECTION SECURITY statement.

Reference note

When the TEST option is specified, only checking of the proposed password character string restrictions is performed. Violators are not placed in password-invalid account lock state.

The following example checks in advance for users who would be placed in password-invalid account lock state by proposed character string restrictions.

Example

Check for violators if the following character string restrictions were set for passwords:

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

 
CREATE CONNECTION SECURITY
    FOR PASSWORD TEST             ...1
      MIN LENGTH 8                ...2
      USER IDENTIFIER RESTRICT    ...3
      SIMILAR RESTRICT            ...4
 

Explanation

1. Specifies the TEST option for checking in advance.
2. The minimum number of bytes permitted for a password would be 8.
3. Inclusion of the user's authorization identifier in that user's password would be prohibited.
4. Use of only one type of characters in a password would be prohibited.

When this SQL statement is executed, the passwords of all users who are registered in SQL_USERS are checked, and a violation type code is set in the PASSWORD_TEST column of SQL_USERS for any user whose password would be in violation of any of the specified proposed restrictions.

Hint

• If the specification contents of the CREATE CONNECTION SECURITY SQL for an advance check are not the same as the specification contents when the password character string restrictions are actually set, even users who corrected their passwords might be placed in password-invalid account lock state. It is important that the CREATE CONNECTION SECURITY SQL specified for the advance check be the same as the SQL executed for setting the password character string restrictions (other than for the TEST option).
• After the advance check is executed, instruct only the users who will be in violation of the new character string restrictions to change their passwords. Also make sure that the password of any new user who is registered complies with the new password restrictions.

Violation type codes are set in the PASSWORD_TEST column of SQL_USERS. Search the PASSWORD_TEST column to identify users whose existing password will cause them to be placed in password-invalid account lock state. The following table shows the violation type codes that are set. If there is no violation, the NULL value is set.

Table 25-4 Violation type codes set in the PASSWORD_TEST column

Order number	Item	Violation type code set in the PASSWORD_TEST column
1	Violation of the minimum number of bytes for a password	L
2	Violation of the prohibition on inclusion of the authorization identifier in the password	U
3	Violation of the prohibition on use of only one type of characters in a password	S

Note

If a password violates multiple items, only one violation type code is set, depending on the item order numbers. For example, if a password violates the items with order numbers 1 and 2, L (the violation type code for item 1) is set.

Examples of checks for users in password-invalid account lock state are shown below.

Example 1

Obtain a list of users who are in violation of a password character string restriction:

 
SELECT USER_ID
   FROM MASTER.SQL_USERS
     WHERE PASSWORD_TEST IS NOT NULL
 

Execution results

USER_ID ----------- USER1 USER2 USER3

Explanation

USER1, USER2, and USER3 are in violation of a password character string restriction.

Example 2

Obtain a list of DBA privilege holders and auditors who are in violation of a password character string restriction:

 
SELECT USER_ID
    FROM MASTER.SQL_USERS
      WHERE PASSWORD_TEST IS NOT NULL
        AND (DBA_PRIVILEGE = 'Y' OR AUDIT_PRIVILEGE = 'Y')
 

Execution results

USER_ID ----------- AUDITOR1 DBA1 DBA2

Explanation

DBA privilege holders DBA1 and DBA2, as well as the auditor (AUDITOR1), are in violation of a password character string restriction.