Authorisation schemes (or "a deerstalker and a pipe")

Yesterday I met with my clients to give them a first look at the application I've been developing for them in Apex. They're old clients of ours who have been buying applications developed in Oracle Forms from us for over a decade, and so yesterday was not just an unveiling of the new application but an opportunity to hint at the future development direction of our company. No pressure then.

Fortunately they liked what they saw, and thought that the app looked better than any of our Oracle Forms offerings from the past. Phew!

Today, however, a small issue arose. We've managed to sell this app to two different clients (and a few others are very interested); however, our customers are very much like the blind men who went to see the elephant - they all interpret the government legislature that referees their existence slightly differently and so they all want the application to look slightly different and do things slightly differently (they say "to-ma-to", we say "to-may-to"). What to do?

Authorisation schemes to the rescue!

The idea behind authorisation schemes is easy: you attach some code to an object and if it resolves to true the object is displayed, otherwise it isn't. But I don't want the hassle of setting authorisation schemes object-by-sodding-object, what I need is the ability to, in one place, attach different authorisation schemes to various objects (items, buttons, regions, pages, list entries) and, in that way, easily customise my app for every client. Time to play Sherlock Holmes and delve into the innards of Application Express.

(It is my duty to warn you here that you must be very careful if you decide to tamper with the tables on which Application Express is built as you may cause irreparable damage. Ah, but what the heck, we're software developers - twice as brave as James Bond and nine point five times as sexy! Let's dive in with our eyes closed!)

I quickly discovered that Application Express stores all its authorisation schemes in a table called FLOWS_030000.WWV_SECURITY_SCHEMES. From this table I will get the Id and Name of the authorisation scheme that I wish to apply to my object.

Further investigations reveal that I can get a list of my page items from the view APEX_APPLICATION_PAGE_ITEMS. This view, however, is built on the table FLOWS_030000.WWV_FLOW_STEP_ITEMS. This is the table I will be updating to apply authorisation schemes to my items.

Now I know this it is a simple task to build an SQL report listing my many page items along with a select list (APEX_ITEM.SELECT_LIST_FROM_QUERY) that will allow me to choose the authorisation scheme I want attached to each one. Of course I'll also need a PL/SQL process to actually update the table when I press my Submit button.

Easy-peasy. When you know how.

Page items are not the only objects you may wish to apply authorisation schemes to. To apply them to regions the view you need is APEX_APPLICATION_PAGE_REGIONS and the table you need to update is FLOWS_030000.WWV_FLOW_PAGE_PLUGS. (The column in this table that controls the authorisation scheme is, counter-intuitively, called PLUG_REQUIRED_ROLE.)

Buttons are listed in the APEX_APPLICATION_PAGE_BUTTONS view; to update the authorisation scheme you'd want to update FLOWS_030000.WWV_FLOW_STEP_BUTTONS.

Other views that will be of interest to you are: APEX_APPLICATION_TABS, APEX_APPLICATION_LISTS, APEX_APPLICATION_LIST_ENTRIES. And other underlying tables that you will want to update include: WWV_FLOW_TABS, WWV_FLOW_LIST_ITEMS.

Now that I have built a page where I can easily manipulate the authorisation of my various objects it is the easiest thing in the world to create an "Export this configuration to a file" button, behind which I will create an SQL file containing scripts to set up my underlying tables exactly the way I want them. This way I can configure my application for one customer, export my configuration file, and then completely reconfigure it for the next client knowing I can simply revert to my old set-up by running a file.

Go on, say it. Genius.