Monday, 25 February 2013

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
  ,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
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
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
No condition
Action: Refresh region "Object Privileges"
Dynamic action screengrab
And that's it!



Nathan Catlow said...

Hi Scott, great tutorial, could I suggest a small change please?

,'javascript:pageItemValue(''' ||"GRANTED_ROLE"|| ''')' As link

would ideally be;

,'javascript:pageItemValue(''' || apex_escape.js_literal("GRANTED_ROLE") || ''')' As link

although not an issue with your example, this code may be cut and pasted into a situation where cross-site scripting might be an issue. Thanks.

Mark Lancaster said...

Another minor refinement, instead of using local javascript function pageItemValue, just use $s()

e.g. 'javascript:$s(''P13_SEC_APPLICATION_ROLE_ID'', '''||||''');' as link

Scott Wesley said...

@Nathan - quite true. As much as I try, I still sometimes forget to escape strings when relevant.
This is a good habit for people to form, and XSS is a large threat to nip in the bud where possible.

@Mark - I'm torn with this. A recent project has re-affirmed again & again the value in encapsulation, even when you don't think it would be necessary.
Though in this case it might be worth letting it slide.

Tom said...

Hi Scott,
A while ago I also made a short blogpost+example page with the apex tree. I went a whole other way about this, with a bit more javascript, and using jstree's functions rather than using the 'link' column. Just mentioning for diversity :) demo application on

Scott Wesley said...

Thanks for that, Tom - I remember seeing your example the other day when researching the jsTree as per that OTN post you helped me with.