In order to make sure that all of our applications share a common look and feel, as well as some basic things like navigation and a central menu page, I've been working on setting up an underlying framework at my office prior to teaching my fellow developers how to build applications. (It's much easier to establish common ground from the start than to retroactively apply it.) Today, I finalized the authorization system, and I'm very pleased with how it turned out.
The requirements for the authorization system are fairly straightforward. APEX allows for a single authorization to be assigned to an object (page, page item, button, process, etc.), but we don't know how many different types of authorizations each of our applications will need. Some may not need any (pure reports, for instance), while others might have several different levels which may or may not overlap. This latter case precludes the simplistic “every authorization level includes all the lower levels”. I also wanted it to be as simple to implement as possible, to minimize the chances for error.
The core of the authorization system is derived from the setup I did for our (dynamic) central menu page. Basically, we have a set of tables to hold information about our applications and how they're grouped on the menu page. To this, I added two tables–one defines authorization levels for each application, and the other maps users to each of these authorization levels. So, for instance, if Application 1 has universal read access, some users with limited write privileges, and a very few users with overall write access, I only need to define two authorization levels and assign users to the two write ones as needed (a shuttle control works great for this, by the bye!).
The next step was to define the authorization framework. I did this by creating a central function:
function check_authorization(p_authorization_level in varchar2) return boolean is l_levels apex_application_global.vc_arr2; l_ok number := 0; begin l_levels := apex_util.string_to_table(p_authorization_level); for i in 1..l_levels.count loop select count(*) into l_ok from authorized_users au, application_authorizations aa where au.authorization_id = aa.authorization_id and upper(au.username) = v('APP_USER') and aa.application_id = v('APP_ID') and au.authorization_level = l_levels(i); if l_ok > 0 then return true; end if; end loop; return false; end check_authorization;
Then, when creating the authorization schemes in APEX, all of them follow the same pattern: the method is PL/SQL function returning boolean, and the function is simply “return check_authorization(
Finally, note that I'm using APP_USER and APP_ID to check the current user and application. This, again, helps reduce the chances for errors, since we don't need to worry about accidentally feeding the function the wrong application ID.