Tips & Tricks


Public Check Authorization

Written by Richard Weug. Posted in Tips and Tricks

APEX Authorization Schemes are a very effective and simple way to restrict elements in our applications.  Once defined, these authorizations can be applied to the majority of elements in APEX: Pages, Regions, Items, Buttons, Processes, Branches, etc…

There are several ways to code them, it will depend on your needs, but ultimately they return TRUE or FALSE.  Is the user ADMIN or NOT ADMIN.  Say for example that we have a MYAPP_USER_ROLES table that stores ROLE_KEY and USERNAME columns. In this case, we could define an “ADMIN” Authorization Scheme of type “Exists SQL Query” that looks like this:


select 1
from myapp_user_roles
where role_key = 'ADMIN'
  and username = :APP_USER

Then we could secure a page by setting the Authentication Scheme to ADMIN. This would make the page completely unavailable to anyone that does not have the ADMIN role_key assigned. We could have a page with a form available to any user, but make a Delete button available only to ADMIN. Or have a field on the form to show for ADMIN only but unavailable (removed) to everyone else. Assigning the Authorization Scheme to a element, effectively eliminates that element.

However, what if we want to make an item read only for all users and editable to ADMIN? In this case we cannot use the Authorization Scheme drop down because that would make the item disappear. We’ll want to use the “Read Only” condition.

Use the APEX_UTIL.PUBLIC_CHECK_AUTHORIZATION API function call to see if a given Authorization Scheme is available to the current user or not. The function receives a one case sensitive value, the name of the scheme to check.

Set your Read Only condition to “PL/SQL Expression” and the code to


Item Read Only Condition

You can also use this API call in your PL/SQL processes to enhance your logic.

As a quick example, maybe Admin users get a record initialized with a different status than regular users.

if apex_util.public_check_authorization('ADMIN') then
   -- Administrators can skip the pending status
   l_status := 'IN PROGRESS';
   l_status := 'PENDING';
end if;

Another useful application is when you need to combine multiple Authorization Schemes and it’s not practical to create a new unique Scheme.

 or apex_util.public_check_authorization('Approver')
 or apex_util.public_check_authorization('Super User')