Application Express, Network ACLs and Oracle Database 11gR2

In Oracle Database 11gR1, a new feature was introduced called Fine-Grained Access to External Network Services. Succinctly defined, this feature gives an administrator control over which database users are permitted to access external network services, and on which ports. If an application relied upon the PL/SQL packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP or UTL_INADDR, they would now need to be given permission to access the external network service via a Network ACL. An excellent writeup of this feature is on ORACLE-BASE.

There have been two changes in this feature in Database 11gR2 which may impact Application Express users. These aren't necessarily documented so prominently in the release notes or README of Database 11gR2, so I felt it necessary to share them here.

1. In Database 11gR2 11.2.0.1, the precedence order in evaluation of the network ACL entries has been changed to most specific to least specific. More about this below.
   
   
2. In Database 11gR2 11.2.0.2, the network ACL now applies to any use of DBMS_LDAP.

In the installation guide for Oracle Application Express, we document this feature in Oracle Database 11gR1 and also how to create a network access control list which permits the APEX engine to access any network service on any network port. The name of the ACL in our example is power_users.xml. It just so happened that someone else on our instance of Application Express needed access to an outbound HTTP proxy, so I created a separate network ACL for this user.

The Network ACL privileges looked like:

SQL> column host format a30
SQL> column acl format a40
SQL> select host, lower_port, upper_port, acl from dba_network_acls;

HOST                           LOWER_PORT UPPER_PORT ACL
------------------------------ ---------- ---------- ------------------------------------
proxyserver.domain.com          80         80        /sys/acls/proxy_rule.xml
*                                                    /sys/acls/power_users.xml


SQL> column acl format a40
SQL> column principal format a30
SQL> select acl, principal from dba_network_acl_privileges;

ACL                                      PRINCIPAL
---------------------------------------- ------------------------------
/sys/acls/proxy_rule.xml                 SOME_OTHER_USER
/sys/acls/power_users.xml                APEX_040000

Prior to 11.2.0.1, if APEX_040000 needed to access the outbound HTTP proxy, this would be evaluated in terms of the least specific rule to the most specific rule. The ACL power_users.xml permitted access to all hosts on all ports, and thus, there were no issues. But in 11.2.0.1 and higher, this is now evaluated from most specific to least specific. If APEX_040000 now needs to access the outbound HTTP proxy, it is blocked and we'll encounter the dreaded error message "ORA-24247: network access denied by access control list (ACL)". A match for the host in the outbound HTTP proxy is found in /sys/acls/proxy_rule.xml, APEX_040000 does not have privileges on that ACL, and now the fine-grained access control blocks the request.

To correct this, I had to grant privilege on the proxy ACL to APEX_040000:

    dbms_network_acl_admin.add_privilege(
        acl => 'proxy_rule.xml',
        principal => 'APEX_040000',
        is_grant => TRUE,
        privilege => 'connect' );

To address the second point, in 11.2.0.2, access to the host and port specified by methods in the DBMS_LDAP PL/SQL package are now controlled via this same fine-grained access control to external network services.

I can understand, from a security perspective, why this behavior was changed. However, if you've come to rely upon this behavior, upgrading to Oracle Database 11gR2 may introduce some changed behavior when accessing external network resources.

Custom Authentication Scheme for Oracle Application Express and Oracle Access Manager - Addendum

As mentioned in my earlier post about Oracle Application Express integration with Oracle Access Manager, Dilip Gowda, an Oracle consultant, very kindly shared a Word document detailing all of the steps he performed to get Oracle Access Manager 10.1 working with Oracle Application Express 3.2. It can be downloaded from here. The custom authentication scheme for APEX should work with any APEX version - it's a generic header variable authentication scheme.

Once I get Oracle Access Manager 11gR1 installed and configured, I hope to prepare and share a similar document. The eventual goal is to turn this into an official whitepaper and then ultimately provide an out-of-the-box header variable authentication scheme in a future version of Oracle Application Express.

Custom Authentication Scheme for Oracle Application Express and Oracle Access Manager

Our customers frequently ask about how to integrate Oracle Access Manager authentication with Oracle Application Express. There is currently a thread on the Oracle Technology Network discussion forum, asking for this type of solution. It has always been my intention to present this as an official whitepaper and recommended solution from Oracle. However, I have been struggling with some Oracle Access Manager configuration issues and I simply did not want to delay any further. The "official" whitepaper and detailed instructions will have to come later.

Back in March, 2010, I took careful note of a message that Scott Spadafore on our team had sent to someone in Oracle Support. It was a generic solution for authentication via an HTTP header variable. A couple months ago, this question came up again and Tyler Muth provided me a slightly modified version of what Scott had originally authored. With some more minor modifications on my part, I can share this custom authentication scheme, which can be used with Oracle Access Manager and really any environment which will securely set a header variable to an authenticated username.

The page sentry function is:

create or replace function header_variable_page_sentry ( p_apex_user in varchar2 default 'APEX_PUBLIC_USER' ) 
    return boolean 
as
    l_cgi_var_name              varchar2(100) := 'REMOTE_USER';
    l_authenticated_username    varchar2(256) := upper(owa_util.get_cgi_env(l_cgi_var_name));
    --
    l_current_sid               number;    
begin
    -- check to ensure that we are running as the correct database user
    if user != upper(p_apex_user) then 
        return false; 
    end if;

    if l_authenticated_username is null then
        return false; 
    end if;     
  
  
    l_current_sid := apex_custom_auth.get_session_id_from_cookie;
    if apex_custom_auth.is_session_valid then
        apex_application.g_instance := l_current_sid;
        if l_authenticated_username = apex_custom_auth.get_username then
            apex_custom_auth.define_user_session(
                p_user=>l_authenticated_username,
                p_session_id=>l_current_sid);      
            return true;
        else -- username mismatch. unset the session cookie and redirect back here to take other branch
            apex_custom_auth.logout(
                p_this_app=>v('APP_ID'),
                p_next_app_page_sess=>v('APP_ID')||':'||nvl(v('APP_PAGE_ID'),0)||':'||l_current_sid);
            apex_application.g_unrecoverable_error := true; -- tell apex engine to quit            
            return false;
        end if;
        
    else -- application session cookie not valid; we need a new apex session
        apex_custom_auth.define_user_session(
            p_user=>l_authenticated_username, 
            p_session_id=>apex_custom_auth.get_next_session_id);
        apex_application.g_unrecoverable_error := true; -- tell apex engine to quit
        --
        if owa_util.get_cgi_env('REQUEST_METHOD') = 'GET'  then
            wwv_flow_custom_auth.remember_deep_link(p_url => 'f?'|| wwv_flow_utilities.url_decode2(owa_util.get_cgi_env('QUERY_STRING')));
        else
            wwv_flow_custom_auth.remember_deep_link(p_url=>'f?p='||
                to_char(apex_application.g_flow_id)||':'||
                to_char(nvl(apex_application.g_flow_step_id,0))||':'||
                to_char(apex_application.g_instance));
        end if;
        --        -- register session in APEX sessions table,set cookie,redirect back
        apex_custom_auth.post_login( 
            p_uname      => l_authenticated_username,
            p_session_id => nv('APP_SESSION'),
            p_app_page   => apex_application.g_flow_id||':'||nvl(apex_application.g_flow_step_id,0)); 
        return false;        
    end if;    
end header_variable_page_sentry;

The high-level steps to be performed are:

1. Compile this function header_variable_page_sentry in the parsing schema of your application
2. Create a new custom authentication scheme. In the Page Sentry Function attribute of the custom authentication scheme, enter: return header_variable_page_sentry;
3. Add directive PlsqlCGIEnvironmentList inside the corresponding APEX Database Access Descriptor. By default, OAM Webgate uses the header variable REMOTE_USER.
4. Secure the APEX application in Oracle Access Policy Manager by defining its corresponding Policy Domain.
5. Back in your APEX application, make this new authentication scheme "current" for your application.

The custom authentication scheme should work in any version of Application Express. A gentleman from Oracle Consulting got this to work at a customer site using APEX 3.2 and OAM 10g. He very graciously put together a document detailing all of the steps he performed in Application Express and Oracle Access Manager to get this to work, which is invaluable to someone like me who is essentially OAM-ignorant. I've asked for his permission to share this document, and when/if I get his okay, I'll make it available from this blog.