A blog about Application Express and Oracle related topics.

How to create an APEX 4.1 authorization plugin

The EA1 for APEX 4.1 is out, so people are trying to use what we built for the last months, hopefully with success. Building Authorization Plugins was one of my tasks. The new plugin architecture allows developers to build authorizations in a declarative way, instead of copying and pasting SQL and PL/SQL code. Here’s a small walkthrough on how to make use of it. The example plugin can be used for authorizations using the built-in user and group management of APEX.

  1. Go to Shared Components / Plug-ins
  2. Click “Create >”
  3. Enter Name: APEX Group Authorization
  4. Enter Internal Name: COM.MY-COMPANY.APEX.GROUP-AUTH
  5. Enter Type: Authorization Scheme Type
  6. Enter PL/SQL Code:
    function is_authorized (
        p_authorization in apex_plugin.t_authorization,
        p_plugin        in apex_plugin.t_plugin )
        return apex_plugin.t_authorization_exec_result
        l_group  varchar2(4000) := p_authorization.attribute_01;
        l_count  number;
        l_result apex_plugin.t_authorization_exec_result;
        select count(*)
          into l_count
          from apex_workspace_group_users
         where user_name  = p_authorization.username
           and group_name = l_group;
        l_result.is_authorized := l_count > 0;
        return l_result;
    end is_authorized;
  7. Enter Execution Function Name: is_authorized
  8. Click “Create”
  9. Click “Add Attribute”
  10. Enter Label: Group Name
  11. Enter Type: Text
  12. Enter Required: Yes
  13. Click “Create”

The plugin has been created. Now we define an authorization based on the plugin.

  1.  Go to Shared Components / Authorization Schemes
  2. Click “Create >”
  3. Click “Next >”
  4. Enter Name: Is Manager
  5. Enter Scheme Type: APEX Group Authorization [Plug-in]
  6. Enter Group Name: Manager
  7. Enter Error Message: Only Managers can see that (it gets displayed if a page or application authorization fails)
  8. Click “Create”

To test our authorization scheme, we have to create users and assign groups to them, then apply the authorization to some pages, regions or items. But I’m sure you already know how to do that ;-)

In APEX 4.0, you would have probably built the authorization scheme using an exists SQL query. And copied the query to every other group authorization scheme (Is Sales, Is Accounting, …), which is harder to maintain. Plugins can help to encapsulate the authorization code in one place and prevent mistakes.

Because EA1 users do not have easy access to APEX package specs, here are the data type declarations for authorization plugins:

create or replace package wwv_flow_plugin as
type t_authorization is record (
    id                   number,          -- internal id of authorization scheme
    name                 varchar2(255),   -- authorization scheme's name (see step 17)
    username             varchar2(255),   -- current user's name
    attribute_01         varchar2(32767), -- plugin attribute values 1 to 15
    attribute_02         varchar2(32767),
    attribute_03         varchar2(32767),
    attribute_04         varchar2(32767),
    attribute_05         varchar2(32767),
    attribute_06         varchar2(32767),
    attribute_07         varchar2(32767),
    attribute_08         varchar2(32767),
    attribute_09         varchar2(32767),
    attribute_10         varchar2(32767),
    attribute_11         varchar2(32767),
    attribute_12         varchar2(32767),
    attribute_13         varchar2(32767),
    attribute_14         varchar2(32767),
    attribute_15         varchar2(32767) );

type t_authorization_exec_result is record (
    is_authorized        boolean );        -- set to true if authorization succeeds
end wwv_flow_plugin;

The 2nd type might seem excessive, but we prefer records, so extensions in later releases will not change function signatures.

EDIT: 2011-08-25
There was an inconsistency in the record types t_authorization and t_authentication that we could fix before the 4.1 release. Both now contain an attribute username, I renamed t_authorization‘s user_name. Thanks go to Scott Spendolini for finding that issue.


11 responses to “How to create an APEX 4.1 authorization plugin

  1. Dan McGhan May 9, 2011 at 19:25

    Very nice! Looking forward to authentication plug-ins!

  2. VANJ May 10, 2011 at 14:05

    Looks great. But I am not sure I understand. Your introduction mentions using this with LDAP but I would have thought verifying user credentials (single sign-on i.e. user/password are retrieved from Windows) against a Active Directory/LDAP server would be the job of an *authentication* scheme and not an authorization scheme.

    Can this new plugin concept be used to build a LDAP-based authentication scheme described above?

    • chrisonoracle May 10, 2011 at 14:16

      Thanks! Let’s see if authentication plugins make it into EA2….

      @VANJ: Sorry if the text is misleading. LDAP can be used both for authentication (see if user is in the directory, correct password) and authorization (is user in the LDAP group?). For LDAP authorization, you can already use apex_ldap.{is_member, member_of, member_of2} to check the user’s privileges. A plugin could encapsulate the call and provide values for certain parameters.

      • VANJ May 10, 2011 at 14:24

        @Christian – Ok that’s nice. But as you can see from the OTN forums, seamless integration with Microsoft AD/LDAP to provide single-signon (as in no login screen, read username/password from Windows and verify against LDAP/AD server) in an Apex application is a long-standing request. Customers have been using Apache mod_ntlm in conjunction with some custom page_sentry code but mod_ntlm is no longer actively maintained, doesn’t work with the new Apex listener or the EPG. We really need a good solution in this space.

        Any ideas?

  3. chrisonoracle May 10, 2011 at 14:50

    @VANJ: We are aware of the requirement for single sign-on via AD and the issues regarding mod_ntlm and pl/sql based solutions (e.g. my colleague Jason’s page sentry here). I can only say that once we have authentication plugins, the sharing of an NTLM solution will be easier. Maybe one time there will even be an extension to the Apex Listener to support NTLM via a CGI header variable.

    • VANJ May 10, 2011 at 14:59

      As you know, many (if not most) corporate environments use the Microsoft stack and IE still dominates. As such, asking for a username/password in any Apex application is not acceptable. All intranet applications have to be able to do single-signon via AD. As I said, we have been using Apache/mod_ntlm since Apex version 1.6 (2005 or so) but I am afraid that changes in the technology stack will render this solution obsolete and break all our Apex applications. So I am trying to be pro-active and have a alternative solution up my sleeve. Jason’s solution is really not acceptable from a security perspective, as I discussed here

      Looking forward to more enhancements in this area. Thanks for your time.

  4. antonpug November 2, 2011 at 15:16

    I am having issues with the described plugin – I followed your directions closely, however I get a WWV_FLOW_PLUGIN_ENGINE.NO_EXECUTION_FUNCTION error? I am not sure what is causing it. Do you think you might know?

  5. Frank Bohlken January 13, 2012 at 13:32

    Nice piece of work explanation,
    I have used on the moment authorization schema’s with function returning boolean and issued following problem:
    On my start screen an user can change from department.
    For each department changes the authorization.
    When I use a boolean function the test the authorization it works fine till i change from department.
    This is because Authorization schemes of type Exists, Not Exists, and Function Returning Boolean are cached on a per session basis to improve performance.
    Will this plugin solve this problem?


    • chrisonoracle January 13, 2012 at 14:22

      Hi Frank,
      thank you. Per default, authorization results are cached for the session. However, you can change an authorization’s “Evaluation Point” to “Once per page view”. Then it will be evaluated for each request, when APEX first encounters it during the show/accept processing. You could also call apex_util.reset_authorizations during submit processing on the start screen, when the user changes the department.

  6. Pingback: APEX 4.1 and more | Oracle Administrators Blog - by Aman Sood

Leave a Reply

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

You are commenting using your 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