Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Sep 15, 2010

Oracle Application Express - A DBA Perspective

By Karen Reliford

Oracle Application Express (APEX) is a product provided by Oracle that is used for rapid application development to create web enabled applications to run against an Oracle database. Being primarily a developer's tool, the question arises...should DBAs be overly concerned about APEX? And the short answer to this question is...YES!

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:

a) Database Version 10.2.0.3 or higher
b) Shared pool size at least 100M (or use Automatic Shared Memory Management or Automatic Memory Management)
c) Free space in the APEX tablespace 185M (plus 75M for each additional language required)
d) Free space in the SYSTEM tablespace 100M
e) Oracle XML DB must be installed, and the associated anonymous user must not be dropped
f) Oracle Text is required (but this is installed by default)
g) PL/SQL Web Toolkit version 10.1.2.0.6 or later

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date