APEX: Page Access Protection and Session State Protection

APEX’s Page Access Protection (PAP - For Pages) and Session State Protection (SSP - For Items) are excellent security tools to help prevent users from altering session values. What some people may not be aware of is that if you enable PAP for page it does not prevent users from altering the session state of items on that page. All it does is require that any items passed through that page via the URL require a checksum. Malicious users can still alter the item’s session state using AJAX or from other pages. Long story short, if you want to lock your application down you need to enable SSP for all required items.

APEX has a great tool to do this quickly for you rather than having to go into each page item. Shared Components / Session State Protection / Page / (click page number). You can now set the PAP and the SSP for all the page items.

If you do use PAP and SSP the following queries will help you do some quick validations to ensure all your security checks are in place

Pages without Page Access Protection

SELECT aap.application_id,
       aap.application_name,
       aap.page_id,
       aap.page_name
  FROM apex_application_pages aap
 WHERE LOWER (aap.page_access_protection) = 'unrestricted'
   AND aap.application_id = :app_id

Page items without Session State Protection

SELECT aapi.application_id,
       aapi.application_name,
       aapi.page_id,
       aapi.page_name,
       aapi.item_name
  FROM apex_application_page_items aapi
 WHERE aapi.application_id = :app_id
   AND LOWER (aapi.item_protection_level) = 'unrestricted'

Pages which have Page Access Protection, but have page items with no Session State Protection

This query helps identify pages which you think are locked down, but end users could set the session state of item values

SELECT aapi.application_id,
       aapi.application_name,
       aapi.page_id,
       aapi.page_name,
       aapi.item_name
  FROM apex_application_pages aap,
       apex_application_page_items aapi
 WHERE LOWER (aap.page_access_protection) != 'unrestricted'
   AND aap.application_id = :app_id
   AND aapi.application_id = aap.application_id
   AND aap.page_id = aapi.page_id
   AND LOWER (aapi.item_protection_level) = 'unrestricted'

Enhancing APEX Security - Explanation

I recently posted an entry called Enhancing APEX Security. I may have jumped the gun and not given a full explanation as to what problem this was trying to solving and the logic behind this solution. Here’s the full explanation to the problem and a high level overview of the proposed solution.

In APEX, Session State Protection (SSP) can help secure your applications by preventing users to set session state value of items. Developers should be aware that it has 2 issues regarding it. First, it prevents items from being set using AJAX. If I try to set using AJAX I get the following error (using Firebug):

Second, when a user is on a page, they can alter the value before it is submitted to try to access data they aren’t allowed to. For example, let’s say users are only allowed to see information about people within their own department. So my LOV is something like:

SELECT e.ename d,
       e.empno r
  FROM emp e
WHERE job = 'CLERK'

Using Firebug I can see that this is what my option list looks like

Now what if I were to modify the select list and change the value for “SMITH” from 7369 to 7839 (This is “KING” / President) then submit the page? It still works and sets the value in session state:

Notice how the value in session is set to 7839, which we really shouldn’t have access to? If you’re not careful in your application this can lead to serious security holes.

Now on to the proposed solution. When you have a Select List, use a hashed value for the IDs so that users can’t alter the select list values (well they can but they’ll require the hash value as well). Instead of P1_EMPNO being a select list, set it as a Hidden and Protected item and enable SSP for it. This allows your application to still reference P1_EMPNO as it normally would. Create a new item, P1_EMPNO_SEC, which will be a select list with the altered LOV code (i.e. the return value has a hash associated to it). So if the value is 7369, it will actually be [email protected] (i.e. VALUE[DELIMITER][HASH]). The code (in my example) handles the rest so that when a user submits a page, it will take the submitted value from the secure item and then set the “regular” item with the unhashed value. If someone were to try to alter the select list, they’d be unsuccessful since the unhashing method returns NULL when an invalid hash value is present.

The reason why I create 2 items is I don’t want to have to alter any existing code. So all code referencing P1_EMPNO will remain the same. If users need to pass P1_EMPNO in via the URL they can (provided they pass in the checksum since it has SSP enabled).

Enhancing APEX Security

Note: Full explanation available here. Please read before continuing with this post.

The best way to secure data in your APEX application, or any application, is to secure your data in the database. You can do this using Oracle’s Virtual Private Database (VPD), also known as Fine Grained Access Control (FGAC). APEX also has Session State Protection (SSP) which helps prevent front-end modification of data by preventing URL tampering.

I’m a big advocate of using both VPD and SSP. In some situations VPD may not be implemented for various reasons so developers must rely on SSP. SSP is great and allows developers to quickly and easily help prevent malicious users from trying to access data that they aren’t supposed to. It’s important to note that SSP only prevents URL tampering. If users wanted to alter data on a form they could easily do that with Firebug (I won’t go into that in this post). SSP also prevents users from setting values using AJAX. This can be circumvented using the “x01” .. “x10” parameters (please see Carl’s post for more info) but still leaves the door open for users to alter items in session state that they shouldn’t.

How can you use SSP, make AJAX calls, and ensure that users aren’t altering data on a form? This question has been bugging me for a while and I think I’ve come up with a solution that should work with minimal changes to existing code. For this example I’m going to use a select list containing a list of IDs (this is where the issue has come up the most for me). I’ll use the emp table, with empno as the id, for this example.

Some notation before we begin: “Secure” items (i.e. ones that have a hashed value appended to them) will be called PX_ITEM_NAME_SEC while it’s corresponding unsecured item is called PX_ITEM_NAME. The code only looks at “secure” items that have a matching “unsecured” item. “Unsecure” items are really items that are Hidden and Protected so they have a checksum associated to them.

The demo can be found here

Step 1- Compile Package

Since I don’t have access to DMBS_CRYPTO on apex.oracle.com I’ve used a dummy encryption method. You’ll need to grant execute on DBMS_CRYPTO from SYS:

GRANT EXECUTE ON DBMS_CRYPTO TO giffy; -- Where "giffy" is your schema name

Compile the following code in your schema

pkg_apex_sec.pks

CREATE OR REPLACE PACKAGE pkg_apex_sec
AS
  /**
   * Returns secure value
   * @param p_value
   * @return
   */
  FUNCTION f_get_sec_val (
    p_value IN VARCHAR2
  )
    RETURN VARCHAR2;

  /**
   * Checks if secured value is valid
   * @param p_hashed_val (case sensitive
   * @return 'Y' or 'N'
   */
  FUNCTION is_valid_hashed_val (
    p_hash IN VARCHAR2
  )
    RETURN VARCHAR2;

  /**
   * unsecure value given the hash
   * @param p_hash
   * @return unsecure number
   */
  FUNCTION f_get_val (
    p_hash IN VARCHAR2
  )
    RETURN VARCHAR2;

   /**
  * Sets unsec values in the page given the secure values
  * @param p_page_id Page ID to set. Default current page
  */
  PROCEDURE sp_set_page_unsec_values (
    p_page_id IN apex_application_pages.page_id%TYPE DEFAULT v ('APP_PAGE_ID')
  );

  /**
   * Set all the secure values given the unsecure values
   * @param p_page_id Page ID. Default current page
   */
  PROCEDURE sp_set_page_sec_values (
    p_page_id IN apex_application_pages.page_id%TYPE DEFAULT v ('APP_PAGE_ID')
  );
END pkg_apex_sec;

pkg_apex_sec.pkb

CREATE OR REPLACE PACKAGE BODY pkg_apex_sec
AS
  -- Constants
  gc_delim CONSTANT VARCHAR2 (1) := '@';

/**
 * Returns hashed value
 * May require sys to grant access to dbms_crypto
 *  - GRANT EXECUTE ON DBMS_CRYPTO TO ;
 * @param p_source
 * @param p_key
 * @return hashed value
 */
  FUNCTION f_get_md5 (
    p_source IN VARCHAR2,
    p_key IN VARCHAR2
  )
    RETURN VARCHAR2
  IS
    v_key VARCHAR2 (4000) := p_key;
  BEGIN
    -- Normally your key should be different from the source. (this is up to you to maintain)
    -- For simplicity this function will see if they are the same. If so we'll append the app_id
    IF p_source = p_key THEN
      v_key := p_key || v ('APP_ID');
    END IF;

    -- Can't use DBMS_CRYPTO in apex.oracle.com. Using generic coding
    -- RETURN DBMS_CRYPTO.mac (src => UTL_RAW.cast_to_raw (p_source), typ => 2, KEY => UTL_RAW.cast_to_raw (v_key));
    RETURN p_source || p_key || 123;                                                                     -- DELETE THIS.
  END f_get_md5;

/**
 * Returns secure value
 * @param p_value
 * @return
 */
  FUNCTION f_get_sec_val (
    p_value IN VARCHAR2
  )
    RETURN VARCHAR2
  AS
  BEGIN
    -- For the Key value I'm arbitrarily appending the app id. You should change this to something that is secure to your code.
    -- You can add :app_session as well, provided you're not using session 0 or external links (ie. from emails etc)
    RETURN p_value || gc_delim || f_get_md5 (p_source => p_value, p_key => p_value || v ('APP_ID'));
  END f_get_sec_val;

  /**
   * Checks if secured value is valid
   * @param p_hashed_val (case sensitive
   * @return 'Y' or 'N'
   */
  FUNCTION is_valid_hashed_val (
    p_hash IN VARCHAR2
  )
    RETURN VARCHAR2
  AS
    v_value VARCHAR2 (4000);
  BEGIN
    v_value := REPLACE (REGEXP_SUBSTR (p_hash, '^[[:print:]]+' || gc_delim), gc_delim);

    IF p_hash = f_get_sec_val (p_value => v_value) THEN
      RETURN 'Y';
    ELSE
      RETURN 'N';
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN 'N';
  END is_valid_hashed_val;

  /**
   * unsecure value given the hash
   * @param p_hash
   * @return unsecure number
   */
  FUNCTION f_get_val (
    p_hash IN VARCHAR2
  )
    RETURN VARCHAR2
  AS
  BEGIN
    IF is_valid_hashed_val (p_hash => p_hash) = 'N' THEN
      RETURN NULL;
    END IF;

    RETURN (REPLACE (REGEXP_SUBSTR (p_hash, '^[[:print:]]+' || gc_delim), gc_delim));
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END f_get_val;

  /**
   * Sets unsec values in the page given the secure values
   * @param p_page_id Page ID to set. Default current page
   */
  PROCEDURE sp_set_page_unsec_values (
    p_page_id IN apex_application_pages.page_id%TYPE DEFAULT v ('APP_PAGE_ID')
  )
  AS
    v_app_id apex_applications.application_id%TYPE := v ('APP_ID');
  BEGIN
    BEGIN
      -- Set all the unsecure values from the secure values
      FOR x IN (SELECT a1.item_name item_name_sec,
                       a2.item_name
                  FROM apex_application_page_items a1,
                       apex_application_page_items a2
                 WHERE a1.application_id = v_app_id
                   AND a1.page_id = p_page_id
                   AND a1.item_name LIKE '%_SEC'
                   -- Find corresponding item name
                   AND a2.application_id = a1.application_id
                   AND a2.page_id = a1.page_id
                   AND RTRIM (a1.item_name, '_SEC') = a2.item_name) LOOP
        apex_util.set_session_state (x.item_name, pkg_apex_sec.f_get_val (v (x.item_name_sec)));
      END LOOP;
    END;
  END sp_set_page_unsec_values;

  /**
   * Set all the secure values given the unsecure values
   * @param p_page_id Page ID. Default current page
   */
  PROCEDURE sp_set_page_sec_values (
    p_page_id IN apex_application_pages.page_id%TYPE DEFAULT v ('APP_PAGE_ID')
  )
  AS
    v_app_id apex_applications.application_id%TYPE := v ('APP_ID');
  BEGIN
    -- Set all the secure values from the secure values
    FOR x IN (SELECT a1.item_name item_name_sec,
                     a2.item_name
                FROM apex_application_page_items a1,
                     apex_application_page_items a2
               WHERE a1.application_id = v_app_id
                 AND a1.page_id = p_page_id
                 AND a1.item_name LIKE '%_SEC'
                 -- Find corresponding item name
                 AND a2.application_id = a1.application_id
                 AND a2.page_id = a1.page_id
                 AND RTRIM (a1.item_name, '_SEC') = a2.item_name) LOOP
      IF v (x.item_name) IS NOT NULL THEN
        apex_util.set_session_state (x.item_name_sec, pkg_apex_sec.f_get_sec_val (v (x.item_name)));
      END IF;
    END LOOP;
  END sp_set_page_sec_values;
END pkg_apex_sec;

Step 2- Create Application Processes

Create Application Processes (On Load Before Header) called: AP_SET_SEC_PAGE_ITEMS. This will allow you to pass IDs in the URL as you normally would.

BEGIN
  pkg_apex_sec.sp_set_page_sec_values;
END;

Create Application Processes (On Submit and Before Computation ) called: AP_SET_UNSEC_PAGE_ITEMS

BEGIN
  pkg_apex_sec.sp_set_page_unsec_values;
END;

Create Application Process (On Demand) called AP_NULL to set values in session (using AJAX).

BEGIN
  pkg_apex_sec.sp_set_page_unsec_values; -- Update the unsec values from secure values for the current page
  NULL;
END;

Step 3 - Create page with Interactive Report (IR)

SELECT *
  FROM emp
 WHERE empno = NVL (:p1500_empno, empno)

Step 4 - Create “Secure” Id LOV and a “Hidden and Protected” unsecured fields

Create P1500_EMPNO as Hidden and Protected. It’s extremely important that you make the field Hidden and Protected so users can’t alter the value in the form.
Create P1500_EMPNO_SEC as a Select List. LOV:

Instead of:

SELECT e.ename d,
       e.empno r
  FROM emp e

Use:

SELECT e.ename d,
       pkg_apex_sec.f_get_sec_val (e.empno) r
  FROM emp e

Besides adding the application processes etc, this is the only significant change that you’ll need to make to your application

At this point your application is good to go. Steps 5 and 6 are included only for demonstration purposes.

Step 5 - JavaScript
This JavaScript will be used to simulate the onChange event for a select list

function onLovChange(pThisId){
  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=AP_NULL',$v('pFlowStepId'));  
  get.add(pThisId, $v(pThisId));
  vReturn = get.get();  
}

Step 6 - Button
Create a Submit button that will submit and branch to the same page. This will demonstrate a “normal” submit process
Create a button called “AJAX”. URL = javascript:onLovChange(‘P1500_EMPNO_SEC’);

I haven’t used this in production yet so their may be some changes that I add to the code. If you have any feedback please leave a comment.

APEX Interactive Reports - Customize Wait Display Part 2

APEX Interactive Reports - Custom Wait Display Part 2

Note: If you’re using APEX 4.0 please see this post: http://www.talkapex.com/2010/08/apex-40-interactive-reports-customize.html

A few weeks ago I wrote about customizing the default APEX IR wait logo: http://apex-smb.blogspot.com/2009/04/apex-interactive-reports-customize-wait.html. Here is the second part of that article which focuses on overwriting APEX’s dispIRBusyGraphics function.

Overriding the default “Busy Graphic” function can be used to customize messages/images as well as preventing users from resubmitting IR request which may take a long time.

An example of this can be found here

- 1: Create IR Report

-- Trying to make this a slow query to demonstrate the IR loader
SELECT     e.*, sum(e.sal) over() test
      FROM emp e
CONNECT BY LEVEL <= 5

- 2: Download jQuery and Simple Modal and upload the JavaScript files in the Application’s Static Files:

jQuery: http://jquery.com/
Simple Modal: http://www.ericmmartin.com/projects/simplemodal/

- 3: Create an HTML region and add the following:





Custom Download for APEX Interactive Reports

A few months ago I needed to modify the downloaded Interactive Report (IR) to CSV function so that we could include group headings, report description, and report parameters (please see Column Groups in APEX Interactive Reports on how to display column groups in IR). Denes Kubicek has a package to download regular reports in Excel format, however it does not return the query that the user is currently viewing. This is really important since users may add columns, change the ordering of columns, etc. When users download a report it should reflect what is currently being displayed on the screen.

After digging through the APEX packages I was able to write some code to generate my own custom download function for IRs. Since the code is fairly long you’ll need to get a copy of the code here (please save the file as pkg_apex_report.zip). Note: This code is NOT ready for a production environment!. It’s also important to note that some of the grant privileges could have security implications and is not recommend to be run for public applications. If you need to use these grants in a production instance I suggest creating a special “APEX” schema and write some custom wrapper functions. Since grants are required from the APEX schema I can’t post a working example of this application on apex.oracle.com.

Now that I’m done with my disclaimers here’s how to customize IR downloads (don’t forget to download the code). Please note that the schema I developed in this was called “giffy”.

1- Grant Privileges

  -- Change "apex_030200" to the version of APEX that you are using
  -- Change "giffy" to your schema
  GRANT EXECUTE ON apex_030200.wwv_flow_worksheet_standard TO giffy;
  CREATE OR REPLACE SYNONYM giffy.wwv_flow_worksheet_standard FOR apex_030200.wwv_flow_worksheet_standard

  GRANT EXECUTE ON apex_030200.wwv_flow_conditions TO giffy;
  CREATE OR REPLACE SYNONYM giffy.wwv_flow_conditions FOR apex_030200.wwv_flow_conditions;

  GRANT EXECUTE ON apex_030200.wwv_flow_worksheet TO giffy;
  CREATE OR REPLACE SYNONYM giffy.wwv_flow_worksheet FOR apex_030200.wwv_flow_worksheet;

  GRANT EXECUTE ON apex_030200.wwv_flow_render_query TO giffy;
  CREATE OR REPLACE SYNONYM giffy.wwv_flow_render_query FOR apex_030200.wwv_flow_render_query;

2- Create Package apex_report

Download code here (save the file as pkg_apex_report.zip)
In SQL*Plus run:

  @pkg_apex_report.pks
  @pkg_apex_report.pkb

3- Create Interactive Report region

  SELECT *
    FROM emp

4- Create “Download Page”

  • Create a new HTML Page.

  • Create a page item P3_BASE_REPORT_ID

  • Create a page item P3_PAGE_ID

  • Create a PL/SQL Process

    • Point: On Load Before Header
    • PL/SQL:
  DECLARE
    v_region_id apex_application_page_regions.region_id%TYPE;
    v_base_report_id apex_application_page_ir_rpt.base_report_id%TYPE;
    v_page_id apex_application_page_ir_rpt.page_id%TYPE;
  BEGIN
    v_base_report_id := :p3_base_report_id;
    v_page_id := :p3_page_id;

    SELECT ir.region_id
      INTO v_region_id
      FROM apex_application_page_ir_rpt irr,
           apex_application_page_ir ir
     WHERE irr.application_id = :app_id
       AND irr.page_id = v_page_id
       AND irr.session_id = :app_session
       AND irr.base_report_id = v_base_report_id
       AND irr.interactive_report_id = ir.interactive_report_id;

    pkg_apex_report.sp_download_report (p_page_id            => v_page_id,
                                        p_region_id          => v_region_id,
                                        p_base_report_id     => v_base_report_id,
                                        p_format_typ         => 'CSV'
                                       );
  END;

5- Alter Interactive Report Download Link
Please see APEX Interactive Report - Download with One Click for more information about this code. You’ll need jQuery for this.

//Note: "3" is my download page
$(document).ready(function() {
  $('.dhtmlSubMenuN[title="Download"]').attr('href','f?p=' + $v('pFlowId') + ':3:' + $v('pInstance') + '::NO:3:P3_PAGE_ID,P3_BASE_REPORT_ID:' + $v('pFlowStepId') + ',' + $v('apexir_REPORT_ID'));  
});

At this point you should be able to run your interactive report and download the custom CSV file. You’ll notice that there’s a block of HTML that appears at the top of the CSV file. This is caused by the call (wwv_flow_worksheet.get_worksheet_report_query) to get the SQL the user is currently looking at. I don’t have a fix or a work around (if you know of one please post in comment section)

Please post any bugs, suggestions, or updates to the code as I’d like to create a production version for this code.

APEX Interactive Reports - Customize Wait Display Part 1

We recently needed to change, and move, the default Interactive Report (IR) AJAX “Loading” image (spinning wheel):

After some investigation they’re several things you can do depending on your requirements. A demo application is available here.

Part 2 is here: http://apex-smb.blogspot.com/2009/04/apex-interactive-reports-customize-wait_28.html

Before we go over the solution it’s important to review where the image is located and how it gets toggled on and off. The image is stored in a span region which is hidden by default. The code looks like this:

  
    ![](/i/ws/ajax-loader.gif)
  

If you want to manually make it visible you can run the following code:

  $('#apexir_LOADER').show();

The JS code to toggle the image is stored in: apex_ns_3_1.js. You can view an uncompressed version of this file in your installation directory: apex_3.1.2\apex\images\javascript\uncompressed\apex_ns_3_1.js To toggle the image, APEX calls the following JS function: apex.worksheet.ws._BusyGraphic (We won’t be using this function in this post but I’ll do another posting on how we can use it for a completely customizable solution)

First Let’s build a IR page with the emp table:

select *
from emp
connect by level <= 3

The easiest option is if all you need to do is change the image:

  $('#apexir_LOADER img')[0].src="#IMAGE_PREFIX#processing3.gif";

The new image, processing3.gif, is part of APEX as well. You can reference your own images if you’d like.

In Part 2 I’ll go over changing the actual function that shows and hides the loading image. This will give you complete control of the loading message.

Presenting at ODTUG Kaleidoscope 2009 - APEX


Frank Hoogendoorn and I will be presenting at the Application Express Symposium on Sunday at ODTUG Kaleidoscope 2009. Our presentation is called “Getting the Ape out of APEX“.

I attended ODTUG last year in New Orleans and was amazed with all the excellent APEX presentations, I’m sure this year will be even better. If you can only attend once conference this year and you are using, or interested in, APEX this is the conference to go to!

You can go to www.odtugkaleidoscope.com for more information and registration.

See you then.

Log APEX Interactive Report Search Filter

A few days ago someone posted a question on the forums http://forums.oracle.com/forums/message.jspa?messageID=3365707 asking how to log the search filter in an IR. Here’s how to do it. A working example is available here.

Please note you will need jQuery for this example

Step 1: Create Log Table

  CREATE TABLE tir_search_filter_log(
    search_filter VARCHAR2(255) NULL,
    search_date DATE DEFAULT SYSDATE NOT NULL,
    username VARCHAR2(255) NOT NULL);

Step 2: Create Application Process

Create an On Demand Application Process. Call it: AP_LOG_SEARCH_FILTER

  BEGIN
    INSERT INTO tir_search_filter_log
                (search_filter,
                 search_date,
                 username
                )
    VALUES      (apex_application.g_x01,
                 SYSDATE,
                 :app_user
                );
  END;

Step 3: Create Interactive Report

Create a IR Page

  SELECT *
  FROM   emp

Step 3: Javascript

Create a HTML region and add the following javascript code:

  // Function to call our Application Process to log the search
  function fLogSearch(){
    var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=AP_LOG_SEARCH_FILTER',$v('pFlowStepId'));    
    get.addParam('x01',$v('apexir_SEARCH')); // IR Search Filter Value
    gReturn = get.get();  // Call AP Log Search Filter to insert log
    // Call APEX IR Search
    gReport.search('SEARCH');
  }

  // Run the following once the document is ready
  $(document).ready(function(){  
    // -- Handle Go Button --
    // Unbind all events. Important for order of execution
    $('input[type="button"][value="Go"]').attr('onclick',''); //unbind click event
    // Rebind events
    $('input[type="button"][value="Go"]').click(function(){fLogSearch()});

    // -- Handle "Enter" in input field --
    $('#apexir_SEARCH').attr('onkeyup',''); //unbind onkeyup event
    // Rebind Events
    $('#apexir_SEARCH').keyup(function(event){($f_Enter(event))?fLogSearch():null;});
  });

Column Groups in APEX Interactive Reports

Note: If you are using APEX 4.0 I’ve developed a plugin for this: http://www.talkapex.com/2010/12/column-groups-in-apex-40-interactive.html

A while ago Dimitri Gielis helped us to display column groups in Interactive Reports (IR) in APEX. Please see his blog posting to get a background of how to enable column groups in Interactive Reports.

After some testing we’ve made several changes to the code. The updated code fixes a bug that would display multiple group headers. It does not load the group headers via AJAX so it speeds up response time. I’ve included the updated code below. The example of this is here.

Please note you will need jQuery for this example

Step 1: Application Process
Create an Application Process to load the IR colum group header information
Name: AP_GET_IR_COL_GROUPS
Point: On Load: After Header (page template header)

DECLARE
  v_sql                         VARCHAR2 (500);
  v_count                       PLS_INTEGER;
BEGIN
  -- Need to ensure at least 1 col group exists
  SELECT COUNT (column_group_id)
  INTO   v_count
  FROM   apex_application_page_ir ir, apex_application_page_ir_col c
  WHERE  ir.application_id = :app_id
  AND    ir.page_id = :app_page_id   
  AND    ir.interactive_report_id = c.interactive_report_id;

-- Need to do join columns to IR rather than the page_id since columns that are added after IR was created need have a null page_id
  v_sql                      := '
SELECT c.column_alias,
       c.report_label,
       c.column_group
FROM   apex_application_page_ir_col c,
       apex_application_page_ir i
WHERE  i.application_id = :app_id
AND    i.page_id = :app_page_id
AND    i.interactive_report_id = c.interactive_report_id
AND    c.column_group IS NOT NULL';
  HTP.p ('');
END;

Step 2: Javascript Code
You can put this on Page 0 or just directly on the page with the Interactive Report

dispIRColGrpHeader=function(){
  if(typeof(gPageIRColGrps) != "undefined"){
    // retrieve the Interactive report table
    var vTbl = $('.apexir_WORKSHEET_DATA');

    // change the look and feel of the IR table
    $(vTbl).attr("border","1");

    // Prevent Duplicate rows
    $('#irColGrpRow').remove();

    // Add the Column Group row
    $(vTbl[0].rows[0]).before('<tr id="irColGrpRow"></tr>');

    var vPrevColGrp = '';
    var vColGrpExists = false;
    var vColSpan = 1;
    // Loop over the row headers and see if we need to add a column group.
    for (var i = 0; i < $(vTbl[0].rows[1].cells).length; i++){
      // For IR, the column headers have divs with id of apexir
      vColId = '';
      // Only set the col ID if it exists (needed for IR row_id icon)
      if (typeof($('.apexir_WORKSHEET_DATA tr:eq(1) th:eq(' + i + ') div').attr('id')) != "undefined")
        vColId = $('.apexir_WORKSHEET_DATA tr:eq(1) th:eq(' + i + ') div').attr('id').replace(/apexir_/, '').toUpperCase();
      var vFoundColGrp = false; // This column has an associated column grp
      var vColGrp = ''; // Current Column group

      // Find the ID in the IR Groups global variable (genereated in AP)
      for (var j = 0; j < gPageIRColGrps.row.length; j ++ ){
        if (gPageIRColGrps.row[j].COLUMN_ALIAS.toUpperCase() == vColId) {
          vFoundColGrp = true;
          vColGrpExists = true;
          vColGrp = gPageIRColGrps.row[j].COLUMN_GROUP;
          break;
        }//if
      }// For IR Col Groups

      // Only print the col group header for the previous entry. This allows us to set the col span for similar groups
      // Have to do it this way to support IE  (otherwise we could look at the previous entry and update it's col span

      // If the current
      if (vColGrp.length > 0 && vColGrp == vPrevColGrp){
        // Don't display the
        vColSpan = vColSpan + 1;
      }
      else if(i > 0) {
        // Display the previous item
        $('#irColGrpRow').append('<th colspan="' + vColSpan + '">' + vPrevColGrp + '</th>');
        vColSpan = 1;
      }

      // If this is the last item then display it
      if (i == $(vTbl[0].rows[1].cells).length-1) {
        $('#irColGrpRow').append('<th colspan="' + vColSpan + '">' + vColGrp + '</th>');
      }

      vPrevColGrp = vColGrp;
    }// For each column being displayed

    // Remove the col group heading if no column groups:
    if (!vColGrpExists)
        $('#irColGrpRow').remove();
  } // Column groups exist

} // dispIRColGrpHeader

// Once the page is finsihed loading run the following (which will activate the column grouping display).
$(document).ready(function(){

// If page has an IR then set up for column grouping
  if ($('.apexir_WORKSHEET_DATA').length > 0) {

    function dispIRColGroups(){
      // Set the class for div hover since can't do in IE
      $('table.apexir_WORKSHEET_DATA th div').mouseover(function(){
        $(this).addClass('apexir_WORKSHEET_DATA_th_div_hover');
      });
      $('table.apexir_WORKSHEET_DATA th div').mouseout(function(){
        $(this).removeClass('apexir_WORKSHEET_DATA_th_div_hover');
      });  

     dispIRColGrpHeader();
     // This time out is required since after the report is refreshed via AJAX, need to reattach the l_LastFunction command
     setTimeout(
       function(){
        gReport.l_LastFunction = function(){dispIRColGroups();};
       },
       1000
      );
    }

    gReport = new apex.worksheet.ws('');
    gReport.l_LastFunction = function(){dispIRColGroups();}
    dispIRColGroups();
  }//if
});