In APEX 4.2, whenever I need to apply an Authorization Scheme to components of a page I tend to create tables for roles, privileges, a role_privilege linker table and usually a user_role table. I then create an Authorization Scheme named after screen components.
For example: I have a Role called “Finance Team Leader”, a privilege called “Authorise Payment” and I also have an Authorization scheme called “Accounts Page – Authorise Payment Button”. I then apply this Authorization scheme to my button. It works, but in a large application I have dozens and dozens of these Authorization schemes all running a slight variation of the same code for each component (e.g. button, region, page, etc.) on a page.
In APEX 5.0 we have a solution to this – May I introduce Component Level Authorization Schemes.
Component Level Authorization Schemes can easily be created by selecting “Once per component” in the “Validate authorization scheme” setting when we create or edit an Authorization Scheme.
Once per Component means the Authorization is evaluated once for each component that references the authorization scheme, and the result is saved in the session. Further requests to render or process the scheme use the memorized result. Meaning, each rendering of the component executes the scheme once, and then it uses the memorized result for the duration of the session.
New for APEX 5.0, in our “PL/SQL Function Returning Boolean” Authorization Scheme Code we now have access to three new application items:
These are really handy as instead of using a Privilege record which maps to logical page component we can create a Privilege record which maps to a physical page component – meaning fewer Authorization Schemes.
For Example: In our Privilege table we need to create columns to hold Page ID (or Page Alias), Component Name and Component Type (this needs to hold values like APEX_APPLICATION_PAGE_REGION and APEX_APPLICATION_BUTTONS) and, if we like, Component ID.
We then need to insert records in to the Privilege table to represent each of the APEX page components which require authorization.
In the code for our Component Level Authorization Scheme, we can pass the application items :APP_COMPONENT_NAME, :APP_COMPONENT_TYPE, :APP_COMPONENT_ID together with :APP_USER_ID and :APP_PAGE_ID (or :APP_PAGE_ALIAS) as parameters to a database function which evaluates the users access against their role, role_privilege and ultimately the Privilege table which then returns a TRUE/FALSE result based on their access.
In the above example I have a data driven authorization solution with one component level Authorization scheme instead of the dozens I would have needed in APEX 4.2.
Finally an honourable mention to the “Always (No Caching)” Authorization Scheme; this is also a new feature for APEX 5.0. This does what it says on the tin, the result is not saved in session and is evaluated for every render or process of the page or component. There will be a performance overhead of re-evaluating the result especially on pages containing many components with assigned Authorization Schemes. However it could be useful in some applications where user authorization is rapidly changing and the user is not required to log off/on to acquire their new authorizations.
Using “Always (No Caching)” we also have access to the 3 new application items. These application items will return null values when using “Once per session” and “Once per page view).
Author: Matt Mulvaney
Job Title: Senior Oracle APEX Development Consultant
Bio: Matt is an experienced APEX solution designer having designed numerous complex systems using a broad range of Oracle Technologies. Building on his previous experience of Oracle Forms & PL/SQL, he is entirely focused on providing functionally rich APEX solutions. Matt promotes APEX as a software platform and openly shares best practises, techniques & approaches. Matt has a passion for excellence and enjoys producing high quality software solutions which provide a real business benefit.