APEX applications can be secure or insecure just like any other computer applications. The more widespread a technology is the bigger the circle of people who deal with it to find security loopholes and capitalised on them. APEX is just like JEE or .NET a system that is „out of the box“ which is insufficiently secure and whose security requirements need to be adjusted.
The security standard of the whole system is determined by the weakest chain link. In doing so one must also consider the operating system, the database and the network. The security model must also include a security maintenance, which defines tasks that need to be done regularly like patches, checking of auditlogs or monitoring the rights. To minimalize complexities, this article talks about the specific aspects of APEX configuration in a productive setting starting with all the general threats to an APEX application that are relevant for all web applications.
Threats to an APEX Application
SQL Injection: The attacker changes the SQL command and introduces changed behaviour. This can lead to disturbance in a database or to accessing protected data. SQL injection comes about as a result of character string change action to an existing SQL command. A manipulated parameter that is executed by a SQL command is passed by the user who changed it. Protection is provided by bind variables or by using the DBMS_ASSERT-Package.
Cross Site Scripting (CSS): A destructive code is slipped through a website such that it runs on the clients browser. There are two types of attacks persistent and non persistent attacks. A persistent attack would be saved as a blog entry in the database and will be displayed as part of the website. It is displayed and running on the clients browser and may cause a javascript call.
URL Tampering: The manipulation of the URL causes the displaying of web content that the user is not supposed to see. For example the HTTP-GET personal ID parameter is manipulated and sent to the server. To guard against such attacks Session State Protection can be used and this will be discussed futher in this article.
Network: Through tapping of the network parcels one can access personal IDs, passwords and other targeted information. Also the manipulation or repeatedly sending of network packages like a „man in the middle-attack“ must be observed. An encrypted HTTP connection with SSL (Secure Sockets Layer) protects the HTTPS against these attacks. For high security standards a certificate from a certification center can be used.
Installation choices
When installing the APEX application, one differentiates between an installation with the embedded PL/SQL Gateway (EPG) as well as installation with Oracle HTTP-Server (OHS) and MOD_PLSQL. Other than this one can also install the APEX Listener. The embedded PL/SQL gateway the Oracle XML DB HTTP Server runs directly in the Oracle 11g database. In this two level architecture, the users browser has direct access to the database. There are less network overheads as compared to the other varieties as one does not need to install a seperate server. On security matters this solution cannot be recommended because the HTTP Listener and APEX are run in the same database and it is not possible to seperate them. The database cannot be seperated behind a firewall and this makes prone to attacks.MOD_PLSQL is an expanded Apache webserver that uses PL/SQL packages and stored procedures, which can be used to produce dynamic websites. The advantage is the seperation of the Apache database from the server such that the database can be protected behind a firewall ensuring a high standard of security. The APEX Listener runs on a java container, which is connected through JDBC and the database. Listener and database can be seperated through a firewall so that the same security like in the useage of MOD_PLSQL solutions is generated:
 |
| APEX installation with MOD_PLSQL and APEX listener |
The APEX listener has extensive security functions that make it the safest APEX installation variety. It has lists of allowed and blocked procedures, which can be used for procedures, packages and scheme names. The database validation function is a function of the database that checks if the requested procedure should be executed. It can use a cache of the listener website and prevent a database roundtrip. On receiving a request, the validate function is checked then the allowed list and at last the blocked list. Only then does the procedure call follow. The APEX listener has an error tracking system and keeps statistics and a protocol. This protocol shows information like the changed cache entries of the validate function or the execution time of the procedure.
Data Access Descripter (DAD)
If one does not use the APEX listener then one can limit the access to the database using Database Access Descriptor (DAD). Using „wwv_flow_epg_include_mod_local“ function one can use string comparison to authorise a user defined function. This function says TRUE for a successful authorisation or FALSE for unauthorised. An example of the script shows the authorisation of the function „myfunction“CREATE OR REPLACE function wwv_flow_epg_include_mod_local( procedure_name in varchar2)
return boolean
is
begin
if upper(procedure_name) in (‘APEX_040100.MYFUNCTION','MYFUNCTION') then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;
IP Whitelist, HTTPS, File Upload, Session Timeout and Passwords
With the settings „Restrict Access by IP Address“ one can limit the access to APEX entity on the basis of the IP. In order to change the parameters one logs in as the APEX-admin through the website and then goes to Manage Instance-> Security under „Restrict Access by IP Address“ an adjustable list can be entered by using wildcards.Setting the „Require HTTPS-Attribute to „Yes“ activates the HTTPS-Protocol. Data is encypted and transmitted through the internet to the clients browser. These settings can be done in INTERNAL-Workspace under Manage Instance -> security. Through the configuration of „Allow Public File Upload“ attributes, which are control elements to „no“ prevents a user who is not authenticated from uploading data. So as not to lock out browsers or users who do not logout of the application and pose a little risk, it is wise to define the parameter Session Timeout. It is important that the users do not use a simple passwords. An example for these rules would be at least the use of a capital letter, a number or a minimum length of the password consisting of six letters. Furthermore security can be improved if the repeated useage of password is prohibited. This functionality be activated for 60 days under sqlplus as APEX schema owner (APEX_040100) as shown in this example:
APEX_INSTANCE_ADMIN.SET_PARAMETER('PASSWORD_HISTORY_DAYS',60);
Session State Protection
Using HTTPS ensures that packages en route can no longer be manipulated. However, the user can manipulate the Person-ID or Client ID in a browser before sending. APEX saves session parameters persistent in the database. It is for this reason that sensitive data in the session should be saved in an encrypted form. As shown in illustartion 3. Shared Components->Session State Protection can be activated in different levels by using a wizard: |
| Session-State-Protection against the URL-Manipulation. |
There is a difference between „Application Item Protection“, which protects individual items and „Page Access Protection”, which protects the whole page. The Page protection is chosen from the wizard for the page „Arguments Must Have Checksum“. Other settings are „No Arguments Allowed“, „No URL Access“ or „Unrestricted“.
A page protected by „Arguments Must Have Checksum“ has the last request in the URL as Parameter cs=<checksum>. The user can see the other parameters in the URL but a change leads to a wrong checksumme and results in an error. This prevents URL tampering.
Session State Protection and VPD
A Session State Protection protects the clients side from manipulation. An SQL command that finds a security loophole by Session State Protection will be executed unhindered. Another problem is an error due to complex applications caused by faulty design in the reports leads to showing a lot of unintended data. Quality control has never retrieved the report that uses loopholes to exploit the users parameters. However, with loopholes in the Session State Protection or in an application error an additional Session State Protection and a Virtual Private Database can be used to protect the data. VPD can only be used with the Enterprise Edition of the database compelling the protection of the data when executing the SQL command in the database. An example should show how VPD with APEX can be used. The basic usage is explained in the MyOracleSupport Note (ID 1352641.1)“ Mater Note For Oracle Virtual Private Database“. In the example a new workspace called TESTAPP is created in the TESTAPP schema, a table „confidential“, is built. This table consists of tables, functions and procedure names of the database using the dba_objects table. The following SQL command is executed as sys with sqlplus:create table testapp.geheim as select * from (
select object_name, object_type ,
ROW_NUMBER() OVER(PARTITION BY object_type ORDER BY object_type, object_name) myindex
from dba_objects where object_type in ('TABLE', 'FUNCTION', 'PROCEDURE') order by object_type, object_name) where myindex <= 5
A simple report is created for this new application in APEX in which the whole table can be selected by the following SQL-Command „select * from confidential“. In APEX three users admin, table_admin and procedure_admin are determined for the application. Later the user is supposed to see only the tables table_admin and procedure_admin and the procedures in the „confidential“ table. In order to use VPD TESTAPP schema for the Logon.Procedure, „vpd_context.logon User“ context setting is determined like the Policy-Function „pol_confidential“ and implemented to uphold the above rule:
Grant EXECUTE ON DBMS_RLS TO TESTAPP; (as user sys)
Grant CREATE ANY CONTEXT TO TESTAPP; (als user sys)
create or replace context apex_context using vpd_context
/
create or replace package vpd_context is
procedure logonUser(userid varchar2);
end vpd_context;
/
create or replace package body vpd_context is
procedure logonUser(userid varchar2) is
begin
dbms_session.set_context('apex_context','userid', userid);
end logonUser;
end vpd_context;
/
create or replace function pol_geheim(obj_owner in varchar2, obj_name in varchar2) return varchar2
as
predicate varchar2(200) := '1=2';
begin
if SYS_CONTEXT('apex_context','userid')= 'ADMIN' then
predicate := '';
elsif SYS_CONTEXT('apex_context','userid')= 'TABLE_ADMIN' then
predicate := 'OBJECT_TYPE=''TABLE''';
elsif SYS_CONTEXT('apex_context','userid')= 'PROCEDURE_ADMIN' then
predicate := 'OBJECT_TYPE=''PROCEDURE''';
end if;
return predicate;
end;
/
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'testapp2',
object_name => 'geheim',
policy_name => 'secure_geheim',
policy_function => 'pol_geheim',
statement_types => 'SELECT');
END;
/
The rule is activated by using the function ADD_POLICY. Logging in as user admin does not show any data because APEX context is not yet in use. In APEX under Application Builder->„Edit Application Properties“->Security in the field „Initialization PL/SQL Code“ the context can be determined and configured as:
„vpd_context.logonUser(v('APP_USER'))“.
APEX substitutes v('APP_USER') by the user who logs in. If user table_admin or procedure_admin logs in and a report is retrieved one can see different data as shown below. The rule is enforced not through APEX but through the forced execution of VPD.
 |
| Report data on the left procedure_admin, on the right table_admin. |
Conclusion
The general threats to an APEX application are multifaceted. Just like the attacker is creative in his attacks, the security concepts in defence need to be just as creative.A holistic approach must include all system components. Even during the development of the application threats like SQL injection and bind variables must be considered. A basic APEX hardening involves installation of the right variety and the determination of APEX security parameters. The costs involved for these measures are resonable and lead to an advanced security. Other measures to increase the security are associated with higher costs. The use of Virtual Private Database for example requires an Enterprise Edition License. VPD is useful in providing additional protection to prevent access of sensitive data by the users but not by the database adminstrator.
