Auditing User Profiles

AuditingUserProfile.sql

/******************************************************************************/
/* Find security-related changes made by potentially vulnerable user profiles */
/******************************************************************************/

-- Navigator's Users Tab
SELECT
       CASE GROUP_ID_NUMBER
           WHEN 0 THEN 'USER'
           ELSE 'GROUP'
       END AS PROFILE_TYPE,
       A.*
    FROM QSYS2.USER_INFO A
    LIMIT 100 OFFSET 0;


-- Find users who haven't changed their password in a while...
SELECT AUTHORIZATION_NAME,
       PREVIOUS_SIGNON,
       PASSWORD_CHANGE_DATE,
       TEXT_DESCRIPTION
    FROM QSYS2.USER_INFO
    WHERE GROUP_ID_NUMBER = 0
          AND PREVIOUS_SIGNON IS NOT NULL
          AND PASSWORD_CHANGE_DATE < CURRENT_TIMESTAMP - 3 YEAR
          AND STATUS = '*ENABLED'
    ORDER BY PASSWORD_CHANGE_DATE ASC;

-- Find delete operations performed by potentially-vulnerable user profiles
WITH PASSWORD_NEGLECTORS AS (
        SELECT AUTHORIZATION_NAME,
               PREVIOUS_SIGNON,
               PASSWORD_CHANGE_DATE,
               TEXT_DESCRIPTION
            FROM QSYS2.USER_INFO A
            WHERE GROUP_ID_NUMBER = 0
                  AND PREVIOUS_SIGNON IS NOT NULL
                  AND PASSWORD_CHANGE_DATE < CURRENT_TIMESTAMP - 3 YEAR
                  AND STATUS = '*ENABLED'
    )
    SELECT *
        FROM PASSWORD_NEGLECTORS PG
             JOIN TABLE (
                     SYSTOOLS.AUDIT_JOURNAL_DO(STARTING_TIMESTAMP => CURRENT TIMESTAMP - 1 MONTHS, USER_NAME => PG.AUTHORIZATION_NAME) -- DO finds Deleted Object
                 ) AUDIT_INFO
                 ON PG.AUTHORIZATION_NAME = AUDIT_INFO.USER_NAME
             WHERE AUTHORIZATION_NAME NOT LIKE 'Q%'; -- Don't include system profiles
             
-- Take the results of the previous query, but now disable the profiles.
WITH PASSWORD_NEGLECTORS AS (
        SELECT AUTHORIZATION_NAME,
               PREVIOUS_SIGNON,
               PASSWORD_CHANGE_DATE,
               TEXT_DESCRIPTION
            FROM QSYS2.USER_INFO A
            WHERE GROUP_ID_NUMBER = 0
                  AND PREVIOUS_SIGNON IS NOT NULL
                  AND PASSWORD_CHANGE_DATE < CURRENT_TIMESTAMP - 3 YEAR
                  AND STATUS = '*ENABLED'
    )
    SELECT *
        FROM PASSWORD_NEGLECTORS PG,
             TABLE (
                 SYSTOOLS.AUDIT_JOURNAL_DO(STARTING_TIMESTAMP => CURRENT TIMESTAMP - 1 MONTHS, 
                                           USER_NAME => PG.AUTHORIZATION_NAME) -- DO finds Deleted Object
             ) AUDIT_INFO,
             TABLE (
                 SYSTOOLS.CHANGE_USER_PROFILE(P_USER_NAME => PG.AUTHORIZATION_NAME, 
                                              P_STATUS => '*DISABLED', 
                                              PREVIEW => 'YES')
             )
        WHERE PG.AUTHORIZATION_NAME = AUDIT_INFO.USER_NAME
              AND AUTHORIZATION_NAME NOT LIKE 'Q%'; -- Don't include system profiles