20.1 Understanding Administrator Security Best Practices

Administrators can configure login controls for an entire instance or for individual workspaces.

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.

Administrators can configure login controls at the instance or workspace level. For example, if an Instance administrator configures account login controls in Oracle Application Express Administration Services that configuration applies to all Application Express accounts in all workspaces across an entire development instance.

If the Instance administrator does not enable login controls across an entire instance, then each Workspace administrator can enable the following controls on a workspace-by-workspace basis:

• End-user account expiration and locking.

• A maximum number of failed login attempts for end-user accounts.

• The password lifetime (or number of days an end-user account password can be used before it expires for end-user accounts).

Administrators can use the Allow Public File Upload attribute to control whether unauthenticated users can upload files in applications that provide file upload capability

Administrators can restrict user access to an Oracle Application Express instance by entering a comma-delimited list of allowable IP addresses in the Restrict Access by IP Address attribute on the Manage Instance, Security page.

Administrators can use the Instance Proxy attribute to configure an entire Oracle Application Express instance to use a proxy for all outbound HTTP traffic.

Setting a proxy at the instance-level supersedes any proxies defined at the application-level or in web service references. If a proxy is specified, regions of type URL, Web services, and report printing will use the proxy.

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.

Instance administrators can configure both their Oracle Application Express instance and all related applications to require HTTPS by configuring the Require HTTPS and Require Outbound HTTPS attributes. Configuring these attributes forces authenticated pages within the App Builder to require HTTPS which encrypts network communications. Changing the Require HTTPS option does not affect the web server in any way. You will still need to enable the web server for HTTPS for these options to have any meaning. To learn more about enabling HTTPS, see the documentation for your specific web server.

Require HTTPS options include:

• Always - Enforces HTTPS for all applications (including the Oracle Application Express development and administration applications) to require HTTPS.

• Development and Administration - Forces all internal applications within Oracle Application Express (that is, App Builder, SQL Workshop, Instance Administration and so on) to require HTTPS.

• Application specific - Makes HTTPS dependent on application-level settings.

Administrators can use the Allow RESTful Access attribute to control whether developers can expose report regions as RESTful services.

Use the Rejoin Sessions attribute to control if Oracle Application Express supports application URLs that do not contain session IDs.

When rejoin sessions is enabled, Oracle Application Express attempts to use the session cookie to join an existing session, when a URL does not contain a session ID. To use Rejoin Sessions, administrators must enable Rejoin Sessions at the application or page-level. Note that a more restrictive value of Rejoin Sessions at the instance-level overrides application and page settings.

While supporting session joins is convenient, it does present some serious security risks:

• Triggering malicious session state changes or other modifications.

  If an attacker tricks the user into clicking an application link, this can trigger malicious session state changes or other modifications. Because of this risk, Application Express requires a checksum that the attacker will not be able to guess when processing requests that alters data.

• Triggering unintended changes between applications.

  If a user has two applications open in separate tabs on the same Oracle Application Express instance, one application could trigger unintended changes in the other one. The attacking application could be written by a hacker or it could have an XSS vulnerability that enables an attacker to inject code. This application could use Ajax calls that simulate user interactions with the other application.

Isolating workspaces is an effective approach to preventing browser attacks.

The only way to truly isolate a workspace is to enforce different domains in the URL by configuring the Allow Hostnames attribute in Oracle Application Express Administration Services. When the URLs of the attacker and the victim have different domains and hostnames, the browser's same-origin policy prevents attacks.

Workspace isolation by configuring Allow Hostnames is a counter measure against client side attacks that attempt to cross workspace boundaries. This security measure is not necessary if you trust all applications that are accessible using the instance's host which includes applications that are written in other frameworks and languages such as Oracle Application Development Framework (ADF) and Java.

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

Developers can configure session time out for a specific application, or administrators can configure sessions time out for an entire instance.

Site administrators can restrict password reuse for all administrator, developer, and end-user accounts based on a history of passwords previously used for the accounts.

1. Start SQL*Plus and connect to the database where Oracle Application Express is installed having the apex_administrator_role role or 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
     

   Tip:

   The APEX_ADMINISTRATOR_ROLE must be granted directly to a user and not through another database role.

2. Execute the following statement:

   ALTER SESSION SET CURRENT_SCHEMA = apex_180100;
   
   

3. Execute the following statement:

   BEGIN
   APEX_INSTANCE_ADMIN.SET_PARAMETER('PASSWORD_HISTORY_DAYS',365);
   COMMIT;
   END;
   /
   

   This restricts new or updated passwords to those that have not been used for the account for the previous 365 days. Recording of previously used passwords (actually, hashed representations of passwords) begins upon installation of Oracle Application Express.

4. To disable this feature, run the block above, providing 0 for the parameter value. For example:

   BEGIN
   APEX_INSTANCE_ADMIN.SET_PARAMETER('PASSWORD_HISTORY_DAYS',0);
   COMMIT;
   END;
   /
   

Disabling the Allow SQL Websheet attribute enables administrators to prevent Websheet users from accessing underlying database objects using SQL tags or from creating SQL reports.

Administrators can control the ability to create and access RESTful Services at either the workspace or instance-level.

Disabling the Enable RESTful Services attribute prevents developers from creating and editing RESTful Web Services mapped to SQL and PL/SQL in conjunction with Oracle REST Data Services 2.0 or later.

To configure Enable RESTful Services:

• In Workspace Administration — Sign in to Oracle Application Express and click the Administration menu. Select Manage Service, Set Workspace Preferences, SQL Workshop and find the Enable RESTful Services attribute.

• In Instance Administration — Sign in to Oracle Application Express Administration Services. Under Instance Settings, click Feature Configuration and click Feature Configuration. Find the REST tab and then Enable RESTful Services attribute.

Administrators determine the amount of automation when provisioning (or creating) new workspaces.

To determine how provisioning works, an Instance Administrator selects one of the following options on the Instance Settings page:

• Manual - The administrator creates new workspaces and manually notifies the Workspace administrator of the login information.

• Request - Users request workspaces directly in a self-service fashion.

• Request with Email Verification - In this mode, users request workspaces directly by clicking a link on the login page to access a request form. Each user receives an initial email containing a verification link. When the user clicks the verification link, the request is processed. The user can then access the workspace using the Sign In page.

In order to send email from an Oracle Application Express application, an Instance administrator must define the email SMTP settings and set the Use SSL/TLS attribute to use a secure connection for Oracle Database 11g R2 or later.

Administrators can configure the following storage options: require a new schema when requesting a workspace, auto extend tablespaces, or delete uploaded files are a specified number of days.

A wallet is a password-protected container that stores authentication and signing credentials (including private keys, certificates, and trusted certificates) needed by SSL. You must create a wallet if you:

• Call a SSL-enabled URL (for example, by invoking a Web service).

• Create a region of type URL that is SSL-enabled.

• Configure secure SMTP by setting the Use SSL/TLS attribute to Yes.

• Have applications with LDAP authentication schemes that are configured to use SSL with Authentication.

When users log in to Oracle Application Express, they log in to a shared work area called a workspace. Each workspace can have multiple associated (or mapped) schemas. Instance administrators can define associations between workspaces and schemas as appropriate. By associating a workspace with a schema, developers in that workspace can create new database objects in that schema and build applications that interact with the database objects in that schema.

Administrators can define what schemas specific users have access to and also their user role, that is if they are a workspace administrator, a developer, or an end user. Administrators can also limit developer access to specific Application Express components and lock accounts. Oracle Application Express includes two separate interfaces for managing developers and users. Workspace administrators manage user accounts within their workspace and Instance administrators manage all user accounts across an Application Express development instance.

Workspace administrators can control user access to key components such as App Builder, Team Development, SQL Workshop, and PL/SQL editing, which determines whether developers have the ability to edit and compile PL/SQL program units from Object Browser. For example, suppose an administrator wants users to be able to build database components, run SQL statements, but not create applications, he or she could define a workspace with rights to a specific schema and then configure the users as developers using the options on the Set Workspace Preferences page.

Enabling network services enables support for sending outbound mail in Oracle Application Express, use of Web services in Oracle Application Express, and PDF report printing.

By default, the ability to interact with network services is disabled in Oracle Database 11g Release 2 or later. Therefore, if you are running Oracle Application Express with Oracle Database 11g Release 2 or later, you must use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_180100 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.

Demonstrates how to grant connect privileges to any host for the APEX_180100 database user.

DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_180100
  -- the "connect" privilege if APEX_180100 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;
 
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_180100',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_180100', 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_180100', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;

DECLARE
  ACL_PATH  VARCHAR2(4000);
BEGIN
  -- Look for the ACL currently assigned to 'localhost' and give APEX_180100
  -- the "connect" privilege if APEX_180100 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;
   
  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_180100',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_180100', 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 users to connect to localhost',
    'APEX_180100', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;
/
COMMIT;

Procedures CREATE_ACL, ASSIGN_ACL, ADD_PRIVILEGE and CHECK_PRIVILEGE in DBMS_NETWORK_ACL_ADMIN are deprecated in Oracle Database 12c. Oracle recommends to use APPEND_HOST_ACE.

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_180100',
                           principal_type => xs_acl.ptype_db));
END;
/

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => 'localhost',
        ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                           principal_name => 'APEX_180100',
                           principal_type => xs_acl.ptype_db));
END;
/

Learn how to identify a invalid ACL error by running the query.

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;