Oracle Application Express - A DBA Perspective

September 15, 2010

The three main reasons that DBAs do need to be concerned are first, it must be installed into the database, http server access to the database must be enabled for APEX based applications to run and there are several privileges granted to the workspace administrator accounts that the DBA should be aware of. Additionally there are always space requirements for each of the workspaces that the DBA needs to be aware of if managing a database that is going to support applications designed using Oracle Application Express.

Choosing the HTTP Server Access

The first decision that should be made is to determine which kind of HTTP Server Access. There are slightly different detailed installation steps depending on which access method is chosen.

Application Express has three options (if installed into an Oracle 11g database) for configuring the required HTTP Server Access to the database; the Oracle HTTP Server with mod_plsql, the Oracle Application Express Listener or the Embedded PL/SQL Gateway. All of these methods are used to establish communications between the web server and the APEX objects in the database using a SQL*Net connection.

Application Express Listener

This is a java based web server and is a multi tier option that allows for separation of the listener tier from the database tier. This is downloaded separately from Oracle and must be unzipped and then separately installed and configured.

HTTP Server with mod_plsql

Using the Oracle HTTP Server requires the mod_plsql plug-in and the proper configuration of the dads.conf file for the HTTP Server.

The huge advantage of the HTTP Server with mod_plsql is that it allows for separation of the mid tier from the database tier. If your application is going to be placed outside of a firewall (for example, external web based applications) the DBA should always go with the HTTP Server with mod_plsql option rather than using the Oracle Application Express Listener.

Embedded PL/SQL Gateway

This is installed in the database as part of the Oracle XML DB HTTP Server.

However, it should be noted, that there are two distinct disadvantages to using the embedded gateway option. First, there is no way to separate the listener from the database, which would be a security risk for internet based applications. Second, it is not as feature rich, when it comes to configuration and logging, as the Oracle HTTP server with mod_plsql.

Installing Application Express

System Requirements

Before beginning the installation (Application Express 4.0), the following system requirements must be met:

Running the Installation Scripts

Application Express can be downloaded from Oracle ( Application Express Download Site ). There are two versions of Application Express, the full development version and a runtime version. After downloading and staging the software, running the following command (must be run while connected as sysdba) installs the full version:

@apexins <apex_tbs> <apex_files_tbs> <tmp_tbs> /i/ 
<apex_tbs> is the tablespace for the APEX application user
<apex_files_tbs> is the tablespace for the APEX files user
<tmp_tbs> is the temporary tablespace
/i/ the virtual directory for APEX images - using /i/ is recommended for future upgrade support

The following script has the same parameters, but installs the runtime version of Application Express.

@apexrtins <apex_tbs> <apex_files_tbs> <tmp_tbs> /i/

The following three accounts will be created in the database - APEX_040000 (owner of the application express schema and meta data), FLOWS_FILES (owner of any uploaded files by application express) and APEX_PUBLIC_USER (a low privilege account used by the HTTP Server with mod_plsql)

Changing the Application Express Administrator Password

A default ADMIN account is set up to be used when first logging into the Application Express Administration site. This is the primary set of tools used by the Application Express Administrator to create and manage workspaces in the Oracle database. This is not the account that is set up for each workspace to be the workspace administrator. There is generally only one Application Express Administrator while there is at least one workspace administrator account for each workspace created.

This password should always be changed using the provided script.

@apxchpwd

Configure APEX_PUBLIC_USER

If using the HTTP Server with mod_plsql (recommended), then the APEX_PUBLIC_USER must be configured properly. Log in as sysdba and set the password for the account.

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY password

It is also important to make sure the password for the APEX_PUBLIC_USER does not expire because your application will become unusable if the password changes and the dads.conf file is not updated properly.

The best method is to create a password profile that does not expire the password and assign that profile to the APEX_PUBLIC_USER account.

CREATE PROFILE apex_user_prfl
LIMIT PASSWORD_LIFE_TIME unlimited;
ALTER USER APEX_PUBLIC_USER PROFILE apex_user_prfl;

Copy the Images

The images for Apex must be copied from the apeximages directory to the home for the Oracle HTTP Server. The following example could be used on a unix platform

cp -rf $ORACLE_HOME/apex/images HTTPSERVERHOME/Apache

Configure the dads.conf file

Configure the dads.conf file (if using the HTTP Server with mod_plsql) - the following is a typical dads.conf entry for Application Express:

Alias /i/ "HTTPSERVER_HOME/Apache/images/"
<Location /pls/apex
Order deny, allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure www_flow_file_mgr.process_download
PlssqlDatabaseConnectString host:listenerport:service
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword <password set earlier>
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
Allow from all
</Location>

Enable Network Services (for 11g databases)

By default, in Oracle 11g (both R1 and R2) the ability to interact with network services is disabled. The DBMS_NETWORK_ACL_ADMIN package must be used to grant connect privileges to any host for the APEX_040000 database user account.

For more details on using the package, refer to the online installation documentation for Application Express (Application Express Installation Guide)

Setting JOB_QUEUE_PROCESSES

In order for Application Express to run successfully, it submits jobs, and requires JOB_QUEUE_PROCESSES to be set. A recommended starting number is 20.

Privileges Granted to the Workspace Administrator Accounts

Each workspace that is created is associated with a workspace administrator database account in the database. By default, there are a number of privileges assigned to this account, and the workspace administrator is able to connect via tools other than just Application Express.

The following is a list of the privileges that are assigned to the workspace administrator:

CONNECT
CREATE ANY CONTEXT
CREATE CLUSTER
CREATE DIMENSION
CREATE INDEXTYPE
CREATE JOB
CREATE MATERIALIZED VIEW
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW

With the popularity of Oracle Application Express on the rise, database administrators should be aware of the features, installation options, listener/communication options and privileges, and space associated with the workspaces and user accounts associated with Oracle Application Express.

» See All Articles by Columnist Karen Reliford