Using SQL to check the user password rules

I am sure when we sign on to a new partition and are prompted to change our user profile's password we spend several minutes trying to find a password we like that matches the partition's password rules.

As part of the new release IBM i 7.5 and 7.4 TR6 comes a new SQL table function that allows me to validate passwords to the partition's rules. SQL table function CHECK_PASSWORD uses all of the password system values in this hierarchy:

• QPWDRQDDIF:  Required Difference in Passwords
• Either:
  • QPWDRULES:  Password Rules
• Or:
  • QPWDMINLEN:  Minimum Length of Passwords
  • QPWDMAXLEN:  Maximum Length of Passwords
  • QPWDLMTAJC:  Restriction of Consecutive Digits for Passwords
  • QPWDLMTCHR:  Restricted Characters for Passwords
  • QPWDLMTREP:  Restriction of Repeated Characters for Passwords
  • QPWDRQDDGT:  Requirement for Numeric Character in Passwords

I can check the contents of these System Values using the SYSTEM_VALUE_INFO View. Below is the statement I used to retrieve all of these values:

01 SELECT SYSTEM_VALUE_NAME AS "System value", 02 CHAR(CASE 03 WHEN CURRENT_CHARACTER_VALUE IS NULL THEN CHAR(CURRENT_NUMERIC_VALUE) 04 ELSE CURRENT_CHARACTER_VALUE 05 END, 10) AS "Value" 06 FROM QSYS2.SYSTEM_VALUE_INFO 07 WHERE SYSTEM_VALUE_NAME IN ('QPWDRQDDIF', 08 'QPWDRULES', 09 'QPWDMINLEN','QPWDMAXLEN','QPWDLMTAJC', 'QPWDLMTCHR','QPWDLMTREP','QPWDRQDDGT')

Lines 2 – 5: The values of the System Values are returned in either a numeric or a character column. I am using a CASE function to convert the numeric values to character. I am surrounding this with a CHAR function to limit the size of the derived columns to ten characters.

Lines 7 – 9: These are the system values I described above.

On this partition the results are (your partition may be different):

System value Value ------------ ---------- QPWDRQDDIF 3 QPWDLMTAJC 0 QPWDLMTREP 0 QPWDRQDDGT 1 QPWDMINLEN 6 QPWDMAXLEN 10 QPWDLMTCHR *NONE QPWDRULES *PWDSYSVAL

The results are returned in System Value name order. As QPWDRULES, last row of the results, is *PWDSYSVAL the other System Values are used for the password rules.

The CHECK_PASSWORD Table function can be passed two parameters:

1. PASSWORD:  Password to test
2. AUTHORIZATION_NAME:  User profile to test. I am limited by security in this partition so I cannot test any profile. I can only test for the following special values:
   • *CURRENT:  Current user profile
   • *NONE:  No profile is used. If a profile or special value is not given this value is the default

The following columns are returned by CHECK_PASSWORD:

• PASSWORD_VALID:  Yes or No
• MESSAGE_ID:  If the password is invalid it will contain the message id. If the password is valid it will contain null
• MESSAGE_TEXT:  If the password is invalid it will contain the message id first level message text. If the password is valid it will contain null
• MESSAGE_SECOND_LEVEL_TEXT:  If the password is invalid it will contain the message id first level message second level text. If the password is valid it will contain null. This column makes the results to wide to fit on this page, therefore, I will be omitting it

It would appear that the if you are using a password that is a maximum of ten characters the passwords can be entered in either case.

First example:

SELECT * FROM TABLE(QSYS2.CHECK_PASSWORD('Something'))

This password is invalid for the following reason:

PASSWORD MESSAGE _VALID _ID MESSAGE_TEXT -------- ------- -------------------------------- NO CPF22C9 Password must contain a number.

Let me try another password:

SELECT * FROM TABLE(QSYS2.CHECK_PASSWORD('123'))

This one fails for the following reason:

PASSWORD MESSAGE _VALID _ID MESSAGE_TEXT -------- ------- -------------------------------- NO CPF22C2 Password less than 6 characters.

Let me try a longer password, which is greater than ten characters:

SELECT * FROM TABLE(QSYS2.CHECK_PASSWORD('SomethingVeryVeryLong1'))

It comes as no surprise that this is invalid:

PASSWORD MESSAGE _VALID _ID MESSAGE_TEXT -------- ------- ----------------------------------- NO CPF22C3 Password longer than 10 characters.

Let me try this one:

SELECT * FROM TABLE(QSYS2.CHECK_PASSWORD('PASS123'))

This one was successful:

PASSWORD MESSAGE _VALID _ID MESSAGE_TEXT -------- ------- -------------------------------- YES <NULL> <NULL>

What happens if I use lower case of the above password:

SELECT * FROM TABLE(QSYS2.CHECK_PASSWORD('pass123'))

That is valid too:

PASSWORD MESSAGE _VALID _ID MESSAGE_TEXT -------- ------- -------------------------------- YES <NULL> <NULL>

What happens if I use the AUTHORIZATION_NAME parameter?

These two statements are valid as the authorization name is upper case:

SELECT * FROM TABLE(QSYS2.CHECK_PASSWORD('pass123','*NONE')) ; SELECT * FROM TABLE(QSYS2.CHECK_PASSWORD('pass123','*CURRENT')) ;

Is the authorization name case sensitive?

SELECT * FROM TABLE(QSYS2.CHECK_PASSWORD('pass123','*none')) ; SELECT * FROM TABLE(QSYS2.CHECK_PASSWORD('pass123','*current')) ;

It is, and both of the above statements produce the same error message when the statement is executed:

Error: CPF222E: *SECADM Special authority is required.

It must regard the lower-case values as user profiles rather than special values.

I love CHECK_PASSWORD as it going to allow me to test the password without having to use the Change Password command, CHGPWD.

 

You can learn more about the CHECK_PASSWORD SQL Table Function from the IBM website here.

 

This article was written for IBM i 7.5 and 7.4 TR6.