Retrieving Role, Duty Role, and Privileges Navigation Path in Oracle Fusion BI Report with SQL Query

The following Oracle Fusion SQL query enables administrators and auditors to retrieve the complete hierarchy of Job Roles, Duty Roles, and associated Privileges, along with the navigation path that defines their interrelationships.

This query is particularly useful in Oracle Fusion BI Reports to analyze access governance, track inherited security relationships, and ensure compliance with internal control frameworks.

SQL Query

SELECT DISTINCT
    "C"."Job Role id",
    "C"."Job Role Name",
    "C"."Duty Role id",
    (SELECT "ASE_ROLE_VL"."ROLE_NAME"
     FROM "FUSION"."ASE_ROLE_VL" "ASE_ROLE_VL"
     WHERE "ASE_ROLE_VL"."ROLE_ID" = "C"."Duty Role id") AS "Duty Role Name",
    "ASE_PRIV_ROLE_MBR"."PRIVILEGE_ID" AS "PRIVILEGE_ID",
    (SELECT "ASE_PRIVILEGE_VL"."NAME"
     FROM "FUSION"."ASE_PRIVILEGE_VL" "ASE_PRIVILEGE_VL"
     WHERE "ASE_PRIVILEGE_VL"."PRIVILEGE_ID" = "ASE_PRIV_ROLE_MBR"."PRIVILEGE_ID") AS "Privilege_name",

    “C”.”Navigation path”

FROM (

    SELECT
        "B"."Job_Role_id1" AS "Job Role id",
        (SELECT "ASE_ROLE_VL"."ROLE_NAME"
         FROM "FUSION"."ASE_ROLE_VL" "ASE_ROLE_VL"
         WHERE "ASE_ROLE_VL"."ROLE_ID" = "B"."Job_Role_id1") AS "Job Role Name",
        "B"."Duty_Role_id1" AS "Duty Role id",

        “B”.”Navigation_path1″ AS “Navigation path”

    FROM (

        SELECT
            "A"."Job_Role_id" AS "Job_Role_id1",
            "A"."Duty_Role_id" AS "Duty_Role_id1",
            (SELECT "ASE_ROLE_TYPE_VL"."ROLE_TYPE_NAME"
             FROM "FUSION"."ASE_ROLE_TYPE_VL" "ASE_ROLE_TYPE_VL"
             WHERE "ASE_ROLE_TYPE_VL"."ROLE_TYPE_CODE" =
                 (SELECT "ASE_ROLE_VL"."ROLE_TYPE_CODE"
                  FROM "FUSION"."ASE_ROLE_VL" "ASE_ROLE_VL"
                  WHERE "ASE_ROLE_VL"."ROLE_ID" = "A"."Job_Role_id")) AS "JOB_ROLE_TYPE_NAME",
            (SELECT "ASE_ROLE_TYPE_VL"."ROLE_TYPE_NAME"
             FROM "FUSION"."ASE_ROLE_TYPE_VL" "ASE_ROLE_TYPE_VL"
             WHERE "ASE_ROLE_TYPE_VL"."ROLE_TYPE_CODE" =
                 (SELECT "ASE_ROLE_VL"."ROLE_TYPE_CODE"
                  FROM "FUSION"."ASE_ROLE_VL" "ASE_ROLE_VL"
                  WHERE "ASE_ROLE_VL"."ROLE_ID" = "A"."Duty_Role_id")) AS "DUTY_ROLE_TYPE_NAME",

            “A”.”Navigation_path” AS “Navigation_path1”

        FROM (

            (SELECT DISTINCT 

                CONNECT_BY_ROOT “ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID” AS “Job_Role_id”,

                “ASE_ROLE_ROLE_MBR”.”PARENT_ROLE_ID” AS “Duty_Role_id”,

                SYS_CONNECT_BY_PATH(

                    (SELECT “ASE_ROLE_VL”.”ROLE_NAME”

                     FROM “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL”

                     WHERE “ASE_ROLE_VL”.”ROLE_ID” = “ASE_ROLE_ROLE_MBR”.”PARENT_ROLE_ID”), 

                ‘ -> ‘) AS “Navigation_path”

            FROM “FUSION”.”ASE_ROLE_ROLE_MBR” “ASE_ROLE_ROLE_MBR”

            WHERE (“ASE_ROLE_ROLE_MBR”.”EFFECTIVE_END_DATE” >= SYSDATE 

                   OR “ASE_ROLE_ROLE_MBR”.”EFFECTIVE_END_DATE” IS NULL)

            CONNECT BY PRIOR “ASE_ROLE_ROLE_MBR”.”PARENT_ROLE_ID” = “ASE_ROLE_ROLE_MBR”.”CHILD_ROLE_ID”)

            UNION

            (SELECT 

                “ASE_ROLE_VL”.”ROLE_ID” AS “Job_Role_id”,

                “ASE_ROLE_VL”.”ROLE_ID” AS “Duty_Role_id”,

                ‘ -> ‘ || “ASE_ROLE_VL”.”ROLE_NAME” AS “Navigation_path”

             FROM “FUSION”.”ASE_ROLE_VL” “ASE_ROLE_VL”

             WHERE (“ASE_ROLE_VL”.”EFFECTIVE_END_DATE” >= SYSDATE 

                    OR “ASE_ROLE_VL”.”EFFECTIVE_END_DATE” IS NULL))

        ) “A”

    ) “B”

    WHERE (“B”.”JOB_ROLE_TYPE_NAME” LIKE ‘%Duty Roles%’ 

        OR “B”.”DUTY_ROLE_TYPE_NAME” LIKE ‘%Duty Roles%’)

) “C”,

“FUSION”.”ASE_PRIV_ROLE_MBR” “ASE_PRIV_ROLE_MBR”

WHERE “ASE_PRIV_ROLE_MBR”.”ROLE_ID” = “C”.”Duty Role id”

AND UPPER(“C”.”Job Role Name”) LIKE UPPER(:P_NAME);

Query Overview

This SQL query performs a recursive traversal of Oracle Fusion’s role hierarchy to extract:

• Job Role → Duty Role → Privilege relationships
• Navigation path illustrating how roles inherit privileges through multiple levels
• Effective role hierarchy based on EFFECTIVE_END_DATE conditions
  

It joins core Oracle Fusion security tables such as:

• ASE_ROLE_ROLE_MBR: Stores parent-child role relationships.
• ASE_ROLE_VL: Contains role details and type codes.
• ASE_PRIV_ROLE_MBR: Links roles to their corresponding privileges.
• ASE_PRIVILEGE_VL: Provides privilege names and metadata.

Business Use Case

This query is invaluable for:

• Access Governance: Understanding how privileges flow from job roles down to individual duty roles.
• SoD Analysis (Segregation of Duties): Identifying where privilege overlaps may cause conflicts.
• Compliance and Audit: Providing detailed lineage of access entitlements.
• Security Model Reviews: Visualizing navigation paths between job roles, duty roles, and privileges for validation or redesign.
  

Organizations using this query can enhance their visibility into user role composition, simplify security audits, and strengthen access governance across Oracle Fusion Cloud applications.