Apex Authorization Schemes – PL/SQL Function returning Boolean

January 24, 2008

Personally, I’ve found the authentication and authorization stuff a little tricky and have spent way too much time trying stuff out.

I was trying to use our college’s single-sign-on web authentication method, but never quite got it working.  I found out last week that an Oracle programmer who’s smarter than me figured it out!  But I still find the Oracle Database Account method to be worth using because:

  1. It’s already been figured out by the pros!
  2. Most everyone that would use one of my applications already has an Oracle account.
  3. I’m not that big a fan of reinventing the wheel!

Having chosen the Database Account Authentication method, here are my notes on creating the simplest authorization scheme possible.

Authorization Scheme Definition

Calling syntax

1) Here’s a minimalist sample Oracle authorization function:

Description: Authorizes based on Apex user name.
Called by Apex to authorize administrators
to access Apex application.
Call this from a single page after the user logs in via Database Account

Parameters: app_user_in – Oracle OPS$ name, uppercase

Results: TRUE if in administrator list

Author: me

Usage: return is_allowed(‘OPS$ME’);

ret_value BOOLEAN := FALSE;
status_msg VARCHAR2(16) := ‘FAILURE’;
IF app_user_in IN (‘OPS$ADMIN’)
ret_value := TRUE;
status_msg := ‘SUCCESS’;

RETURN ret_value;
WHEN no_data_found THEN
RETURN ret_value;
RETURN ret_value;
END is_allowed;

2) Call the above function by putting the following in the authorization schema’s Expression1 field:

return is_allowed(v(‘APP_USER’))

3) Define as run once per session

4) In the Error Message field, be sure to include a way for them to get to your login page, such as:

Sorry, you are not authorized to use this application.

<br> <br>

Please see Admin to get authorization.

<br> <br>

<a href=”&LOGOUT_URL.“>Login</a>

I had trouble when I was originally developing this that my authorization failed and I couldn’t get back to log in as an authorized user.  The default error page didn’t show a Login link.  So I suggest one for the error message.  But later once I’d fixed my authorization function, I saw there was a Login link.  I swear it wasn’t there before!  Hence the above suggestion.


  • Do not attach to application

If you put it on the application, it will be called for your login page and fail.

Unless you add a parameter for the apex page number and test for your login page # (defaults to 101).

  • Attach to first app page only, once they’ve authenticated via Database Account

It will get called once during the session when they hit your first page.

If they’re not, they’ll get the defined Error Message.

  • If your application has some restricted pages

You’ll need to build a more sophisticated authorization function, that you can call with different values from the restricted pages.

  1. Add a role_required parameter to your authorization function, maybe default it to NULL, so your basic authorization scheme doesn’t need to pass it.
  2. Copy the basic authorization scheme to an auth_only scheme
  3. Change the calling expression1 to pass a keyword to the new role_required parameter, that you test for in your function.
  4. e.g. return is_allowed(v(‘APP_USER’), ‘ADMIN’)
  5. Attach to each restricted page

One Response to “Apex Authorization Schemes – PL/SQL Function returning Boolean”

  1. Thank you, I have just been looking for info approximately this subject for ages and yours is the greatest I’ve came upon so far. But, what in regards to the bottom line? Are you positive in regards to the source?|What i don’t realize is in reality how you are no longer actually much more well-preferred than you may be right now. You are so intelligent.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: