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:
- It’s already been figured out by the pros!
- Most everyone that would use one of my applications already has an Oracle account.
- 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
1) Here’s a minimalist sample Oracle authorization function:
CREATE OR REPLACE FUNCTION is_allowed(app_user_in IN VARCHAR2) RETURN BOOLEAN IS
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
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’;
WHEN no_data_found THEN
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘EXCEPTION IN is_allowed – ‘ || SQLCODE || ‘: ‘ || SQLERRM);
2) Call the above function by putting the following in the authorization schema’s Expression1 field:
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.
Please see Admin to get authorization.
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.
- 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.
- Copy the basic authorization scheme to an auth_only scheme
- Change the calling expression1 to pass a keyword to the new role_required parameter, that you test for in your function.
- e.g. return is_allowed(v(‘APP_USER’), ‘ADMIN’)
- Attach to each restricted page