Understanding Administrator Security Best Practices

Security Considerations When Using the Embedded PL/SQL Gateway

The embedded PL/SQL gateway runs in the database as part of the XML DB HTTP Protocol Listener. The XML DB HTTP Protocol Listener and embedded PL/SQL gateway provides the equivalent core features of Oracle HTTP Server and mod_plsql. Because the HTTP Listener runs in the same database where Oracle Application Express is installed, it is not possible to separate the HTTP listener from the database. For this reason, it is not recommended to use the embedded PL/SQL gateway for applications that run on the Internet. Additionally, the embedded PL/SQL gateway does not provide the same flexibility of configuration and detailed logging as Oracle HTTP Server with mod_plsql.

About Configuring Oracle HTTP Server with mod_plsql for Oracle Application Express

Oracle HTTP Server uses the mod_plsql plug-in to communicate with the Oracle Application Express engine within the Oracle database. mod_plsql functions act as a communication broker between the Web server and the Oracle Application Express engine in the Oracle database.

Each mod_plsql request is associated with a set of configuration values used to access the database called a Database Access Descriptor (DAD). mod_plsql provides a DAD parameter called PlsqlRequestValidationFunction which enables you to allow or disallow further processing of a requested procedure. You can utilize this parameter to implement tighter security for your PL/SQL application by blocking package and procedure calls which should not be allowed to run from the DAD. Oracle recommends a DAD configuration for Oracle Application Express which utilizes the PlsqlRequestValidationFunction directive with a value of wwv_flow_epg_include_modules.authorize.

The purpose of the PlsqlRequestValidationFunction parameter is to control which procedures can be invoked through mod_plsql. By default, the only procedures permitted are the public entry points of Oracle Application Express. This can be extended using the validation functions shipped with Oracle Application Express. To learn more, see "Restricting Access to Oracle Application Express" in Oracle Application Express Administration Guide.

Utilizing Secure Sockets Layer (SSL)

Secure Sockets Layer (SSL) is a protocol for managing the security of data transmitted on the Internet. For Web applications, SSL is implemented by using the HTTPS protocol. Oracle recommends that you run Oracle Application Express applications using SSL (HTTPS protocol) to prevent any sensitive data from being sent over an unencrypted (cleartext) communication channel.

You can configure both your Oracle Application Express instance and all related applications to require HTTPS by setting the Require HTTPS attribute to Yes on the on the Manage Service page in Oracle Application Express Administration Services.

Setting the Require HTTPS attribute to Yes and logging out prevents anyone from logging in to Oracle Application Express Administration Services and from logging into an Oracle Application Express workspace as a developer or administrator unless the protocol in use is HTTPS.

Integrating with Oracle BI Publisher

Oracle recommends that you employ Secure Sockets Layer (SSL) when integrating Oracle Application Express with Oracle BI Publisher. Once SSL (HTTPS protocol) is configured for your Oracle BI Publisher server, you must create a wallet and specify the HTTPS protocol for the Report Server in the internal administration Environment Settings.

About Setting Password Complexity Rules

Workspace administrators and developers can create user accounts for the purpose of logging in to the Oracle Application Express development environment and for end-user authentication to applications developed within their workspaces. The Oracle Application Express administrator can create password complexity rules, or a policy, for the passwords of these accounts. These rules apply to all accounts in the installation, across all workspaces.

The following are example password complexity policies:

All passwords must:

• Contain at least one upper-case character

• Contain at least one numeric digit

• Must be at least six characters long

Note that many other variations are possible. Oracle recommends administrators establish a password complexity policy for each Oracle Application Express instance. To learn more, see "Enabling Login Controls for All Workspaces," "About Password Policies," and "Enabling Login Controls for a Workspace" in Oracle Application Express Administration Guide.

Advantages of the Oracle Application Express Runtime Environment

An Oracle Application Express runtime environment enables you to run production applications, but it does not provide a Web interface for administration or direct development of these applications

Oracle recommends that you run any sensitive production Oracle Application Express applications with a runtime installation of Oracle Application Express. A runtime installation does not expose the Web-based application development environment, thus preventing the use of Application Builder, SQL Workshop, and related utilities on a production installation. Additionally, a runtime environment only includes the Oracle Application Express database objects and privileges necessary to run applications, making it a more hardened environment.

Understanding Session Timeout

By configuring Session Timeout attributes, you can reduce your application's exposure. Users often leave their computers unattended for extended periods and do not close applications before departing. Therefore, an unauthorized person can easily assume the user's identity within the application. By setting the session and idle timeout, users are automatically logged out of their application after the specified timeout.

Session Timeout attributes include:

• Maximum Session Length in Seconds

• Session Timeout URL

• Maximum Session Idle Time in Seconds

• Idle Timeout URL

To learn more about configuring these attributes at an application-level, see "Session Timeout". To learn more about configuring these attributes for an entire Oracle Application Express instance, see "Configuring Session Timeout" in Oracle Application Express Administration Guide.

Enabling Network Services in Oracle Database 11g

By default, the ability to interact with network services is disabled in Oracle Database 11g release 1 (11.1). Therefore, if you are running Oracle Application Express with Oracle Database 11g release 1 (11.1), you must use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_030200 database user. Failing to grant these privileges results in issues with:

• Sending outbound mail in Oracle Application Express.

  Users can call methods from the APEX_MAIL package, but issues arise when sending outbound email.

• Using Web services in Oracle Application Express.

• PDF/report printing.

• Searching for content in online Help (that is, using the Find link).

Topics in this section include:

• Granting Connect Privileges to a Host

• Granting Connect Privileges to a Local Host

• Troubleshooting an Invalid ACL Error

DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_030200
  -- the "connect" privilege if APEX_030200 does not have the privilege yet.

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- Before checking the privilege, make sure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_030200'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
    FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
         EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200', 
     'connect') IS NULL THEN 
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
     'APEX_030200', TRUE, 'connect'); 
  END IF;

EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_030200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  -- Look for the ACL currently assigned to 'localhost' and give APEX_030200
  -- the "connect" privilege if APEX_030200 does not have the privilege yet.
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- Before checking the privilege, make sure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_030200'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --

  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
    FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
         EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_030200', 
     'connect') IS NULL THEN 
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
     'APEX_030200', TRUE, 'connect'); 
  END IF;

EXCEPTION
  -- When no ACL has been assigned to 'localhost'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_030200', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;
/
COMMIT;

REM Show the dangling references to dropped users in the ACL that is assigned
REM to '*'.

SELECT ACL, PRINCIPAL
  FROM DBA_NETWORK_ACLS NACL, XDS_ACE ACE
 WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL AND
       NACL.ACLID = ACE.ACLID AND
       NOT EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);

DECLARE
  ACL_ID   RAW(16);
  CNT      NUMBER;
BEGIN
  -- Look for the object ID of the ACL currently assigned to '*'
  SELECT ACLID INTO ACL_ID FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- If just some users referenced in the ACL are invalid, remove just those
  -- users in the ACL. Otherwise, drop the ACL completely.
  SELECT COUNT(PRINCIPAL) INTO CNT FROM XDS_ACE
   WHERE ACLID = ACL_ID AND
         EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);

  IF (CNT > 0) THEN

    FOR R IN (SELECT PRINCIPAL FROM XDS_ACE
               WHERE ACLID = ACL_ID AND
                     NOT EXISTS (SELECT NULL FROM ALL_USERS
                                  WHERE USERNAME = PRINCIPAL)) LOOP
      UPDATE XDB.XDB$ACL
         SET OBJECT_VALUE =
               DELETEXML(OBJECT_VALUE,
                         '/ACL/ACE[PRINCIPAL="'||R.PRINCIPAL||'"]')
       WHERE OBJECT_ID = ACL_ID;
    END LOOP;

  ELSE
    DELETE FROM XDB.XDB$ACL WHERE OBJECT_ID = ACL_ID;
  END IF;

END;
/

REM commit the changes.

COMMIT;

Enabling Indexing of Online Help in Oracle Database 11g Release 2 and Higher

The ability to search Oracle Application Express online Help is accomplished through Oracle Text and a URL data store. There is a change in the default behavior and permissions to use an Oracle Text URL data store in Oracle Database 11g Release 2 and later releases.

If users attempt to search Oracle Application Express online Help in Oracle database 11g Release 2 and encounter the following error, then the permission to use an Oracle Text URL data store has not been granted to database user APEX_030200.

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine 
ORA-20000: Oracle Text error: 
DRG-10758: index owner does not have the privilege to use file or URL data store

To enable the indexing of online Help in Oracle Application Express, the permission to use an Oracle Text URL data store must be granted to the APEX_030200 database user. This is accomplished by assigning this specific privilege to a datbase role and then granting this role to the APEX_030200 database user.

To determine if the ability to use an Oracle Text URL data store is already granted to a database role:

1. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role. For example:

   • On Windows:

     SYSTEM_DRIVE:\ sqlplus /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

   • On UNIX and Linux:

     $ sqlplus /nolog
     SQL> CONNECT SYS as SYSDBA
     Enter password: SYS_password
     

2. Run the following command:

   SELECT par_value FROM ctxsys.ctx_parameters WHERE par_name = 'FILE_ACCESS_ROLE';
   

   This returns either NULL or the database role which is granted the ability to use an Oracle Text URL data store.

3. If no value is returned by step 2, then create a new database role as shown in the following example:

   CREATE ROLE APEX_URL_DATASTORE_ROLE;
   

4. Grant this role to the database user APEX_030200 with the following statement:

   GRANT APEX_URL_DATASTORE_ROLE to APEX_030200;
   

   If step 2 returned a value, use this database role name instead of the example APEX_URL_DATASTORE_ROLE.

5. Lastly, if step 2 did not return a value, then use the Oracle Text API to grant permission to the newly created database role with the following statement:

   EXEC ctxsys.ctx_adm.set_parameter('file_access_role', 'APEX_URL_DATASTORE_ROLE');