-- ============================================================
--  category:    IBM i Services - Security
--  description: Step 1 - Create output schema and inventory tables
--  minvrm:      v7r3m0
-- ============================================================

-- Create the schema if it does not already exist
CREATE SCHEMA IF NOT EXISTS SECAUDIT;

-- --------------------------------------------------------
-- Output table for LIBRARY objects
-- --------------------------------------------------------
CREATE OR REPLACE TABLE SECAUDIT.LIB_AUTH_INVENTORY (
    INVENTORY_TS        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    TARGET_USER         VARCHAR(10)     NOT NULL,
    OBJECT_LIBRARY      VARCHAR(10)     NOT NULL,
    OBJECT_NAME         VARCHAR(10)     NOT NULL,
    OBJECT_TYPE         VARCHAR(10)     NOT NULL,
    OBJECT_OWNER        VARCHAR(10),
    AUTL_NAME           VARCHAR(10),    -- Authorization list securing the object
    AUTH_SOURCE         VARCHAR(50)     NOT NULL, -- Where authority came from
    AUTH_SOURCE_DETAIL  VARCHAR(100),   -- e.g. group name that provided access
    EFFECTIVE_AUTHORITY VARCHAR(50)     NOT NULL, -- e.g. *ALL, *CHANGE, *USE, *EXCLUDE
    OBJ_OPERATIONAL     CHAR(3),
    OBJ_MANAGEMENT      CHAR(3),
    OBJ_EXISTENCE       CHAR(3),
    OBJ_ALTER           CHAR(3),
    OBJ_REFERENCE       CHAR(3),
    DATA_READ           CHAR(3),
    DATA_ADD            CHAR(3),
    DATA_UPDATE         CHAR(3),
    DATA_DELETE         CHAR(3),
    DATA_EXECUTE        CHAR(3),
    CONSTRAINT LIB_AUTH_INV_PK
        PRIMARY KEY (TARGET_USER, OBJECT_LIBRARY, OBJECT_NAME, OBJECT_TYPE)
);

LABEL ON TABLE SECAUDIT.LIB_AUTH_INVENTORY
    IS 'Library Object Authority Inventory';

LABEL ON COLUMN SECAUDIT.LIB_AUTH_INVENTORY (
    INVENTORY_TS        IS 'Inventory Run Timestamp',
    TARGET_USER         IS 'User Profile Checked',
    OBJECT_LIBRARY      IS 'Object Library',
    OBJECT_NAME         IS 'Object Name',
    OBJECT_TYPE         IS 'Object Type',
    OBJECT_OWNER        IS 'Object Owner',
    AUTL_NAME           IS 'Authorization List',
    AUTH_SOURCE         IS 'Authority Source (Priority 1-7)',
    AUTH_SOURCE_DETAIL  IS 'Source Detail (e.g., Group Name)',
    EFFECTIVE_AUTHORITY IS 'Effective Authority Level'
);

-- --------------------------------------------------------
-- Output table for IFS objects
-- --------------------------------------------------------
CREATE OR REPLACE TABLE SECAUDIT.IFS_AUTH_INVENTORY (
    INVENTORY_TS        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    TARGET_USER         VARCHAR(10)     NOT NULL,
    IFS_PATH_NAME       VARCHAR(1024)   NOT NULL,
    OBJECT_TYPE         VARCHAR(20)     NOT NULL,
    OBJECT_OWNER        VARCHAR(10),
    AUTL_NAME           VARCHAR(10),    -- Authorization list securing the object
    AUTH_SOURCE         VARCHAR(50)     NOT NULL,
    AUTH_SOURCE_DETAIL  VARCHAR(100),
    EFFECTIVE_AUTHORITY VARCHAR(50)     NOT NULL,
    DATA_READ           CHAR(3),
    DATA_ADD            CHAR(3),
    DATA_UPDATE         CHAR(3),
    DATA_DELETE         CHAR(3),
    DATA_EXECUTE        CHAR(3),
    CONSTRAINT IFS_AUTH_INV_PK
        PRIMARY KEY (TARGET_USER, IFS_PATH_NAME)
);

LABEL ON TABLE SECAUDIT.IFS_AUTH_INVENTORY
    IS 'IFS Object Authority Inventory';

LABEL ON COLUMN SECAUDIT.IFS_AUTH_INVENTORY (
    INVENTORY_TS        IS 'Inventory Run Timestamp',
    TARGET_USER         IS 'User Profile Checked',
    IFS_PATH_NAME       IS 'Full IFS Path',
    OBJECT_TYPE         IS 'IFS Object Type',
    OBJECT_OWNER        IS 'Object Owner',
    AUTL_NAME           IS 'Authorization List',
    AUTH_SOURCE         IS 'Authority Source (Priority 1-7)',
    AUTH_SOURCE_DETAIL  IS 'Source Detail (e.g., Group Name)',
    EFFECTIVE_AUTHORITY IS 'Effective Authority Level'
);

-- ============================================================
--  category:    IBM i Services - Security
--  description: Step 2 - Library object authority inventory
--               Resolves authority in IBM i priority order (1-7).
--               Replace 'MYLIB' and 'HUGO' before running.
--  minvrm:      v7r3m0
-- ============================================================

-- Remove any previous inventory rows for this user + library
DELETE FROM SECAUDIT.LIB_AUTH_INVENTORY
WHERE TARGET_USER    = 'HUGO'
  AND OBJECT_LIBRARY = 'MYLIB';

INSERT INTO SECAUDIT.LIB_AUTH_INVENTORY (
    INVENTORY_TS, TARGET_USER, OBJECT_LIBRARY, OBJECT_NAME, OBJECT_TYPE,
    OBJECT_OWNER, AUTL_NAME, AUTH_SOURCE, AUTH_SOURCE_DETAIL,
    EFFECTIVE_AUTHORITY, OBJ_OPERATIONAL, OBJ_MANAGEMENT, OBJ_EXISTENCE,
    OBJ_ALTER, OBJ_REFERENCE, DATA_READ, DATA_ADD, DATA_UPDATE, DATA_DELETE, DATA_EXECUTE
)
WITH

-- User profile attributes
user_attrs AS (
    SELECT AUTHORIZATION_NAME, SPECIAL_AUTHORITIES,
           GROUP_PROFILE_NAME, SUPPLEMENTAL_GROUP_LIST
    FROM QSYS2.USER_INFO
    WHERE AUTHORIZATION_NAME = 'HUGO'
),

-- Expand all group profiles into individual rows
user_groups AS (
    SELECT GROUP_PROFILE_NAME AS GROUP_NAME
    FROM user_attrs
    WHERE GROUP_PROFILE_NAME IS NOT NULL
      AND TRIM(GROUP_PROFILE_NAME) NOT IN ('', '*NONE')

    UNION ALL

    -- Supplemental groups - tokenize the space-delimited list with XMLTABLE
    SELECT TRIM(x.GROUP_TOKEN) AS GROUP_NAME
    FROM user_attrs,
         XMLTABLE(
             'for $t in tokenize($s, " ") return <g>{string($t)}</g>'
             PASSING SUPPLEMENTAL_GROUP_LIST AS "s"
             COLUMNS GROUP_TOKEN VARCHAR(10) PATH '.'
         ) AS x
    WHERE SUPPLEMENTAL_GROUP_LIST IS NOT NULL
      AND TRIM(SUPPLEMENTAL_GROUP_LIST) NOT IN ('', '*NONE')
      AND TRIM(x.GROUP_TOKEN) NOT IN ('', '*NONE')
),

-- All objects in the target library + detect *AUTL linkage
obj_list AS (
    SELECT os.OBJLIB, os.OBJNAME, os.OBJTYPE, os.OBJOWNER,
           NULLIF(TRIM(ap.AUTHORIZATION_LIST), '') AS AUTL_NAME
    FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB', '*ALL')) AS os
    LEFT JOIN QSYS2.OBJECT_PRIVILEGES AS ap
        ON  ap.SYSTEM_OBJECT_SCHEMA = os.OBJLIB
        AND ap.OBJECT_NAME          = os.OBJNAME
        AND ap.OBJECT_TYPE          = os.OBJTYPE
        AND ap.USER_NAME            = '*PUBLIC'
        AND ap.OBJECT_AUTHORITY     = '*AUTL'
),

-- Priority 2: User private authority
user_priv AS (
    SELECT op.SYSTEM_OBJECT_SCHEMA AS OBJLIB, op.OBJECT_NAME AS OBJNAME,
           op.OBJECT_TYPE AS OBJTYPE, op.OBJECT_AUTHORITY AS EFFECTIVE_AUTHORITY,
           op.OBJECT_OPERATIONAL, op.OBJECT_MANAGEMENT, op.OBJECT_EXISTENCE,
           op.OBJECT_ALTER, op.OBJECT_REFERENCE,
           op.DATA_READ, op.DATA_ADD, op.DATA_UPDATE, op.DATA_DELETE, op.DATA_EXECUTE
    FROM QSYS2.OBJECT_PRIVILEGES AS op
    WHERE op.SYSTEM_OBJECT_SCHEMA = 'MYLIB'
      AND op.USER_NAME            = 'HUGO'
      AND op.OBJECT_TYPE         <> '*AUTL'
      AND op.USER_NAME           <> '*PUBLIC'
),

-- Priority 3: User private entry in the authorization list
user_autl_priv AS (
    SELECT ol.OBJLIB, ol.OBJNAME, ol.OBJTYPE,
           al.OBJECT_AUTHORITY AS EFFECTIVE_AUTHORITY,
           al.OBJECT_OPERATIONAL, al.OBJECT_MANAGEMENT, al.OBJECT_EXISTENCE,
           al.OBJECT_ALTER, al.OBJECT_REFERENCE,
           al.DATA_READ, al.DATA_ADD, al.DATA_UPDATE, al.DATA_DELETE, al.DATA_EXECUTE
    FROM obj_list AS ol
    JOIN QSYS2.OBJECT_PRIVILEGES AS al
        ON  al.SYSTEM_OBJECT_SCHEMA = 'QSYS'
        AND al.OBJECT_NAME          = ol.AUTL_NAME
        AND al.OBJECT_TYPE          = '*AUTL'
        AND al.USER_NAME            = 'HUGO'
    WHERE ol.AUTL_NAME IS NOT NULL
),

-- Priority 4: Any group profile that has *ALLOBJ
grp_allobj AS (
    SELECT ug.GROUP_NAME
    FROM user_groups AS ug
    JOIN QSYS2.USER_INFO AS gui
        ON gui.AUTHORIZATION_NAME = ug.GROUP_NAME
    WHERE gui.SPECIAL_AUTHORITIES LIKE '%*ALLOBJ%'
    FETCH FIRST 1 ROW ONLY
),

-- Priority 5: Best group private authority on each object
grp_priv AS (
    SELECT op.SYSTEM_OBJECT_SCHEMA AS OBJLIB, op.OBJECT_NAME AS OBJNAME,
           op.OBJECT_TYPE AS OBJTYPE, op.USER_NAME AS GROUP_NAME,
           op.OBJECT_AUTHORITY AS EFFECTIVE_AUTHORITY,
           op.OBJECT_OPERATIONAL, op.OBJECT_MANAGEMENT, op.OBJECT_EXISTENCE,
           op.OBJECT_ALTER, op.OBJECT_REFERENCE,
           op.DATA_READ, op.DATA_ADD, op.DATA_UPDATE, op.DATA_DELETE, op.DATA_EXECUTE,
           ROW_NUMBER() OVER (
               PARTITION BY op.SYSTEM_OBJECT_SCHEMA, op.OBJECT_NAME, op.OBJECT_TYPE
               ORDER BY CASE op.OBJECT_AUTHORITY
                   WHEN '*ALL' THEN 1 WHEN '*CHANGE' THEN 2
                   WHEN '*USE' THEN 3 WHEN '*EXCLUDE' THEN 4 ELSE 5
               END
           ) AS RNK
    FROM QSYS2.OBJECT_PRIVILEGES AS op
    JOIN user_groups AS ug ON op.USER_NAME = ug.GROUP_NAME
    WHERE op.SYSTEM_OBJECT_SCHEMA = 'MYLIB'
      AND op.USER_NAME           <> '*PUBLIC'
),

-- Priority 6: Best group entry in the authorization list
grp_autl_priv AS (
    SELECT ol.OBJLIB, ol.OBJNAME, ol.OBJTYPE, al.USER_NAME AS GROUP_NAME,
           al.OBJECT_AUTHORITY AS EFFECTIVE_AUTHORITY,
           al.OBJECT_OPERATIONAL, al.OBJECT_MANAGEMENT, al.OBJECT_EXISTENCE,
           al.OBJECT_ALTER, al.OBJECT_REFERENCE,
           al.DATA_READ, al.DATA_ADD, al.DATA_UPDATE, al.DATA_DELETE, al.DATA_EXECUTE,
           ROW_NUMBER() OVER (
               PARTITION BY ol.OBJLIB, ol.OBJNAME, ol.OBJTYPE
               ORDER BY CASE al.OBJECT_AUTHORITY
                   WHEN '*ALL' THEN 1 WHEN '*CHANGE' THEN 2
                   WHEN '*USE' THEN 3 WHEN '*EXCLUDE' THEN 4 ELSE 5
               END
           ) AS RNK
    FROM obj_list AS ol
    JOIN QSYS2.OBJECT_PRIVILEGES AS al
        ON  al.SYSTEM_OBJECT_SCHEMA = 'QSYS'
        AND al.OBJECT_NAME          = ol.AUTL_NAME
        AND al.OBJECT_TYPE          = '*AUTL'
        AND al.USER_NAME           <> '*PUBLIC'
    JOIN user_groups AS ug ON al.USER_NAME = ug.GROUP_NAME
    WHERE ol.AUTL_NAME IS NOT NULL
),

-- Priority 7: *PUBLIC authority
pub_auth AS (
    -- Branch A: direct *PUBLIC (not secured by *AUTL)
    SELECT op.SYSTEM_OBJECT_SCHEMA AS OBJLIB, op.OBJECT_NAME AS OBJNAME,
           op.OBJECT_TYPE AS OBJTYPE, op.OBJECT_AUTHORITY AS EFFECTIVE_AUTHORITY,
           op.OBJECT_OPERATIONAL, op.OBJECT_MANAGEMENT, op.OBJECT_EXISTENCE,
           op.OBJECT_ALTER, op.OBJECT_REFERENCE,
           op.DATA_READ, op.DATA_ADD, op.DATA_UPDATE, op.DATA_DELETE, op.DATA_EXECUTE
    FROM QSYS2.OBJECT_PRIVILEGES AS op
    WHERE op.SYSTEM_OBJECT_SCHEMA = 'MYLIB'
      AND op.USER_NAME            = '*PUBLIC'
      AND op.OBJECT_AUTHORITY    <> '*AUTL'

    UNION ALL

    -- Branch B: object secured by *AUTL - get *PUBLIC from inside the auth list
    SELECT ol.OBJLIB, ol.OBJNAME, ol.OBJTYPE,
           al.OBJECT_AUTHORITY, al.OBJECT_OPERATIONAL, al.OBJECT_MANAGEMENT,
           al.OBJECT_EXISTENCE, al.OBJECT_ALTER, al.OBJECT_REFERENCE,
           al.DATA_READ, al.DATA_ADD, al.DATA_UPDATE, al.DATA_DELETE, al.DATA_EXECUTE
    FROM obj_list AS ol
    JOIN QSYS2.OBJECT_PRIVILEGES AS al
        ON  al.SYSTEM_OBJECT_SCHEMA = 'QSYS'
        AND al.OBJECT_NAME          = ol.AUTL_NAME
        AND al.OBJECT_TYPE          = '*AUTL'
        AND al.USER_NAME            = '*PUBLIC'
    WHERE ol.AUTL_NAME IS NOT NULL
),

-- Authority resolution - 7-priority CASE chain
resolved AS (
    SELECT 'HUGO' AS TARGET_USER,
        ol.OBJLIB, ol.OBJNAME, ol.OBJTYPE, ol.OBJOWNER, ol.AUTL_NAME,
        CASE
            WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%'
                THEN 'Priority 1 - User *ALLOBJ special authority'
            WHEN up.OBJNAME  IS NOT NULL THEN 'Priority 2 - User private authority'
            WHEN uap.OBJNAME IS NOT NULL THEN 'Priority 3 - User authority via authorization list'
            WHEN (SELECT COUNT(*) FROM grp_allobj) > 0
                THEN 'Priority 4 - Group *ALLOBJ special authority'
            WHEN gp.OBJNAME  IS NOT NULL THEN 'Priority 5 - Group private authority'
            WHEN gap.OBJNAME IS NOT NULL THEN 'Priority 6 - Group authority via authorization list'
            ELSE 'Priority 7 - *PUBLIC authority'
        END AS AUTH_SOURCE,
        CASE
            WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%'
                THEN 'User profile: HUGO'
            WHEN up.OBJNAME  IS NOT NULL THEN 'User profile: HUGO'
            WHEN uap.OBJNAME IS NOT NULL THEN 'Authorization list: ' || COALESCE(ol.AUTL_NAME, '')
            WHEN (SELECT COUNT(*) FROM grp_allobj) > 0
                THEN 'Group profile: ' || (SELECT GROUP_NAME FROM grp_allobj)
            WHEN gp.OBJNAME  IS NOT NULL THEN 'Group profile: ' || gp.GROUP_NAME
            WHEN gap.OBJNAME IS NOT NULL THEN 'Group profile: ' || gap.GROUP_NAME
                || ' via authorization list: ' || COALESCE(ol.AUTL_NAME, '')
            ELSE '*PUBLIC'
        END AS AUTH_SOURCE_DETAIL,
        CASE
            WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%'
                THEN '*ALL (via *ALLOBJ)'
            WHEN up.OBJNAME  IS NOT NULL THEN up.EFFECTIVE_AUTHORITY
            WHEN uap.OBJNAME IS NOT NULL THEN uap.EFFECTIVE_AUTHORITY
            WHEN (SELECT COUNT(*) FROM grp_allobj) > 0 THEN '*ALL (via *ALLOBJ)'
            WHEN gp.OBJNAME  IS NOT NULL THEN gp.EFFECTIVE_AUTHORITY
            WHEN gap.OBJNAME IS NOT NULL THEN gap.EFFECTIVE_AUTHORITY
            ELSE COALESCE(pa.EFFECTIVE_AUTHORITY, '*EXCLUDE (no *PUBLIC entry)')
        END AS EFFECTIVE_AUTHORITY,
        CASE WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%' THEN 'YES'
             WHEN up.OBJNAME  IS NOT NULL THEN up.OBJ_OPERATIONAL
             WHEN uap.OBJNAME IS NOT NULL THEN uap.OBJ_OPERATIONAL
             WHEN (SELECT COUNT(*) FROM grp_allobj) > 0 THEN 'YES'
             WHEN gp.OBJNAME  IS NOT NULL THEN gp.OBJ_OPERATIONAL
             WHEN gap.OBJNAME IS NOT NULL THEN gap.OBJ_OPERATIONAL
             ELSE pa.OBJ_OPERATIONAL END AS OBJ_OPERATIONAL,
        CASE WHEN up.OBJNAME  IS NOT NULL THEN up.OBJ_MANAGEMENT
             WHEN uap.OBJNAME IS NOT NULL THEN uap.OBJ_MANAGEMENT
             WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%' THEN 'YES'
             WHEN gp.OBJNAME  IS NOT NULL THEN gp.OBJ_MANAGEMENT
             WHEN gap.OBJNAME IS NOT NULL THEN gap.OBJ_MANAGEMENT
             ELSE pa.OBJ_MANAGEMENT END AS OBJ_MANAGEMENT,
        CASE WHEN up.OBJNAME  IS NOT NULL THEN up.OBJ_EXISTENCE
             WHEN uap.OBJNAME IS NOT NULL THEN uap.OBJ_EXISTENCE
             WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%' THEN 'YES'
             WHEN gp.OBJNAME  IS NOT NULL THEN gp.OBJ_EXISTENCE
             WHEN gap.OBJNAME IS NOT NULL THEN gap.OBJ_EXISTENCE
             ELSE pa.OBJ_EXISTENCE END AS OBJ_EXISTENCE,
        CASE WHEN up.OBJNAME  IS NOT NULL THEN up.OBJ_ALTER
             WHEN uap.OBJNAME IS NOT NULL THEN uap.OBJ_ALTER
             WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%' THEN 'YES'
             WHEN gp.OBJNAME  IS NOT NULL THEN gp.OBJ_ALTER
             WHEN gap.OBJNAME IS NOT NULL THEN gap.OBJ_ALTER
             ELSE pa.OBJ_ALTER END AS OBJ_ALTER,
        CASE WHEN up.OBJNAME  IS NOT NULL THEN up.OBJ_REFERENCE
             WHEN uap.OBJNAME IS NOT NULL THEN uap.OBJ_REFERENCE
             WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%' THEN 'YES'
             WHEN gp.OBJNAME  IS NOT NULL THEN gp.OBJ_REFERENCE
             WHEN gap.OBJNAME IS NOT NULL THEN gap.OBJ_REFERENCE
             ELSE pa.OBJ_REFERENCE END AS OBJ_REFERENCE,
        CASE WHEN up.OBJNAME  IS NOT NULL THEN up.DATA_READ
             WHEN uap.OBJNAME IS NOT NULL THEN uap.DATA_READ
             WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%' THEN 'YES'
             WHEN gp.OBJNAME  IS NOT NULL THEN gp.DATA_READ
             WHEN gap.OBJNAME IS NOT NULL THEN gap.DATA_READ
             ELSE pa.DATA_READ END AS DATA_READ,
        CASE WHEN up.OBJNAME  IS NOT NULL THEN up.DATA_ADD
             WHEN uap.OBJNAME IS NOT NULL THEN uap.DATA_ADD
             WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%' THEN 'YES'
             WHEN gp.OBJNAME  IS NOT NULL THEN gp.DATA_ADD
             WHEN gap.OBJNAME IS NOT NULL THEN gap.DATA_ADD
             ELSE pa.DATA_ADD END AS DATA_ADD,
        CASE WHEN up.OBJNAME  IS NOT NULL THEN up.DATA_UPDATE
             WHEN uap.OBJNAME IS NOT NULL THEN uap.DATA_UPDATE
             WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%' THEN 'YES'
             WHEN gp.OBJNAME  IS NOT NULL THEN gp.DATA_UPDATE
             WHEN gap.OBJNAME IS NOT NULL THEN gap.DATA_UPDATE
             ELSE pa.DATA_UPDATE END AS DATA_UPDATE,
        CASE WHEN up.OBJNAME  IS NOT NULL THEN up.DATA_DELETE
             WHEN uap.OBJNAME IS NOT NULL THEN uap.DATA_DELETE
             WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%' THEN 'YES'
             WHEN gp.OBJNAME  IS NOT NULL THEN gp.DATA_DELETE
             WHEN gap.OBJNAME IS NOT NULL THEN gap.DATA_DELETE
             ELSE pa.DATA_DELETE END AS DATA_DELETE,
        CASE WHEN up.OBJNAME  IS NOT NULL THEN up.DATA_EXECUTE
             WHEN uap.OBJNAME IS NOT NULL THEN uap.DATA_EXECUTE
             WHEN (SELECT SPECIAL_AUTHORITIES FROM user_attrs) LIKE '%*ALLOBJ%' THEN 'YES'
             WHEN gp.OBJNAME  IS NOT NULL THEN gp.DATA_EXECUTE
             WHEN gap.OBJNAME IS NOT NULL THEN gap.DATA_EXECUTE
             ELSE pa.DATA_EXECUTE END AS DATA_EXECUTE
    FROM obj_list AS ol
    LEFT JOIN user_priv      AS up  ON up.OBJLIB  = ol.OBJLIB AND up.OBJNAME  = ol.OBJNAME AND up.OBJTYPE  = ol.OBJTYPE
    LEFT JOIN user_autl_priv AS uap ON uap.OBJLIB = ol.OBJLIB AND uap.OBJNAME = ol.OBJNAME AND uap.OBJTYPE = ol.OBJTYPE
    LEFT JOIN (SELECT * FROM grp_priv      WHERE RNK = 1) AS gp  ON gp.OBJLIB  = ol.OBJLIB AND gp.OBJNAME  = ol.OBJNAME AND gp.OBJTYPE  = ol.OBJTYPE
    LEFT JOIN (SELECT * FROM grp_autl_priv WHERE RNK = 1) AS gap ON gap.OBJLIB = ol.OBJLIB AND gap.OBJNAME = ol.OBJNAME AND gap.OBJTYPE = ol.OBJTYPE
    LEFT JOIN pub_auth        AS pa  ON pa.OBJLIB  = ol.OBJLIB AND pa.OBJNAME  = ol.OBJNAME AND pa.OBJTYPE  = ol.OBJTYPE
)
SELECT CURRENT_TIMESTAMP, TARGET_USER, OBJLIB, OBJNAME, OBJTYPE, OBJOWNER, AUTL_NAME,
       AUTH_SOURCE, AUTH_SOURCE_DETAIL, EFFECTIVE_AUTHORITY,
       OBJ_OPERATIONAL, OBJ_MANAGEMENT, OBJ_EXISTENCE, OBJ_ALTER, OBJ_REFERENCE,
       DATA_READ, DATA_ADD, DATA_UPDATE, DATA_DELETE, DATA_EXECUTE
FROM resolved
ORDER BY OBJLIB, OBJNAME, OBJTYPE;

-- ============================================================
--  category:    IBM i Services - Security
--  description: Step 4a - Library authority summary by source
--  minvrm:      v7r3m0
-- ============================================================

SELECT
    TARGET_USER,
    OBJECT_LIBRARY,
    AUTH_SOURCE,
    COUNT(*)              AS OBJECT_COUNT,
    LISTAGG(DISTINCT EFFECTIVE_AUTHORITY, ', ')
        WITHIN GROUP (ORDER BY EFFECTIVE_AUTHORITY) AS AUTHORITY_LEVELS
FROM SECAUDIT.LIB_AUTH_INVENTORY
GROUP BY
    TARGET_USER,
    OBJECT_LIBRARY,
    AUTH_SOURCE
ORDER BY
    TARGET_USER,
    OBJECT_LIBRARY,
    AUTH_SOURCE;

-- ============================================================
--  category:    IBM i Services - Security
--  description: Step 4b - Full library authority detail
--  minvrm:      v7r3m0
-- ============================================================

SELECT
    TARGET_USER,
    OBJECT_LIBRARY,
    OBJECT_NAME,
    OBJECT_TYPE,
    OBJECT_OWNER,
    AUTL_NAME,
    AUTH_SOURCE,
    AUTH_SOURCE_DETAIL,
    EFFECTIVE_AUTHORITY,
    DATA_READ,
    DATA_ADD,
    DATA_UPDATE,
    DATA_DELETE,
    DATA_EXECUTE,
    OBJ_OPERATIONAL,
    OBJ_MANAGEMENT,
    OBJ_EXISTENCE,
    OBJ_ALTER,
    OBJ_REFERENCE
FROM SECAUDIT.LIB_AUTH_INVENTORY
ORDER BY
    TARGET_USER,
    OBJECT_LIBRARY,
    OBJECT_TYPE,
    OBJECT_NAME;

-- ============================================================
--  category:    IBM i Services - Security
--  description: Step 4c - IFS authority summary by source
--  minvrm:      v7r3m0
-- ============================================================

SELECT
    TARGET_USER,
    AUTH_SOURCE,
    COUNT(*)              AS OBJECT_COUNT,
    LISTAGG(DISTINCT EFFECTIVE_AUTHORITY, ', ')
        WITHIN GROUP (ORDER BY EFFECTIVE_AUTHORITY) AS AUTHORITY_LEVELS
FROM SECAUDIT.IFS_AUTH_INVENTORY
GROUP BY
    TARGET_USER,
    AUTH_SOURCE
ORDER BY
    TARGET_USER,
    AUTH_SOURCE;

-- ============================================================
--  category:    IBM i Services - Security
--  description: Step 4d - Full IFS authority detail
--  minvrm:      v7r3m0
-- ============================================================

SELECT
    TARGET_USER,
    IFS_PATH_NAME,
    OBJECT_TYPE,
    OBJECT_OWNER,
    AUTL_NAME,
    AUTH_SOURCE,
    AUTH_SOURCE_DETAIL,
    EFFECTIVE_AUTHORITY,
    DATA_READ,
    DATA_ADD,
    DATA_UPDATE,
    DATA_DELETE,
    DATA_EXECUTE
FROM SECAUDIT.IFS_AUTH_INVENTORY
ORDER BY
    TARGET_USER,
    IFS_PATH_NAME;

-- ============================================================
--  category:    IBM i Services - Security
--  description: Step 4e - Denied/excluded objects (both spaces)
--  minvrm:      v7r3m0
-- ============================================================

SELECT 'LIBRARY'    AS OBJECT_SPACE,
       TARGET_USER,
       OBJECT_LIBRARY AS LOCATION,
       OBJECT_NAME,
       OBJECT_TYPE,
       EFFECTIVE_AUTHORITY,
       AUTH_SOURCE,
       AUTH_SOURCE_DETAIL
FROM SECAUDIT.LIB_AUTH_INVENTORY
WHERE EFFECTIVE_AUTHORITY LIKE '%*EXCLUDE%'
   OR EFFECTIVE_AUTHORITY LIKE '%no *PUBLIC%'

UNION ALL

SELECT 'IFS',
       TARGET_USER,
       LEFT(IFS_PATH_NAME, 80),
       IFS_PATH_NAME,
       OBJECT_TYPE,
       EFFECTIVE_AUTHORITY,
       AUTH_SOURCE,
       AUTH_SOURCE_DETAIL
FROM SECAUDIT.IFS_AUTH_INVENTORY
WHERE EFFECTIVE_AUTHORITY LIKE '%*EXCLUDE%'
   OR EFFECTIVE_AUTHORITY LIKE '%no *PUBLIC%'

ORDER BY OBJECT_SPACE, TARGET_USER, LOCATION;

-- ============================================================
--  category:    IBM i Services - Security
--  description: Step 5 - Remove prior inventory rows before re-run
--  minvrm:      v7r3m0
-- ============================================================

DELETE FROM SECAUDIT.LIB_AUTH_INVENTORY
WHERE TARGET_USER = 'HUGO';

DELETE FROM SECAUDIT.IFS_AUTH_INVENTORY
WHERE TARGET_USER = 'HUGO';