High 

Availability Architecture for Oracle 
REST and APEX  
Installation & Configuration using Apache HTTP and Tomcat with HTTP Header 
Authentication 

By Peter Magee 
Technical Lead 
Stellar Innovations & Solutions 
 

September 2019 

Disclaimer: This document is provided “as is” and is not supported by Oracle. There are many factors, 
moving parts, and requirements involved in any system configuration which means that the information 
contained  in  this  document  is  always  subject  to  change  and  therefore  not  guaranteed  in  any  way. 
Everything suggested in this document should be validated through the Oracle Support SR process, Oracle 
Documentation, and your Database and System Administrators before performing software and hardware 
acquisitions or doing anything in your Production environment. In addition, it is recommended that all 
changes should be tried in Development or Test environments before doing anything in Production. 
HA Architecture for Oracle ORDS and APEX  

Contents 
Introduction .................................................................................................................................................. 1 
APEX Server Architecture .......................................................................................................................... 1 
Alternative Configurations ........................................................................................................................ 2 
Scope ......................................................................................................................................................... 2 
Software Versions ..................................................................................................................................... 2 
Conventions .............................................................................................................................................. 2 
Operating System .......................................................................................................................................... 3 
Oracle Application Express (APEX) ................................................................................................................ 3 
Apache Tomcat Services ............................................................................................................................... 4 
Oracle REST Data Services (ORDS) ................................................................................................................ 7 
Optional ORDS Configuration ................................................................................................................. 10 
Apache HTTPD Services ............................................................................................................................... 11 
APEX Workspace Authentication ................................................................................................................ 21 
APEX Application Authentication ................................................................................................................ 22 
APEX Application Authorization .................................................................................................................. 24 
APEX Application Monitoring ...................................................................................................................... 28 
References .................................................................................................................................................. 29 
Biography .................................................................................................................................................... 29 
   

   
 

ii 
 
HA Architecture for Oracle ORDS and APEX  

Introduction 
This guide includes step‐by‐step instructions to install and configure the Apache HTTPD, Tomcat, Oracle 
REST  Data  Services,  and  Oracle  Application  Express  (APEX)  technology  stack  within  a  simple,  highly 
available infrastructure. It also describes basic HTTP Header‐based user authentication and authorization 
configuration within an APEX application built within the stack. 

APEX Server Architecture 
The APEX server architecture described in this paper is designed to be highly available and includes an F5 
LTM load balancer, multi‐node web and application tiers, and an Oracle RAC database. The load balancer 
divides incoming connections between two or more Oracle Linux 7 Apache web servers. The web servers 
reverse proxy connections to Tomcat application servers using load balanced AJP protocol. The Tomcat 
application servers are configured to share session information so that live sessions can be failed over 
from  one  server  to  another  without  impact  to  the  end  user.  Tomcat  hosts  Oracle  REST  Data  Services, 
which connects to Oracle APEX within the Oracle RAC database cluster. Database connections are also 
balanced across multiple nodes to ensure minimal disruption in the event of an unplanned outage. 

LOAD
BALANCER

webserver1 webserver2

appserver1 appserver2

database1 database2 database3

 

In this configuration, up to N‐1 nodes (where N is the total number of nodes in either the web, application, 
or  database  tiers)  can  fail  or  be  offline  for  maintenance  and  user  operations  can  still  continue 
uninterrupted. This allows most forms of system maintenance and software patching to be carried out 
without application downtime. Additional web, application, or database nodes can be created or removed 
as required to deal with specific load conditions. 

1 
 
HA Architecture for Oracle ORDS and APEX  

Alternative Configurations 
Alternative development and testing configurations can use a stand‐alone (non‐RAC or RAC One Node) 
database,  and  a  single  web/application  server  with  both  Apache  HTTPD  and  Tomcat  installed.  Oracle 
Clusterware (aka Grid Infrastructure) can be used in place of the load balancer to provide highly available 
virtual IP addresses for the web tier. When using Clusterware, only one web server is active at any given 
time (whichever is currently hosting the virtual IP for the web service), but all application servers are still 
active in the AJP load balancing configuration. 

Scope 
The scope of this paper includes the web and application tiers of the above architecture. It does not cover 
the configuration of the load balancer, Oracle Clusterware or Oracle RAC. 

Software Versions 
The following software components comprise the Oracle APEX technology stack described in this paper 
at the time of writing. 

 Oracle Enterprise Linux 7 64‐bit 
 Apache 2.4.6 (httpd‐2.4.6‐67.0.1.el7_4.6.x86_64, provided with the OS rpm repository) 
 Apache Tomcat 8.5.43 
 Oracle REST Data Services (ORDS) 19.2 
 Oracle APEX 19.1 
 Oracle RDBMS 11.2.0.4 

Conventions 
The following standard directory locations are referenced as software distribution installer locations or 
installed  software  locations  in  this  document.  Actual  directory  locations  may  be  different  in  your 
environment. 

Software  Installer Location  Installed Location 

Oracle Application Express  /stage/oracle/apex/latest   
Oracle REST Data Services  /stage/oracle/ords  /opt/apps/ords/latest 
Tomcat  /stage/apache/tomcat  /opt/apps/tomcat/latest 
 

The following text conventions are used in this document: 

 All expressions to be filled in by you are enclosed in square brackets and italicized.  
E.g. [PASSWORD], [HOSTNAME], [TOMCAT_HOME], [ORACLE_HOME], etc. 

Be sure to copy all example commands to Notepad before use to avoid accidentally copying Microsoft 
Word characters. 

   

2 
 
HA Architecture for Oracle ORDS and APEX  

Operating System 
Install  Oracle  Enterprise  Linux  7.6.  DISA  STIG‐compliant  configuration  and  lockdown  of  the  operating 
system is not covered in this guide. 

For the purposes of this installation guide, the following servers are defined in all /etc/hosts files: 
192.168.1.11 apps.mydomain.com # (logical IP /DNS name used by load balancer)
192.168.1.21 webserver1.mydomain.com webserver1
192.168.1.22 webserver2.mydomain.com webserver2
192.168.1.23 appserver1.mydomain.com appserver1
192.168.1.24 appserver2.mydomain.com appserver2
192.168.1.25 rac.mydomain.com # (RAC SCAN IP Address)
192.168.1.26 rac.mydomain.com # (RAC SCAN IP Address)
192.168.1.27 rac.mydomain.com # (RAC SCAN IP Address)

The remaining steps of this guide will be presented in order of dependency. 

Oracle Application Express (APEX) 
Following  the  instructions  provided  with  the  specific  APEX  release  or  patch,  install  or  upgrade  Oracle 
Application Express to the desired version in your database. 

If installing for the first time, the steps should look something like this: 

1. On the database server (or any one cluster node for Oracle RAC) as the “oracle” user, configure 
your environment to connect to the desired database instance and perform the following steps 
with SQL*Plus to create required tablespaces and complete the initial installation: 
 
> cd /stage/oracle/apex/latest
> sqlplus /nolog
SQL> connect / as sysdba
SQL> create tablespace apx_data datafile size 10G;
SQL> create tablespace apx_files datafile size 10G;
SQL> @apexins.sql APX_DATA APX_FILES TEMP /i/
 
2. If installing APEX for the first time, unlock the APEX_PUBLIC_USER account and set a password. 
 
SQL> alter user apex_public_user account unlock;
SQL> alter user apex_public_user identified by [PASSWORD];
 
3. Once the initial APEX installation is complete, configure ORDS services within APEX. Be prepared 
to set passwords for the APEX_LISTENER and APEX_REST_PUBLIC_USER accounts. 
 
SQL> @apex_rest_config.sql
Enter a password for the APEX_LISTENER user []
Enter a password for the APEX_REST_PUBLIC_USER user [] 

3 
 
HA Architecture for Oracle ORDS and APEX  

Apache Tomcat Services 
1. As the apache user on each application server, install the Tomcat software in a local directory of 
your choosing. 
 
> cd /opt/apps/tomcat
> tar xvf /stage/apache/tomcat/apache-tomcat-8.5.37.tar
> ln –s apache-tomcat-8.5.37/ latest 
 
2. Configure the /opt/apps/tomcat/latest/conf/server.xml file on each application server as follows. 
Be sure to set the jvmRoute parameter in the <Engine> tag to the correct value for each Tomcat 
cluster node. 
<?xml version="1.0" encoding="UTF-8"?>
<Server port="-1" shutdown="SHUTDOWN">
<Listener className="org.apache.catalina.startup.VersionLoggerListener" />
<Listener className="org.apache.catalina.security.SecurityListener" />

<!--APR library loader. Documentation at /docs/apr.html -->

<Listener className="org.apache.catalina.core.AprLifecycleListener" SSLEngine="on" />

<!-- Prevent memory leaks due to use of particular java/javax APIs-->

<Listener className="org.apache.catalina.core.JreMemoryLeakPreventionListener" />
<Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" />
<Listener className="org.apache.catalina.core.ThreadLocalLeakPreventionListener" />

<GlobalNamingResources>
<Resource name="UserDatabase" auth="Container"
type="org.apache.catalina.UserDatabase"
description="User database that can be updated and saved"
factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
pathname="conf/tomcat-users.xml" />
</GlobalNamingResources>

<Service name="Catalina">

<!-- Define an AJP 1.3 Connector on port 8009 -->

<Connector port="8009" protocol="AJP/1.3" redirectPort="8443" />

<!--
Each node within the cluster is uniquely identified by the jvmRoute parameter.
The following jvmRoute settings should be used for each listed server:

App Servers: appserver1: server1

appserver2: server2
-->

<Engine name="Catalina" defaultHost="localhost" jvmRoute="server1">

<Cluster className="org.apache.catalina.ha.tcp.SimpleTcpCluster"
channelSendOptions="6">

<Manager className="org.apache.catalina.ha.session.BackupManager"
expireSessionsOnShutdown="false"
notifyListenersOnReplication="true"
mapSendOptions="6"/>

<Channel className="org.apache.catalina.tribes.group.GroupChannel">

4 
 
HA Architecture for Oracle ORDS and APEX  

<!--
Individual Tomcat clusters must use different broadcast addresses
to avoid overlap. The following addresses have been defnied for use
within the various EBS environments:

Integraion Lab: 228.0.0.5

-->

<Membership className="org.apache.catalina.tribes.membership.McastService"
address="228.0.0.5"
port="45564"
frequency="500"
dropTime="3000"/>
<Receiver className="org.apache.catalina.tribes.transport.nio.NioReceiver"
address="auto"
port="5000"
selectorTimeout="100"
maxThreads="6"/>

<Sender
className="org.apache.catalina.tribes.transport.ReplicationTransmitter">
<Transport
className="org.apache.catalina.tribes.transport.nio.PooledParallelSender"/>
</Sender>
<Interceptor
className="org.apache.catalina.tribes.group.interceptors.TcpFailureDetector"/>
<Interceptor
className="org.apache.catalina.tribes.group.interceptors.MessageDispatchInterceptor"/>
<Interceptor
className="org.apache.catalina.tribes.group.interceptors.ThroughputInterceptor"/>
</Channel>

<ClusterListener
className="org.apache.catalina.ha.session.ClusterSessionListener"/>
</Cluster>

<Realm className="org.apache.catalina.realm.LockOutRealm">
<Realm className="org.apache.catalina.realm.UserDatabaseRealm"
resourceName="UserDatabase"/>
</Realm>

<Host name="localhost" appBase="webapps"

unpackWARs="true" autoDeploy="true">

<Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs"

prefix="localhost_access_log" suffix=".txt"
pattern="%h %l %u %t &quot;%r&quot; %s %b" />

</Host>
</Engine>
</Service>
</Server>

3. As the root user on each application server, create a startup script named tomcat8 in the 
/etc/rc.d/init.d directory: 
#!/bin/bash
# processname: tomcat8
# description: This script takes care of starting and stopping Tomcat
# created by: Chris C.
# date: 06.06.2015

5 
 
HA Architecture for Oracle ORDS and APEX  

TOMCAT_USER=apache
TOMCAT_HOME="/opt/apps/tomcat/latest"
SHUTDOWN_WAIT=60

tomcat_pid() {
echo `ps aux | grep org.apache.catalina.startup.Bootstrap | grep -v grep | awk '{
print $2 }'`
}

start() {
pid=$(tomcat_pid)
if [ -n "$pid" ]
then
echo "Tomcat is already running (pid: $pid)"
else
# Start tomcat
echo "Starting Tomcat8"
/bin/su - -c "cd $TOMCAT_HOME/bin && $TOMCAT_HOME/bin/startup.sh" $TOMCAT_USER
fi
return 0
}

stop() {
pid=$(tomcat_pid)
if [ -n "$pid" ]
then
echo "Stoping Tomcat8"
/bin/su - -c "cd $TOMCAT_HOME/bin && $TOMCAT_HOME/bin/shutdown.sh" $TOMCAT_USER

let kwait=$SHUTDOWN_WAIT
count=0
count_by=5
until [ `ps -p $pid | grep -c $pid` = '0' ] || [ $count -gt $kwait ]
do
echo "Waiting for processes to exit. Timeout before we kill the pid:
${count}/${kwait}"
sleep $count_by
let count=$count+$count_by;
done

if [ $count -gt $kwait ]; then

echo "Killing processes which didn't stop after $SHUTDOWN_WAIT seconds"
kill -9 $pid
fi
else
echo "Tomcat is not running"
fi

return 0
}

case $1 in
start)
start
;;
stop)
stop
;;
restart)
stop
start

6 
 
HA Architecture for Oracle ORDS and APEX  

;;
status)
pid=$(tomcat_pid)
if [ -n "$pid" ]
then
echo "Tomcat8 is running with pid: $pid"
else
echo "Tomcat8 is not running"
fi
;;
*)
echo $"Usage: $0 {start|stop|restart|status}"
;;
esac
exit 0

4. Set the file permissions for the startup script and enable auto start with the OS: 
 
> chmod 750 /etc/rc.d/init.d/tomcat8
> systemctl enable tomcat8

/usr/lib/systemd/system/bipub.service (644) 

[Unit]
Documentation=man:systemd-sysv-generator(8)
SourcePath=/etc/rc.d/init.d/bipub
Description=SYSV: This script starts and stops Oracle BI Publisher services
Before=runlevel2.target
Before=runlevel3.target
Before=runlevel4.target
Before=runlevel5.target
Before=shutdown.target
After=network-online.target
After=network.service
After=vmware-tools.service

[Service]
Type=forking
Restart=no
TimeoutSec=15min
IgnoreSIGPIPE=no
KillMode=process
GuessMainPID=no
RemainAfterExit=yes
ExecStart=/etc/rc.d/init.d/bipub start
ExecStop=/etc/rc.d/init.d/bipub stop

cd /etc/systemd/system/multi-user.target.wants
ln -s /usr/lib/systemd/system/bipub.service

systemctl daemon-reload
systemctl enable bipub  

Oracle REST Data Services (ORDS) 
1. As the root user on each application server, create the directory for APEX‐related images in each 
Tomcat home directory and copy the image files from the apex installer location: 
 

7 
 
HA Architecture for Oracle ORDS and APEX  

> cd /opt/apps/tomcat/latest/webapps
> mkdir ./i
> chown apache:apache ./i
> cp -R /stage/oracle/apex/latest/images/* ./i
> chown –R apache:apache ./i
> chmod –R 750 ./i
 
2. As the root user on each application server, unzip the ORDS distribution file, rename the 
ords.war file, and create the configuration directory: 
 
> mkdir /opt/apps/ords/ords_3.0.11
> cd /opt/apps/ords
> ln -s /opt/apps/ords/ords_3.0.11 latest
> cd latest
> unzip /stage/oracle/ords/ords.3.0.11.180.12.34.zip
> chown –R root:apache ./config
> chmod –R 770 ./config/

 
3. As the root user, run the ORDS configuration on the first application server: 
> java -jar ords.war install advanced
Enter the name of the database server [localhost]:rac.mydomain.com
Enter the database listen port [1521]:1521
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name:apex.rac.mydomain.com
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this
step [1]:1
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:

Please login with SYSDBA privileges to verify Oracle REST Data Services schema.
Installation may be required.

Enter the username with SYSDBA privileges to verify the installation [SYS]:
Enter the database password for SYS:
Confirm password:
Nov 19, 2015 12:58:45 PM oracle.dbtools.rt.config.setup.SchemaSetup addSchemaParams
INFO:
Oracle REST Data Services schema does not exist and will be created.

Enter the default tablespace for ORDS_METADATA [SYSAUX]:

Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1
[1]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users
(APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Nov 19, 2017 1:01:17 PM oracle.dbtools.common.config.file.ConfigurationFilesBase update

8 
 
HA Architecture for Oracle ORDS and APEX  

INFO: Updated configurations: defaults, apex, apex_pu, apex_al, apex_rt

Nov 19, 2017 1:01:17 PM oracle.dbtools.installer.Installer installORDS
INFO:
Installing Oracle REST Data Services version 3.0.11.180.12.34
... Log file written to /opt/apps/ords/ords-3.0.11/logs/ordsinstall_2017-11-
19_130117_00145.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
Nov 19, 2017 1:01:34 PM oracle.dbtools.installer.Installer installORDS
INFO: Completed installation for Oracle REST Data Services version 3.0.11.180.12.34.
Elapsed time: 00:00:17.727

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2

4. As the root user, run the ORDS configuration on all remaining application servers: 
# java -jar ords.war install advanced
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data:/opt/apps/ords/latest/config

Enter the name of the database server [localhost]:rac.mydomain.com
Enter the database listen port [1521]:1521
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name:apps.rac.mydomain.com
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this
step [1]:1
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1
[1]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users
(APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:
Nov 19, 2017 1:06:37 PM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults, apex, apex_pu, apex_al, apex_rt
Nov 19, 2017 1:06:37 PM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 3.0.11.180.12.34
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2

5. As the root user, deploy the ords.war file to Tomcat on each application server: 
> systemctl stop tomcat8
> cd /opt/apps/ords/latest
> chown –R root:apache ./config
> chmod –R 770 ./config/
> cd /opt/apps/tomcat/latest
> cp /opt/apps/ords/latest/ords.war .
> chown apache:apache ords.war
> chmod 755 ords.war
> systemctl start tomcat8

9 
 
HA Architecture for Oracle ORDS and APEX  

Optional ORDS Configuration 
A single Oracle REST Data Services deployment can be configured to support multiple APEX installations 
on  different  databases.  The  following  steps  describe  how  to  configure  ORDS  to  support  such  a 
configuration. 

1. As the root user on each application server, configure the connection info for each database / 
APEX instance to be serviced by ORDS: 
> cd /opt/apps/ords/latest
> java -jar ords.war setup --database [DB_ALIAS]

Example: 
# root> java -jar ords.war setup --database proto
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts
Enter the location to store configuration data:/opt/apps/ords/latest/config
Enter the name of the database server [localhost]:rac.mydomain.com
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name:apps.rac-proto.mydomain.com
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this
step [1]:1
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.
Installation may be required.

Enter the username with SYSDBA privileges to verify the installation [SYS]:
Enter the database password for csehct as sysdba:
Confirm password:
Mar 22, 2016 10:59:03 AM oracle.dbtools.rt.config.setup.SchemaSetup addSchemaParams
INFO:
Oracle REST Data Services schema does not exist and will be created.

Enter the default tablespace for ORDS_METADATA [SYSAUX]:APX_DATA

Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:APX_DATA
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1
[1]:
Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users
(APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:
Enter the database password for APEX_LISTENER:
Confirm password:
Enter the database password for APEX_REST_PUBLIC_USER:
Confirm password:

2. After being configured, a new set of xml credential files should appear in the 
/opt/apps/ords/latest/config/ebs/conf directory for each additional database: 
> ls /opt/apps/ords/latest/config/ords/conf
build_al.xml build_rt.xml int_al.xml int_rt.xml proto_al.xml proto_rt.xml

10 
 
HA Architecture for Oracle ORDS and APEX  

build_pu.xml build.xml int_pu.xml int.xml proto_pu.xml proto.xml

3. Once credentials are created, a URL for the new database must be mapped so that ORDS can 
match user requests with the desired APEX instance. 
 
> cd /opt/apps/ords/latest
> java -jar ords.war map-url --type base-path --workspace-id [WORKSPACE_NAME]
[PATH_PREFIX] [DATABASE_NAME]
 
For new installs where no workspace yet exists, or for installations where users can be directed 
to a general login screen to select a workspace, a specific workspace‐id is not required. 
Workspace‐id can be added to the configuration at a later time. 
 
Example: 
 
> java -jar ords.war map-url --type base-path /proto proto
 
4. When the URL mapping is complete, the /opt/apps/ords/latest/config/ords/url‐mapping.xml file 
should contain entries something like this: 
<?xml version="1.0" encoding="UTF-8"?>
<pool-config xmlns="http://xmlns.oracle.com/apex/pool-config">
<pool name="proto" base-path="/proto" updated="2016-03-22T17:03:29.996Z"/>
<pool name="build" base-path="/build" updated="2016-03-22T20:17:39.749Z"/>
<pool name="int" base-path="/int" updated="2016-03-24T15:32:29.717Z"/>
</pool-config>

Apache HTTPD Services 
1. As root, install Apache HTTPD on each web server as follows: 

> yum install httpd mod_ssl

 
2. Configure the /etc/httpd/conf/httpd.conf file on each web server as follows. Be sure to adjust 
the server name or IP address of the server to be the correct local values in each file: 
#
# This is the main Apache HTTP server configuration file. It contains the
# configuration directives that give the server its instructions.
# See <URL:http://httpd.apache.org/docs/2.4/> for detailed information.
# In particular, see
# <URL:http://httpd.apache.org/docs/2.4/mod/directives.html>
# for a discussion of each configuration directive.
#
# Do NOT simply read the instructions in here without understanding
# what they do. They're here only as hints or reminders. If you are unsure
# consult the online docs. You have been warned.
#
# Configuration and logfile names: If the filenames you specify for many
# of the server's control files begin with "/" (or "drive:/" for Win32), the
# server will use that explicit path. If the filenames do *not* begin
# with "/", the value of ServerRoot is prepended -- so 'log/access_log'
# with ServerRoot set to '/www' will be interpreted by the

11 
 
HA Architecture for Oracle ORDS and APEX  

# server as '/www/log/access_log', where as '/log/access_log' will be

# interpreted as '/log/access_log'.

#
# ServerRoot: The top of the directory tree under which the server's
# configuration, error, and log files are kept.
#
# Do not add a slash at the end of the directory path. If you point
# ServerRoot at a non-local disk, be sure to specify a local disk on the
# Mutex directive, if file-based mutexes are used. If you wish to share the
# same ServerRoot for multiple httpd daemons, you will need to change at
# least PidFile.
#
ServerRoot "/etc/httpd"

#
# Listen: Allows you to bind Apache to specific IP addresses and/or
# ports, instead of the default. See also the <VirtualHost>
# directive.
#
# Change this to Listen on specific IP addresses as shown below to
# prevent Apache from glomming onto all bound IP addresses.
#
#Listen 12.34.56.78:80
#Listen 80

#
# Dynamic Shared Object (DSO) Support
#
# To be able to use the functionality of a module which was built as a DSO you
# have to place corresponding `LoadModule' lines at this location so the
# directives contained in it are actually available _before_ they are used.
# Statically compiled modules (those listed by `httpd -l') do not need
# to be loaded here.
#
# Example:
# LoadModule foo_module modules/mod_foo.so
#
Include conf.modules.d/*.conf

#
# If you wish httpd to run as a different user or group, you must run
# httpd as root initially and it will switch.
#
# User/Group: The name (or #number) of the user/group to run httpd as.
# It is usually good practice to create a dedicated user and group for
# running httpd, as with most system services.
#
User apache
Group apache

# 'Main' server configuration

#
# The directives in this section set up the values used by the 'main'
# server, which responds to any requests that aren't handled by a
# <VirtualHost> definition. These values also provide defaults for
# any <VirtualHost> containers you may define later in the file.
#
# All of these directives may appear inside <VirtualHost> containers,
# in which case these default settings will be overridden for the
# virtual host being defined.
#

12 
 
HA Architecture for Oracle ORDS and APEX  

#
# ServerAdmin: Your address, where problems with the server should be
# e-mailed. This address appears on some server-generated pages, such
# as error documents. e.g. admin@your-domain.com
#
ServerAdmin root@localhost

#
# ServerName gives the name and port that the server uses to identify itself.
# This can often be determined automatically, but we recommend you specify
# it explicitly to prevent problems during startup.
#
# If your host doesn't have a registered DNS name, enter its IP address here.
#
ServerName webserver1

# STIG Lockdown Settings

Timeout 28800
KeepAlive On
StartServers 10
MinSpareServers 5
MaxSpareServers 10
LimitRequestBody 1000000
LimitRequestBody 0
LimitRequestFields 100
LimitRequestFieldSize 8190
LimitRequestLine 8190
TraceEnable off
ServerTokens Prod
MaxKeepAliveRequests 100

#
# Deny access to the entirety of your server's filesystem. You must
# explicitly permit access to web content directories in other
# <Directory> blocks below.
#
<Directory />
AllowOverride none
Require all denied
</Directory>

#
# Note that from this point forward you must specifically allow
# particular features to be enabled - so if something's not working as
# you might expect, make sure that you have specifically enabled it
# below.
#

#
# DocumentRoot: The directory out of which you will serve your
# documents. By default, all requests are taken from this directory, but
# symbolic links and aliases may be used to point to other locations.
#
DocumentRoot "/var/www/html"

#
# Relax access to content within /var/www.
#
<Directory "/var/www">
AllowOverride None
# Allow open access:

13 
 
HA Architecture for Oracle ORDS and APEX  

Require all granted

</Directory>

# Further relax access to the default document root:

<Directory "/var/www/html">
#
# Possible values for the Options directive are "None", "All",
# or any combination of:
# Indexes Includes FollowSymLinks SymLinksifOwnerMatch ExecCGI MultiViews
#
# Note that "MultiViews" must be named *explicitly* --- "Options All"
# doesn't give it to you.
#
# The Options directive is both complicated and important. Please see
# http://httpd.apache.org/docs/2.4/mod/core.html#options
# for more information.
#
Options Indexes FollowSymLinks

#
# AllowOverride controls what directives may be placed in .htaccess files.
# It can be "All", "None", or any combination of the keywords:
# Options FileInfo AuthConfig Limit
#
AllowOverride None

#
# Controls who can get stuff from this server.
#
Require all granted
</Directory>

#
# DirectoryIndex: sets the file that Apache will serve if a directory
# is requested.
#
<IfModule dir_module>
DirectoryIndex index.html
</IfModule>

#
# The following lines prevent .htaccess and .htpasswd files from being
# viewed by Web clients.
#
<Files ".ht*">
Require all denied
</Files>

#
# ErrorLog: The location of the error log file.
# If you do not specify an ErrorLog directive within a <VirtualHost>
# container, error messages relating to that virtual host will be
# logged here. If you *do* define an error logfile for a <VirtualHost>
# container, that host's errors will be logged there and not here.
#
ErrorLog "logs/error_log"

#
# LogLevel: Control the number of messages logged to the error_log.
# Possible values include: debug, info, notice, warn, error, crit,
# alert, emerg.
#

14 
 
HA Architecture for Oracle ORDS and APEX  

LogLevel warn

<IfModule log_config_module>
#
# The following directives define some format nicknames for use with
# a CustomLog directive (see below).
#
LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %b" common

<IfModule logio_module>
# You need to enable mod_logio.c to use %I and %O
LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\" %I %O"
combinedio
</IfModule>

#
# The location and format of the access logfile (Common Logfile Format).
# If you do not define any access logfiles within a <VirtualHost>
# container, they will be logged here. Contrariwise, if you *do*
# define per-<VirtualHost> access logfiles, transactions will be
# logged therein and *not* in this file.
#
#CustomLog "logs/access_log" common

#
# If you prefer a logfile with access, agent, and referer information
# (Combined Logfile Format) you can use the following directive.
#
CustomLog "logs/access_log" combined
</IfModule>

<IfModule alias_module>
#
# Redirect: Allows you to tell clients about documents that used to
# exist in your server's namespace, but do not anymore. The client
# will make a new request for the document at its new location.
# Example:
# Redirect permanent /foo http://www.example.com/bar

#
# Alias: Maps web paths into filesystem paths and is used to
# access content that does not live under the DocumentRoot.
# Example:
# Alias /webpath /full/filesystem/path
#
# If you include a trailing / on /webpath then the server will
# require it to be present in the URL. You will also likely
# need to provide a <Directory> section to allow access to
# the filesystem path.

#
# ScriptAlias: This controls which directories contain server scripts.
# ScriptAliases are essentially the same as Aliases, except that
# documents in the target directory are treated as applications and
# run by the server when requested rather than as documents sent to the
# client. The same rules about trailing "/" apply to ScriptAlias
# directives as to Alias.
#
ScriptAlias /cgi-bin/ "/var/www/cgi-bin/"

</IfModule>

15 
 
HA Architecture for Oracle ORDS and APEX  

#
# "/var/www/cgi-bin" should be changed to whatever your ScriptAliased
# CGI directory exists, if you have that configured.
#
<Directory "/var/www/cgi-bin">
AllowOverride None
Options None
Require all granted
</Directory>

<IfModule mime_module>
#
# TypesConfig points to the file containing the list of mappings from
# filename extension to MIME-type.
#
TypesConfig /etc/mime.types

#
# AddType allows you to add to or override the MIME configuration
# file specified in TypesConfig for specific file types.
#
#AddType application/x-gzip .tgz
#
# AddEncoding allows you to have certain browsers uncompress
# information on the fly. Note: Not all browsers support this.
#
#AddEncoding x-compress .Z
#AddEncoding x-gzip .gz .tgz
#
# If the AddEncoding directives above are commented-out, then you
# probably should define those extensions to indicate media types:
#
AddType application/x-compress .Z
AddType application/x-gzip .gz .tgz

#
# AddHandler allows you to map certain file extensions to "handlers":
# actions unrelated to filetype. These can be either built into the server
# or added with the Action directive (see below)
#
# To use CGI scripts outside of ScriptAliased directories:
# (You will also need to add "ExecCGI" to the "Options" directive.)
#
#AddHandler cgi-script .cgi

# For type maps (negotiated resources):

#AddHandler type-map var

#
# Filters allow you to process content before it is sent to the client.
#
# To parse .shtml files for server-side includes (SSI):
# (You will also need to add "Includes" to the "Options" directive.)
#
AddType text/html .shtml
AddOutputFilter INCLUDES .shtml
</IfModule>

#
# Specify a default charset for all content served; this enables
# interpretation of all content as UTF-8 by default. To use the

16 
 
HA Architecture for Oracle ORDS and APEX  

# default browser choice (ISO-8859-1), or to allow the META tags

# in HTML content to override this choice, comment out this
# directive:
#
AddDefaultCharset UTF-8

<IfModule mime_magic_module>
#
# The mod_mime_magic module allows the server to use various hints from the
# contents of the file itself to determine its type. The MIMEMagicFile
# directive tells the module where the hint definitions are located.
#
MIMEMagicFile conf/magic
</IfModule>

#
# EnableMMAP and EnableSendfile: On systems that support it,
# memory-mapping or the sendfile syscall may be used to deliver
# files. This usually improves server performance, but must
# be turned off when serving from networked-mounted
# filesystems or if support for these functions is otherwise
# broken on your system.
# Defaults if commented: EnableMMAP On, EnableSendfile Off
#
#EnableMMAP off
EnableSendfile on

# Supplemental configuration
#
# Load config files in the "/etc/httpd/conf.d" directory, if any.
IncludeOptional conf.d/*.conf

3. Configure the /etc/httpd/conf.d/ssl.conf file as follows. Be sure to adjust the server name or IP 
address of the server to be the correct local values in each file: 
#
# When we also provide SSL we have to listen to the
# the HTTPS port in addition.
#
Listen 443 https

##
## SSL Global Context
##
## All SSL configuration in this context applies both to
## the main server and all SSL-enabled virtual hosts.
##

SSLProtocol +TLSv1

SSLCipherSuite TLSv1:AES:SHA1:!DES:!MD5:!DH:!MEDIUM:!LOW:!SSLv2:!NULL

SSLUseStapling On
SSLStaplingForceURL http://ocsp.mydomain.com
SSLStaplingCache "shmcb:/run/httpd/ssl_stapling(1048576)"

# Pass Phrase Dialog:

# Configure the pass phrase gathering process.
# The filtering dialog program (`builtin' is a internal
# terminal dialog) has to provide the pass phrase on stdout.
SSLPassPhraseDialog exec:/usr/libexec/httpd-ssl-pass-dialog

17 
 
HA Architecture for Oracle ORDS and APEX  

# Inter-Process Session Cache:

# Configure the SSL Session Cache: First the mechanism
# to use and second the expiring timeout (in seconds).
SSLSessionCache shmcb:/run/httpd/sslcache(512000)
SSLSessionCacheTimeout 28800

# Pseudo Random Number Generator (PRNG):

# Configure one or more sources to seed the PRNG of the
# SSL library. The seed data should be of good random quality.
# WARNING! On some platforms /dev/random blocks if not enough entropy
# is available. This means you then cannot use the /dev/random device
# because it would lead to very long connection times (as long as
# it requires to make more entropy available). But usually those
# platforms additionally provide a /dev/urandom device which doesn't
# block. So, if available, use this one instead. Read the mod_ssl User
# Manual for more details.
SSLRandomSeed startup file:/dev/urandom 1024
SSLRandomSeed connect file:/dev/urandom 1024

#
# Use "SSLCryptoDevice" to enable any supported hardware
# accelerators. Use "openssl engine -v" to list supported
# engine names. NOTE: If you enable an accelerator and the
# server does not start, consult the error logs and ensure
# your accelerator is functioning properly.
#
SSLCryptoDevice builtin

##
## SSL Virtual Host Context
##

#######################################################
#
# Default Virtual Host
#
#######################################################
<VirtualHost 192.168.1.101:443>

# General setup for the virtual host

DocumentRoot "/var/www/html"
ServerName 192.168.1.101
ServerAdmin root@localhost
ErrorLog "/etc/httpd/logs/default.ssl_error_log"

SSLEngine on

SSLCertificateFile "/etc/pki/tls/certs/server_cert.pem"
SSLCertificateKeyFile "/etc/pki/tls/private/server_key.pem"
SSLCACertificateFile "/etc/pki/tls/certs/server_cas.pem"

SSLVerifyClient none
SSLVerifyDepth 10

SSLOptions +StrictRequire

BrowserMatch "MSIE [2-5]" \

nokeepalive ssl-unclean-shutdown \
downgrade-1.0 force-response-1.0

RequestHeader set SSL_CLIENT_S_DN_CN ""

18 
 
HA Architecture for Oracle ORDS and APEX  

RequestHeader set SSL_CLIENT_S_DN_CN "%{SSL_CLIENT_S_DN_CN}s"

SSLUserName SSL_CLIENT_S_DN_CN

CustomLog "/etc/httpd/logs/default.ssl_request_log" \
"%t %h %{SSL_CLIENT_S_DN_CN}s %u %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %b %D"

</VirtualHost>

#######################################################
#
# APEX Virtual Host
#
#######################################################
<VirtualHost 192.168.1.101:443>

# General setup for the virtual host

DocumentRoot "/var/www/html"
ServerName apps.mydomain.com
ServerAdmin root@localhost
ErrorLog "/etc/httpd/logs/apex.ssl_error.log"
RedirectMatch ^/$ /ords/f?p=200

SSLEngine on

SSLCertificateFile "/etc/pki/tls/certs/server_cert.pem"
SSLCertificateKeyFile "/etc/pki/tls/private/server_key.pem"
SSLCACertificateFile "/etc/pki/tls/certs/server_cas.pem"

SSLVerifyClient require
SSLVerifyDepth 10

SSLOptions +StrictRequire

BrowserMatch "MSIE [2-5]" \

nokeepalive ssl-unclean-shutdown \
downgrade-1.0 force-response-1.0

CustomLog "/etc/httpd/logs/apex.ssl_request_log" \
"%t %h %{SSL_CLIENT_S_DN_CN}s %u %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %b %D"

<IfModule mod_proxy_ajp.c>

ProxyRequests Off
ProxyPreserveHost On

# Initialize the header variables that contain user id information

RequestHeader set SSL_CLIENT_S_DN_CN ""
RequestHeader set SSL_CLIENT_SAN_OTHER_MSUPN ""

# The CAC Certificate Common Name is available as an SSL environment variable

RequestHeader set SSL_CLIENT_S_DN_CN "%{SSL_CLIENT_S_DN_CN}s"

# In Apache 2.4.17 and later, the User Principal Name is also available as an
# SSL environment variable
#
#RequestHeader set SSL_CLIENT_SAN_OTHER_MSUPN "%{SSL_CLIENT_SAN_OTHER_msUPN}s"
#
# In earlier versions of Apache 2.4, the User Principal Name can be derived
# from the CAC Certificate Common Name
RequestHeader set SSL_CLIENT_SAN_OTHER_MSUPN "%{SSL_CLIENT_S_DN_CN}s"
RequestHeader edit* SSL_CLIENT_SAN_OTHER_MSUPN "\D" ""

19 
 
HA Architecture for Oracle ORDS and APEX  

RequestHeader edit SSL_CLIENT_SAN_OTHER_MSUPN $ "@mil"

# Set the UserName for logging purposes

SSLUserName SSL_CLIENT_S_DN_CN

<Proxy balancer://ords_balancer>
BalancerMember ajp://appserver1:8009 route=server1
BalancerMember ajp://appserver2:8009 route=server2
</Proxy>

ProxyPass /ords balancer://ords_balancer/ebs stickysession=JSESSIONID|jsessionid

ProxyPassReverse /ords balancer://ords_balancer/ords
ProxyPassReverseCookiePath /ords /

ProxyPass /i balancer://ords_balancer/i stickysession=JSESSIONID|jsessionid

ProxyPassReverse /i balancer://ords_balancer/i

<Proxy *>
Order deny,allow
Deny from none
Allow from localhost
</Proxy>

<Location /balancer-manager>
SetHandler balancer-manager
</Location>
</IfModule>
</VirtualHost>

4. Enable auto start of the httpd service: 

> systemctl enable httpd 

   

20 
 
HA Architecture for Oracle ORDS and APEX  

APEX Workspace Authentication 
In this architecture, APEX workspace accounts are authenticated through HTTP header variables. Account 
names are based on the Common Name stored on the client PKI certificate. Workspace administrators 
may create, modify, or delete accounts as required, and control membership in workspace user groups. If 
a user has an account within more than one workspace of an APEX instance, then they are presented with 
a list of appropriate workspaces to choose from when they log in. 

To  configure  APEX  workspaces  for  HTTP  header  authentication,  complete  the  following  steps  in  the 
“INTERNAL” workspace as an administrator: 

1. Pre‐create  at  least  one  administrator‐level  user  in  the  INTERNAL  workspace  whose  username 
corresponds to the HTTP header paradigm you intend to use (e.g. “PUBLIC.JOHN.Q.123456789” 
for Common Name or “123456789@mydomain” for User Principal Name) so that you will be able 
to connect to the INTERNAL workspace when the authentication configuration is complete. 

Select “Manage Workspaces” from the main dashboard, then “Manage Developers and Users” 
from the Workspace Actions list. Click the “Create User” button and enter the new username, e‐
mail address for the user. Select “INTERNAL” for the Workspace. Click “Create User”. 

   

 
2. When  the  new  internal  administrator  account  has  been  created,  return  to  the  Instance 
Administration dashboard by clicking on the Oracle Application Express logo in the top left corner.  

3. Now configure the workspace (and by extension all workspaces in the APEX instance) for HTTP 
header  authentication.  Select  “Manage  Instance”  from  the  main  dashboard.  Select  “Security” 
from the Instance Settings menu. Select the “Authentication Control” tab. 

 
 
 
 
4. Select  “HTTP  Header  Variable”  from  the  Development  Environment  Authorization  Schemes 
section. 

21 
 
HA Architecture for Oracle ORDS and APEX  

 
 

5. Enter  the  HTTP  header  variable  name  that  will  contain  the  username  into  the  HTTP  Header 
Variable Name field. Click “Make Current Scheme” and the click “OK” when prompted to confirm 
the change. 

   

Note: Changing the authentication scheme can render APEX inaccessible if parameters on the web 
server are not configured correctly. You can always reset the INTERNAL workspace authentication 
scheme to the original default (username/password) by running the following command as the 
SYS database user: 
SQL> execute apex_instance_admin.set_parameter('APEX_BUILDER_AUTHENTICATION','APEX');

APEX Application Authentication 
In  this  architecture,  individual  APEX  applications  may  now  authenticate  users  through  one  of  two 
methods: 

 HTTP Header Variable: The most common authentication scheme uses one of the two header 
variables  (SSL_CLIENT_S_DN_CN  or  SSL_CLIENT_SAN_OTHER_MSUPN)  to  identify  users.  In  this 
scheme  each  application  maintains  its  own  dedicated  access  control  list  (i.e.  a  USERS  table  of 
some kind) which is used by the various application authorization schemes to determine which 
users  are  allowed  to  perform  given  operations  or  view  certain  content.  These  tables  can  be 
manually  maintained  through  the  application  UI  or  automatically  synchronized  (in  whole  or  in 
part) against an external list of users, like an LDAP directory. 

 No  Authentication:  This  scheme  is  for  applications  that  only  require  a  valid  PKI  certificate  for 
authentication (a check which is handled by externally from the application by the web server) 
and  do  not  restrict  access  to  any  content,  like  the  a  public  Home  page.  Applications  with  this 
scheme will be open to all valid PKI certificate holders. 

Note: Database Authentication is not an option for APEX applications in this architecture, as this requires 
a dedicated database account to be created for each application user. 

An HTTP Header authentication scheme for an application can be defined in the “Shared Components” 
area of the Application Builder, under “Authentication Schemes” in the Security section. It should look like 
this: 

22 
 
HA Architecture for Oracle ORDS and APEX  

The  value  of  the  header  variable  will  be  passed  in  to  the  application  item  :APP_USER  for  use  within 
authorization schemes or other application objects. 

   

23 
 
HA Architecture for Oracle ORDS and APEX  

APEX Application Authorization 
An example of a very basic authorization (access control) configuration for an APEX database application 
is provided here. The user table, in combination with a simple authorization scheme. 

The  sample  APP_USERS  table  below  contains  user  account  information  specific  to  a  generic  APEX 
application.  This  simple  example  includes  limited  account  info,  such  as  the  USER_NAME  and  basic 
USER_PRIVILEGE (e.g. read, write, admin) information. Its fields are defined as follows: 

 USER_ID: a unique system‐generated number to identify every user/privilege relationship 
 
 USER_NAME: a unique username for the user, based on either CAC CN or User Principal Name 
as received from the HTTP header. 
 
 USER_PRIVILEGE: a number representing the basic privilege of the user within the application. 
For this example 1=User, 2=Power User, and 3=Administrator. 
 

-- Table APP_USERS

CREATE TABLE "APP_USERS"(

"USER_ID" Number NOT NULL,
"USER_NAME" Varchar2(1024 ) NOT NULL,
"USER_PRIVILEGE" Number NOT NULL,
"CREATED_DATE" Date,
"CREATED_BY" Varchar2(100 ),
"UPDATED_DATE" Date,
"UPDATED_BY" Varchar2(100 )
)
/

-- Create indexes for table APP_USERS

CREATE UNIQUE INDEX "APP_USERS_PK" ON "APP_USERS" ("USER_ID")

/

CREATE UNIQUE INDEX "APP_USERS_UK1" ON "APP_USERS" ("USER_NAME","USER_PRIVILEGE")

/

-- Add keys for table APP_USERS

ALTER TABLE "APP_USERS" ADD CONSTRAINT "APP_USERS_PK" PRIMARY KEY ("USER_ID")

/

-- Create triggers for table APP_USERS

CREATE TRIGGER "BI_APP_USERS"

BEFORE INSERT OR UPDATE
ON "APP_USERS"
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
if inserting
then
:new.priv_id :=
to_number (sys_guid (), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
:new.created_by :=
nvl (sys_context ('USERENV', 'CLIENT_IDENTIFIER'), user);
:new.created_date := sysdate;
end if;

24 
 
HA Architecture for Oracle ORDS and APEX  

if updating
then
:new.updated_by :=
nvl (sys_context ('USERENV', 'CLIENT_IDENTIFIER'), user);
:new.updated_date := sysdate;
end if;
END
/

 
User authorization scheme functions would be built using PL/SQL packages as follows: 

CREATE OR REPLACE package user_auth_pkg

as
/* Determine if the user account is locked in AD */
function is_locked (p_username in varchar2)
return boolean;

/* Determine if user is a registered user */

function is_user (p_username in varchar2)
return boolean;

/* Determine if user is a power user */

function is_power (p_username in varchar2)
return boolean;

/* Determine if user is an application administrator */

function is_admin (p_username in varchar2)
return boolean;

end user_auth_pkg;
/

CREATE OR REPLACE PACKAGE BODY user_auth_pkg

AS
/* Determine if the user account is locked in Active Directory,
based on value of userPrincipalName in the EBS_USERS table. */
FUNCTION is_locked (p_username IN VARCHAR2)
RETURN BOOLEAN
IS
v_upn VARCHAR2 (1024);
v_result BOOLEAN;
BEGIN
/* get UPN from EBS */
SELECT u.userprincipalname
INTO v_upn
FROM ebs_users a, app_user a
WHERE e.sourceobjectguid = a.ebs_user_id
AND UPPER (a.user_name) = UPPER (p_username);

/* if UPN contains "_sync::" then account is locked */

IF INSTR (v_upn, '_sync::') > 0
THEN
v_result := TRUE;
ELSE
v_result := FALSE;
END IF;

RETURN v_result;
EXCEPTION
/* if no matching EBS record found, show account locked */
WHEN NO_DATA_FOUND
THEN
v_result := TRUE;
RETURN v_result;
END is_locked;

25 
 
HA Architecture for Oracle ORDS and APEX  

/* Determine if user is a registered user */

FUNCTION is_user (p_username IN VARCHAR2)
RETURN BOOLEAN
IS
v_priv NUMBER;
v_result BOOLEAN;
BEGIN
/* Get privilege level for the user */
SELECT user_privilege
INTO v_priv
FROM app_users
WHERE UPPER (user_name) = UPPER (p_username);

/* Check if user has view privs (>=1) or higher, and if account is unlocked */
IF v_priv >= 1 AND is_locked (p_username) = FALSE
THEN
v_result := TRUE;
ELSE
v_result := FALSE;
END IF;

RETURN v_result;
EXCEPTION
/* If no user record found then return false */
WHEN NO_DATA_FOUND
THEN
v_result := FALSE;
RETURN v_result;
END is_user;

/* Determine if user is a Power User */

FUNCTION is_power (p_username IN VARCHAR2)
RETURN BOOLEAN
IS
v_priv NUMBER;
v_result BOOLEAN;
BEGIN
/* Get privilege level for the user */
SELECT user_privilege
INTO v_priv
FROM stig_sec_users_view
WHERE UPPER (user_name) = UPPER (p_username);

/* Check if user has power user privs (>=2) or higher, and if account is unlocked */
IF v_priv >= 2 AND is_locked (p_username) = FALSE
THEN
v_result := TRUE;
ELSE
v_result := FALSE;
END IF;

RETURN v_result;
EXCEPTION
/* If no user record found then return false */
WHEN NO_DATA_FOUND
THEN
v_result := FALSE;
RETURN v_result;
END is_power;

/* Determine if user is an application administrator */

FUNCTION is_admin (p_username IN VARCHAR2)
RETURN BOOLEAN
IS
v_priv NUMBER;
v_result BOOLEAN;
BEGIN
/* Get privilege level for the user */
SELECT NVL (user_privilege, 0)
INTO v_priv
FROM stig_sec_users_view

26 
 
HA Architecture for Oracle ORDS and APEX  

WHERE UPPER (user_name) = UPPER (p_username);

/* Check if user has admin privs (>=3) or higher, and if account is unlocked */
IF v_priv >= 3 AND is_locked (p_username) = FALSE
THEN
v_result := TRUE;
ELSE
v_result := FALSE;
END IF;

RETURN v_result;
EXCEPTION
/* If no user record found then return false */
WHEN NO_DATA_FOUND
THEN
v_result := FALSE;
RETURN v_result;
END is_admin;
END user_auth_pkg;
/

 
The final user level authorization scheme in the APEX app would then look something like this: 

An  authorization  scheme  for  an  application  can  be  defined  in  the  “Shared  Components”  area  of  the 
Application  Builder,  under  “Authorization  Schemes”  in  the  Security  section.  Additional  authorization 
schemes  could  be  created  that  would  call  user_auth_pkg.is_power  or  is_admin  for  Power  Users  or 
Administrators respectively. 

   

27 
 
HA Architecture for Oracle ORDS and APEX  

APEX Application Monitoring 
In order to better monitor and troubleshoot application behavior, it is important to include the following 
application processes in every APEX application. These processes allow database administrators to track 
which portions of an application are consuming significant server resources, and which users are active 
within the application. Application Processes can be created under the Shared Components 

Sequence  1 
Process Point  On Load: Before Header (page template header) 
Name  on_load_set_app_info 
dbms_session.set_identifier(:APP_USER);
PL/SQL Code  dbms_application_info.set_module(:APP_ALIAS,'Load Page '||:APP_PAGE_ID); 
 

Sequence  1 
Process Point  On Submit: After Page Submission – Before Computations and Validations 
Name  on_submit_set_app_info 
PL/SQL Code  dbms_session.set_identifier(:APP_USER);
dbms_application_info.set_module(:APP_ALIAS,'Submit Page '||:APP_PAGE_ID); 
 

Application Processes for an APEX application can be defined in the “Shared Components” area of the 
Application Builder, under “Application Processes” in the Application Logic section. 

28 
 
HA Architecture for Oracle ORDS and APEX  

References 
This document references the following documentation: 

 Oracle Corporation. (2017, October). Oracle Application Express Installation Guide, Retrieved 
March 2018, from Oracle Documentation: https://docs.oracle.com/database/apex‐
5.1/HTMIG/toc.htm   
 Oracle Corporation. (2017, July). Oracle REST Data Services Installation, Configuration, and 
Development Guide, Retrieved March 2018, from Oracle Documentation: 
https://docs.oracle.com/cd/E56351_01/doc.30/e87809/title.htm   
 Oracle Corporation. (2018, January). How to Configure APEX Listener / Oracle REST Data Services 
(ORDS) 2.0 to Serve Multiple APEX Instances (Doc ID 1547603.1), Retrieved March 2018 from My 
Oracle Support: 
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=488172069546566&id=1
547603.1&displayIndex=3&_afrWindowMode=0&_adf.ctrl‐state=4ih03e1n9_126#FIX  

Biography 
Peter Magee is a CompTIA Security+ certified Oracle database administrator with over twenty years of 
professional experience in both commercial and military computing environments. Over the years he has 
published  and  presented  white  papers  and  scripts  on  a  variety  of  performance  tuning  and  security 
subjects.  Highlights  of  Mr.  Magee’s  career  include  contributing  content  and  scripts  to  the  Defense 
Information Systems Agency’s first Database Security Technical Implementation Guide, presentation of 
papers  on  real‐time  auditing  and  monitoring  techniques  using  Oracle  Enterprise  Manager  at  the 
Independent Oracle Users Group (IOUG) Live ’99 Conference, and participation as a network defender in 
the Air Force’s “Black Demon” cyber  defense  exercise. His scripts and award‐winning white  papers on 
security and system monitoring have been published on the Oracle Magazine Interactive Web Site, the 
IOUG’s “Library of Oracle Knowledge”, the Sun Microsystems “Big Admin” portal, the Oracle Communities 
portal, and the CSC Leading Edge Forum. Mr. Magee is currently employed as a Lead System and Database 
Engineer at Stellar Innovations and Solutions, where he leads a  team providing  production operations 
support for a customer’s 12,000 user business management system. Mr. Magee’s blog, “Late Night Oracle 
Blog”, is available at pmdba.wordpress.com. 

29