Custom password audit for "must contain at least one letter and one number" does not work

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

The AM documentation for adding custom password audits provides 4 different sql/examples of how to add custom password audits. The link to the documentation is:

https://techdocs.broadcom.com/us/en/ca-enterprise-software/intelligent-automation/applications-manager/9-4-4/Administration_Guide/Applications_Manager_Security/Defining_Users/Adding_User_Login_Password_Audits.html

However, one of the sql does not work:

-- must contain at least one letter and one number
       IF so_db_passwd = translate(so_db_passwd,'0123456789','')
       or length(rtrim(translate(so_db_passwd,'0123456789',''))) = 0
       THEN raise_application_error(-20081,so_user_name||
            ' Password must contain at least one letter and one number');
       END IF;

After applying the above password audit, a user can still be created with all characters or all numbers without error

Environment

Release : 9.5.0

Resolution

Applications Manager version 9.5.1 will include an update sql.

The below changes can be made to the current sql script as a workaround.

Change:

 -- must contain at least one letter and one number
       IF so_db_passwd = translate(so_db_passwd,'0123456789','')
       or length(rtrim(translate(so_db_passwd,'0123456789',''))) = 0
       THEN raise_application_error(-20081,so_user_name||
            ' Password must contain at least one letter and one number');
       END IF;

To:

 -- must contain at least one letter and one number
       FOR i IN 1..LENGTH(so_db_passwd) LOOP
        IF REGEXP_LIKE(SUBSTR(so_db_passwd, i, 1), '[0-9]') THEN
            has_digits := TRUE;
        ELSIF REGEXP_LIKE(SUBSTR(so_db_passwd, i, 1), '[A-Za-z]') THEN
            has_letters := TRUE;
        END IF;
        
        -- Exit loop early if both conditions are met
        IF has_digits AND has_letters THEN
            EXIT;
        END IF;
       END LOOP;
       -- raise error if the password does not contain both letter and number 
       IF NOT (has_digits AND has_letters) THEN
            raise_application_error(-20081,so_user_name||
            ' Password must contain at least one letter and one number');
       END IF;