APEX Tree region use case - Privileges

While experimenting with the APEX tree region, I came up with a use case that demonstrated some of the related features, as well as providing some useful information about the roles defined in my database.

I will note that to do so my parsing schema required access to DBA_ROLE_PRIVS and DBA_TAB_PRIVS - something that was fine in my development environment, but would be rightly questioned in a production scenario, but I will demonstrate nonetheless.

Final layout

My page looks like the following screen grab, and below I break down the steps to get there.

The tree lists roles assigned to user set in the text item, and the accompanying report region displays object privileges for selected tree node / database role.

Tree region and partnering classic report.

Define tree region

First step would be to create a new page based on a tree region.

Accept the defaults, or enter attributes as desired - such as tree template.

Once you get to defining the table or view, I just selected anything because I'm going to override the query once I'm done anyway - so from there I left all attributes as default and created the page.

Edit the page and open the tree attributes to use the following query.

select case when connect_by_isleaf = 1 then 0
            when level = 1             then 1
            else                           -1
       end as status
  ,level
  ,granted_role||NULLIF((SELECT ' ('||COUNT(*)||')' FROM dba_tab_privs WHERE grantee = aa.granted_role),' (0)') title
  ,null icon
  ,granted_role value
  ,null tooltip
  ,'javascript:pageItemValue('||apex_escape.js_literal(granted_role)||')' As link
FROM dba_role_privs aa  
CONNECT BY grantee = PRIOR  granted_role
START WITH grantee = UPPER(:Pn_USER)

Updated to include apex_escape.js_literal

Create supporting items

Now the page is defined we can create some supporting items.

Create text item Pn_USER - this will accept a username to drive the tree query. You could also define this as an Autocomplete item, using the query for the LOV

SELECT username FROM all_users

Then create a hidden item Pn_SELECTED_NODE, source always replacing session state. This value will be set by the Link field in the query, which calls some JavaScript to be defined on your page.

Edit page properties

Edit page and set JavaScript function declaration as

function pageItemValue(somevalue)
{
  $s('Pn_SELECTED_NODE', somevalue);
}

This sets the value of the selected node in a hidden field, which we can listen for changes to refresh another region that displays the relevant object privileges that may be granted to that role.

Create classic report

This report will show the "Object Privileges" for the selected tree node - the selected role.

SELECT table_name, privilege
FROM dba_tab_privs
WHERE grantee = :Pn_SELECTED_NODE

Once created, edit region grid layout to display as desired - I set "Start new row" to No.

Create dynamic action

When the user selects a node, we want the neighboring report to automatically refresh, so we create a dynamic action that listens for change to the selected node.
My dynamic action has the following properties:
Event: Change
Item: Pn_SELECTED_NODE
No condition
Action: Refresh region "Object Privileges"

Dynamic action screengrab

And that's it!

Scott