Thursday, September 15, 2022

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.

4 comments:

  1. It looks like CHECK_PASSWORD may only be available in 7.5. I'm on 7.4 TRt and it is showing not found. I also do not see it in the 7.4 doc or the 7.4 TR6 list. Can you confirm this?

    ReplyDelete
    Replies
    1. Argh, I am wrong you are right. Thank you for letting me know.

      Delete
  2. One observation, if the system uses QPWDRULES rather than setting it to *PWDSYSVAL then it's worth expanding the result field otherwise you'll only get the first value.
    Example if you've set *MAXLEN10 and *MINLEN6 then result of 10 will only give you the max length.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.