Creating ExtJS Tree JSON Objects with Custom Authorisation in SQL

Okay, the title to this post may seem a tad specific but the principles are transferable to creating pretty much any Ext Tree JSON object under any circumstances as long as you have the data in the database.

Previously I have be using Mark Lancaster’s example for creating the JSON object for an Ext Tree using the APEX List template. This has been working flawlessly for a good while and I love the ability to rejig the tree hierarchy using the list functionality in APEX.

However, I recently had requirement to implement a custom authorisation for the tree – hiding all entries that the user was not allowed to see. This would have been easy normally – I was using the APEX list functionality so I could just apply conditional display. Unfortunately, I couldn’t use this approach because my target was an URL with a target like:


This meant that APEX didn’t know which page the entry was targeting and I therefore couldn’t pass it through to the authorisation function to see if the option was available.

So, I set about creating my own JSON object generating SQL query, I had a google about and many people have done this in PL/SQL but it is normally limited a certain number of levels in the tree and these levels are hard-coded.

Creating the initial object with no authorisation was reasonably straight forward, I had Mark’s example for the  syntax and I created a small list in APEX using that template so that I had a reference point to work to.

To add the authorisation I added the page that I wanted the list entry to be checked against to the ‘User defined attributes’ in APEX. In the apex_application_list_entries table, this is stored as entry_attribute_01.

My problems started where there where multiple levels in the tree and the user was authorised to an entry at the lowest level but not to some of the levels above that in the same path. This was overcome by getting the whole path for each entry, checking if the user was authorised to the lowest level and then walking back up the path to make sure they then were shown all the folders in order to get to that leaf.

My colleague, Kevan Gelling, suggested that a pipelined function would help with this so we created this simple one:

CREATE OR REPLACE TYPE t_string_to_table

    p_String                       VARCHAR2,
    p_Delim                        VARCHAR2 DEFAULT ':',
    p_Start                        VARCHAR2 DEFAULT 'N',
    p_End                          VARCHAR2 DEFAULT 'N'
  RETURN  t_string_to_table PIPELINED
    l_Char                         VARCHAR2(1);
    l_Element                      VARCHAR2(4000);
    l_loopStart                    NUMBER;


    l_Element := NULL;

    IF p_Start = 'Y' THEN

      l_loopStart := 2;


      l_loopStart := 1;

    END IF;

FOR i IN l_loopStart .. LENGTH(p_String)

l_Char := SUBSTR(p_String,i,1);

      IF l_Char = p_Delim THEN

        PIPE ROW (l_Element);

        l_Element := NULL;

ELSIF p_End = 'N' AND i = LENGTH(p_String) THEN

        l_Element := l_Element||l_Char;
        PIPE ROW (l_Element);


        l_Element := l_Element||l_Char;

      END IF;




Now the SQL for the JSON object is as follows (thanks to Kevan for rationalising the last part a bit):

menuList AS
       SELECT list_entry_id,
-- In this example, the target is an URL (like javascript:loadPage(1);)
-- entry_attribute_01 is used to denote the target page number
-- Insert your authorisation function here
-- 'Y' is just hardcoded for demonstration
-- purposes for example -
-- my_auth.isPageAuthorised(:p_user_name,
--                          :p_app_id,
--                           entry_attribute_01)
       FROM   apex_application_list_entries
       WHERE  application_id      = :p_app_id
       AND    list_name           = 'Main Menu'
menuHier AS
      SELECT  list_entry_id,
ROWNUM all_row_num,
              LEVEL                                      the_level,
              CONNECT_BY_ISLEAF                          isleaf,
SYS_CONNECT_BY_PATH( isAuthorised, ':' ) authPath,
SYS_CONNECT_BY_PATH( list_entry_id, ':' ) idPath,
                SELECT  COUNT(*)
                FROM    apex_application_list_entries  l2
                WHERE   l2.list_entry_parent_id = menuList.list_entry_id
              )                                          child_count
FROM menuList
      START WITH list_entry_parent_id IS NULL
      CONNECT BY PRIOR list_entry_id = list_entry_parent_id
      ORDER SIBLINGS BY display_sequence,
menuAuth AS
SELECT menuHier.*,
              COUNT(*) OVER ()                                      row_count,
              ROW_NUMBER() OVER (ORDER BY all_row_num)              auth_row_num,
              ROW_NUMBER() OVER (PARTITION BY the_level,
                                 ORDER     BY all_row_num )         item_row_num,
              LEAD(the_level, 1, 1) OVER(ORDER BY all_row_num)      lead_level
FROM menuHier
      WHERE   list_entry_id IN
                  SELECT  DISTINCT
FROM menuHier,
TABLE(stringToTable(idPath)) i
--                         Last character in path = Y
WHERE SUBSTR(authPath, LENGTH(authPath), 1) = 'Y'
      ORDER BY all_row_num
--      START: JSON
          WHEN the_level = 1 THEN
--      ITEM: Indent levels
|| RPAD(' ',(the_level - 1) * 2)
--      ITEM: Comma separator
     || CASE
           WHEN item_row_num != 1 THEN
--      ITEM: Item details
     || '{'
     ||   '"id":"'   || list_entry_id || '"'
     ||  ',"text":"' || entry_text    || '"'
     ||  ',"href":"' || entry_target  || '"'
--      ITEM: Leaf information */
     || CASE
WHEN isleaf = 0 THEN -- With children
         || ',"children":['
WHEN isleaf != 0 THEN -- With no children
     || -- END: Item
WHEN isleaf != 0 THEN
      -- END: Level
     || CASE
WHEN isleaf != 0 AND
               lead_level < the_level     THEN
RPAD(' ', 1 + ((the_level - lead_level) * 2), ']}')
        -- END: JSON
     || CASE
          WHEN auth_row_num = row_count THEN
FROM menuAuth

This can go anywhere, in an application process, an on-load page process etc, etc. The joy of it is, once you have it working, you can still maintain the hierarchy in the APEX list using the GUI but you can affect it by anything you have in data. You could store ExtJS properties etc in your list entries and carry those through to the tree for example.

This hasn’t been fully tested yet but I will update with any bugs/limitations I come across.


3 thoughts on “Creating ExtJS Tree JSON Objects with Custom Authorisation in SQL

  1. I don’t have a comment on this specific blog, but I REALLY like the new site layout. Very slick. Reminds me of the old Blink182 site.

  2. Hi Ben

    I use a different strategy worth sharing, instead using authorizations to determine if the user has a table based role or not.

    It doesn’t matter what the target is, I’m not going to give you access to the leaf node if you don’t have the table based role. Those authorization function results can be cached per page request using pl/sql tables, or cached for the session using APEX global parameters.

    On the downside, you have to ensure your list items and targets have the same authorization rules – easily managed using the APEX views to compare.

    On the upside, eliminating expensive hierarchical queries is good, my tree loads FAST.

    The other issue you mentioned is branch nodes, where you may or may not want to display a branch depending on the child nodes. Here I use conditional logic, passing a list of roles based on the descendants. Once again the APEX views can validate your tree to identify the roles.

    Site navigation trees are typically static, so “hard coding” the authorization rules at design time is justified.

    I also dynamically construct trees for volatile data using packages, but that’s another story 😉


    • Hi Mark

      Agreed, my choice would have been to go down the role based approach – unfortunately I was required to use someone else’s authorisation scheme thats used for quite a few platforms and had to go down this road.

      As I poitned out, this approach can also be used for a host of other functions which is why it’s quite handy.

      This is included in a package in my implementation, I just wanted to post the SQL so that people can put it where they see fit.

      With regards to speed, even with calling the authoristaion package, this returns in about 70ms on my system which I don’t think is too bad!



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s