IBM i Security Attack Vectors: SQL Analysis with ACS Sample Queries

Steps

Database Attack Vectors

1. Delete Attack Vector

Description: Identifies database files where ANY user can delete rows

--  category:  IBM i Services
--  description:  Security - Db2 for i - Delete attack vector
--  Use Db2 for i to Inject more Security into your IBM i
--  minvrm:  v7r3m0

--
-- Files where ANY user can delete rows
--
WITH libs (lib_name) AS (
    SELECT object_name
      FROM qsys2.object_privileges
      WHERE system_object_schema = 'QSYS' AND object_type = '*LIB' AND user_name = '*PUBLIC' AND
            data_execute = 'YES'
  )
  SELECT *
    FROM libs, qsys2.object_privileges
    WHERE system_object_schema = lib_name AND object_type = '*FILE' AND user_name = '*PUBLIC' AND
          data_delete = 'YES' AND ('PF' = (SELECT objattribute
                FROM TABLE (
                    qsys2.object_statistics(lib_name, '*FILE', object_name)
                  )));

Risk Level: HIGH
Impact: Unauthorized data deletion, data loss, integrity compromise

2. Insert Attack Vector

Description: Identifies database files where ANY user can insert rows

--  category:  IBM i Services
--  description:  Security - Db2 for i - Insert attack vector
--  Use Db2 for i to Inject more Security into your IBM i
--  minvrm:  v7r3m0

--
-- Files where ANY user can insert rows
--
WITH libs (lib_name) AS (
    SELECT object_name
      FROM qsys2.object_privileges
      WHERE system_object_schema = 'QSYS' AND object_type = '*LIB' AND user_name = '*PUBLIC' AND
            data_execute = 'YES'
  )
  SELECT *
    FROM libs, qsys2.object_privileges
    WHERE system_object_schema = lib_name AND object_type = '*FILE' AND user_name = '*PUBLIC' AND
          data_add = 'YES' AND ('PF' = (SELECT objattribute
                FROM TABLE (
                    qsys2.object_statistics(lib_name, '*FILE', object_name)
                  )));

Risk Level: HIGH
Impact: Unauthorized data insertion, data corruption, malicious data injection

3. Query Attack Vector

Description: Identifies database physical files that ANY user can read

--  category:  IBM i Services
--  description:  Security - Db2 for i - Query attack vector
--  Use Db2 for i to Inject more Security into your IBM i
--  minvrm:  v7r3m0

--
-- Database physical files that ANY user can read
--
WITH libs (lib_name) AS (
    SELECT object_name
      FROM qsys2.object_privileges
      WHERE system_object_schema = 'QSYS' AND object_type = '*LIB' AND user_name = '*PUBLIC' AND
            data_execute = 'YES'
  )
  SELECT *
    FROM libs, qsys2.object_privileges
    WHERE system_object_schema = lib_name AND object_type = '*FILE' AND user_name = '*PUBLIC' AND
          data_read = 'YES' AND object_operational = 'YES' AND ('PF' = (SELECT objattribute
                FROM TABLE (
                    qsys2.object_statistics(lib_name, '*FILE', object_name)
                  )));

Risk Level: MEDIUM to HIGH
Impact: Unauthorized data access, information disclosure, privacy violations

4. Update Attack Vector

Description: Identifies files where ANY user can update rows

--  category:  IBM i Services
--  description:  Security - Db2 for i - Update attack vector
--  Use Db2 for i to Inject more Security into your IBM i
--  minvrm:  v7r3m0

--
-- Files where ANY user can update rows
--
WITH libs (lib_name) AS (
    SELECT object_name
      FROM qsys2.object_privileges
      WHERE system_object_schema = 'QSYS' AND object_type = '*LIB' AND user_name = '*PUBLIC' AND
            data_execute = 'YES'
  )
  SELECT *
    FROM libs, qsys2.object_privileges
    WHERE system_object_schema = lib_name AND object_type = '*FILE' AND user_name = '*PUBLIC' AND
          data_update = 'YES' AND ('PF' = (SELECT objattribute
                FROM TABLE (
                    qsys2.object_statistics(lib_name, '*FILE', object_name)
                  )));

Risk Level: HIGH
Impact: Unauthorized data modification, data corruption, integrity compromise

5. RENAME Attack Vector

Description: Identifies database files exposed to a RENAME attack

--  category:  IBM i Services
--  description:  Security - Db2 for i - RENAME attack
--  Use Db2 for i to Inject more Security into your IBM i
--  minvrm:  v7r3m0

--
-- Database files exposed to a RENAME attack
--
WITH libs (lib_name) AS (
    SELECT object_name
      FROM qsys2.object_privileges
      WHERE system_object_schema = 'QSYS' AND object_type = '*LIB' AND authorization_name =
            '*PUBLIC' AND data_execute = 'YES' AND data_update = 'YES'
  )
  SELECT priv.*
    FROM libs,
         LATERAL (
           SELECT *
             FROM qsys2.object_privileges
             WHERE system_object_schema = lib_name AND object_type = '*FILE' AND authorization_name
                   = '*PUBLIC' AND object_management = 'YES' AND object_operational = 'YES' AND (
                     'PF' = (SELECT objattribute
                         FROM TABLE (
                             qsys2.object_statistics(lib_name, '*FILE', object_name)
                           )))
         ) priv;

Risk Level: MEDIUM
Impact: Object manipulation, confusion attacks, service disruption

6. Trigger Attack Vector

Description: Identifies files exposed to a trigger attack

--  category:  IBM i Services
--  description:  Security - Db2 for i - Trigger attack vector
--  Use Db2 for i to Inject more Security into your IBM i
--  minvrm:  v7r3m0

--
-- Files exposed to a trigger attack
--
WITH libs (lib_name) AS (
    SELECT object_name
      FROM qsys2.object_privileges
      WHERE system_object_schema = 'QSYS' AND object_type = '*LIB' AND user_name = '*PUBLIC' AND
            data_execute = 'YES'
  )
  SELECT *
    FROM libs, qsys2.object_privileges
    WHERE system_object_schema = lib_name AND object_type = '*FILE' AND user_name = '*PUBLIC' AND
          data_read = 'YES' AND object_operational = 'YES' AND (object_alter = 'YES' OR
            object_management = 'YES') AND ('PF' = (SELECT objattribute
                FROM TABLE (
                    qsys2.object_statistics(lib_name, '*FILE', object_name)
                  )));

Risk Level: HIGH
Impact: Malicious trigger creation, unauthorized code execution, data manipulation

Library Security Analysis

7. Libraries Open for Attack (Count)

Description: Counts how many libraries are OPEN for attack

--  category:  IBM i Services
--  description:  Security - How many libraries are OPEN for attack?
--  Use Db2 for i to Inject more Security into your IBM i
--  minvrm:  v7r3m0

--
-- How many libraries are OPEN for attack?
--
SELECT COUNT(*) AS library_count
  FROM qsys2.object_privileges
  WHERE system_object_schema = 'QSYS' AND object_type = '*LIB' AND object_name NOT LIKE 'QSYS%' AND
        user_name = '*PUBLIC' AND data_execute = 'YES';

Risk Level: INFORMATIONAL
Purpose: Provides a quick count of potentially vulnerable libraries

8. Libraries Open for Attack (Ownership Perspective)

Description: Shows library vulnerability grouped by owner

--  category:  IBM i Services
--  description:  Security - How many libraries are OPEN for attack? (ownership perspective)
--  Use Db2 for i to Inject more Security into your IBM i
--  minvrm:  v7r3m0

--
-- How many libraries are OPEN for attack? (ownership perspective)
--
SELECT owner, COUNT(*) AS library_count
  FROM qsys2.object_privileges
  WHERE system_object_schema = 'QSYS' AND object_type = '*LIB' AND object_name NOT LIKE 'QSYS%' AND
        user_name = '*PUBLIC' AND data_execute = 'YES'
  GROUP BY owner
  ORDER BY library_count DESC;

Risk Level: INFORMATIONAL
Purpose: Identifies which owners have the most vulnerable libraries

9. Library List Security Review

Description: Reviews the library list for improper *PUBLIC authority

--  category:  IBM i Services
--  description:  Security - Library List security review
--  Use Db2 for i to Inject more Security into your IBM i
--  minvrm:  v7r3m0

--
-- Review the library list and return any library where *PUBLIC is configured to something other than *USE
--
SELECT libl.system_schema_name AS lib_name, priv.authorization_user AS user_name,
       priv.object_authority
  FROM qsys2.library_list_info libl, LATERAL (
         SELECT *
           FROM TABLE (
               qsys2.object_privileges(
                 system_object_schema => 'QSYS', system_object_name => system_schema_name,
                 object_type => '*LIB')
             )
       ) priv
  WHERE priv.authorization_user = '*PUBLIC' AND 
        priv.object_authority   <> '*USE'   AND
        libl.system_schema_name not in ('QTEMP');

Risk Level: MEDIUM
Impact: Identifies libraries in the library list with excessive *PUBLIC authority

Command Security

10. Which Commands Can Anyone Use?

Description: Identifies commands that *PUBLIC can execute

--  category:  IBM i Services
--  description:  Security - Which commands can anyone use?
--  Use Db2 for i to Inject more Security into your IBM i
--  minvrm:  v7r3m0

-- TODO: Query to be added - identifies commands with *PUBLIC *USE authority
-- This would typically query qsys2.object_privileges for *CMD objects

Risk Level: MEDIUM to HIGH
Impact: Identifies potentially dangerous commands accessible to all users

11. Review *ALLOBJ Users

Description: Identifies users with *ALLOBJ authority (direct or via group/supplemental profiles)

--  category:  IBM i Services
--  description:  Security - Review *ALLOBJ users

--
-- Which users have *ALLOBJ authority either directly
-- or via a Group or Supplemental profile?
--
SELECT AUTHORIZATION_NAME, STATUS, NO_PASSWORD_INDICATOR, PREVIOUS_SIGNON,
TEXT_DESCRIPTION
FROM QSYS2.USER_INFO
WHERE SPECIAL_AUTHORITIES LIKE '%*ALLOBJ%'
OR AUTHORIZATION_NAME IN (
SELECT USER_PROFILE_NAME
FROM QSYS2.GROUP_PROFILE_ENTRIES
WHERE GROUP_PROFILE_NAME IN (
SELECT AUTHORIZATION_NAME
FROM QSYS2.USER_INFO
WHERE SPECIAL_AUTHORITIES like '%*ALLOBJ%'
)
)
ORDER BY AUTHORIZATION_NAME;

Risk Level: CRITICAL
Impact: *ALLOBJ users have unrestricted access to all objects on the system

Remediation Guidelines

General Recommendations

1. Principle of Least Privilege: Grant only the minimum necessary authorities
2. Regular Audits: Run these queries regularly to identify new vulnerabilities
3. Documentation: Document all exceptions and business justifications
4. Change Control: Implement approval processes for authority changes
5. Monitoring: Set up alerts for changes to critical object authorities

Priority Actions

CRITICAL (Immediate Action Required)

• Review and restrict *ALLOBJ users
• Remediate DELETE, INSERT, and UPDATE attack vectors
• Address TRIGGER attack vectors

HIGH (Action Required Within 30 Days)

• Review and restrict data area change authorities
• Address IFS directory ownership mismatches
• Review RENAME attack vectors

MEDIUM (Action Required Within 90 Days)

• Review command authorities
• Address data area read authorities
• Review library list security

INFORMATIONAL (Ongoing Monitoring)

• Track library vulnerability counts
• Monitor trigger inventory
• Review IFS ownership summaries