1. Introduction

First defence line - Network security group - an OCI network firewall controls incoming request on TCP level. Second defence line, works on HTTP request level - Load Balancer controls request and makes potential redirections based on simple rules evaluating path part of the URL. In the next step third defence line - Web Application Firewall evaluates more complex rules having access to whole request, limits traffic, etc. The final defence line is based on capabilities of target web server, which in case of Oracle Database may be Oracle REST Data Services, Oracle APEX, or SQL Developer Web. Such combination of security enforcement points establishes complete set of protecting barriers, making braking into the system quite difficult or even not possible, when all elements are configured in a proper way. To make defence complete each of elements must be configured using its capabilities, and protecting from configuration error done at other protection barriers. Once it's done, the system is ready for public exposure. It's especially true for Oracle APEX, which gets more and more attention from customers, and continuously provides new features to increase security in public environment.

As APEX becomes more, and more important technology exposed to public Internet it's mandatory to look closer to its protection against malicious bots and cyber attacks.

2. Oracle recommended approach

To understand complexity of the problem it's recommended to start learning about this area by reading series of blog posts published by APEX team, where authors introduce to the problem, and propose two techniques based on internal APEX capabilities to disable administration applications, and use of OCI Load Balancer capabilities to (a) inject headers informing APEX about public access, (b) redirecting protected URL to permitted addresses, or (c) redirecting protected URL to fake server, producing HTTP error 503 Gateway Unavailable.

3. Going into production

Proposed solution protects internal APEX administration and development tools from public access mainly using APEX capability to disable internal apps by checking HTTP header injected by Load Balancer. It works unless someone will not remove it from APEX configuration or will not remove header manipulation from the Load Balancer configuration. Moreover this solution controls only APEX applications, and others like SQL Developer Web must be disabled by Load Balancer rule.

Authors propose to use Load Balancer redirection rules to hide sensitive applications, or even to use fake backend to return Gateway Unavailable error, what should rather never reach customer.
Part 3 of the blog series, shows how complex is to handle all exposed address by the latest Oracle Database. It's not only complex but it's even insufficient in many situations to just follow proposed solutions.
One of first examples is lack of knowledge of all URLs which are exposed by the latest Oracle Database with ORDS, APEX, and other Web based services. As for now it's a long list, and will be for sure extended in the future together with development of the product. Another problem is related with construction of URL accessing database tools, where user name (db schema) is part of the URL accessing SQL Developer Web. Such situation makes Load Balancer unable to guarantee security of exposed SQL Developer Web. The only thing we can do is to disable this product from public access at all. APEX URL does not make security engineer life easy as well. Load Balancer capabilities are useless dealing with APEX, even with so called Friendly URL enabled, as anyway APEX engine uses complex requests sometime with context information passed in the payload.

This picture leads to conclusion that security based on revokes of known addresses is not what we are looking for. Another issue is related with hiding technology complexity from users' eyes. Typical user should never see 503 Gateway Unavailable, or 404 error handled by the web browser; we should always try to show user more user friendly information.

Saying that I'm not rejecting APEX team ideas, treating them rather as highly important introduction to the problem, and first step in APEX protection. Users exposing Oracle Database ORDS and APEX service to public internet should implement both official recommendations: disable APEX internal apps, and redirect/reject sensitive URLs, however they should not stop on this.

4. Web Application Firewall

OCI Web Application Firewall (WAF) provides ability to create access control logic for HTTP requests. Interesting is that WAF is tightly integrated into Load Balancer, delivering entangled request processing. Engineering decided to use such technique to minimise overall latency introduced by HTTP level security barriers. In many places WAF features overlaps Load Balancer ones, but in many other extends them. One of great extensions is ability to build complex access rules, utilising JMESPath JSON query language ot evaluate HTTP request. It makes ot write quite complex rules having access to complete set of HTTP request elements.

5. Let's protect APEX

The idea behind using WAF access control lists it to prepare series of lists permitting access to certain resources. Once WAF evaluates access list positively, work is done, and request is passed to the load balancer for further processing. In case of negative evaluation of all registered access lists, what means that user tries to access resource which is not permitted, WAF returns defined HTTP code with configured payload. It may be redirect to main page, nice looking error page (with assumption that pictures are loaded from publicly available web sources), or raw error message may be returned to scary careless user.

General idea to protect APEX using WAF access control lists looks the following:

Grant access to APEX core resources
Grant access to application 101
Grant access to application 102
Grant all access for requests coming from corporate network.
Response with error handler - Sorry. Nothing available.

6. Let's extend APEX

Knowing that WAF may be regular security enforcement barrier APEX may be extended to react on ZENEDGE header always added by WAF. Seeing this header APEX may hide internal apps, unless asked not to do so with other header.

7. Access control lists

7.1 APEX core packages

APEX uses number of packages which are building blocks of target applications. As this is static read only content it's allowed for everyone to get this data. WAF should execute Pre-configured Allow Action once below is evaluated as true.

i_starts_with(http.request.url.path, '/i/22.2.0/themes') ||

i_starts_with(http.request.url.path, '/i/22.2.0/plugins') ||

i_starts_with(http.request.url.path, '/i/22.2.0/pkgapp_ui') ||

i_starts_with(http.request.url.path, '/i/22.2.0/libraries') ||

i_starts_with(http.request.url.path, '/i/22.2.0/app_ui') ||

i_starts_with(http.request.url.path, '/i/22.2.0/apex_ui') ||

http.request.url.path == '/i/22.2.0/apex_version.js' ||

http.request.url.path == '/favicon.ico'

7.2 APEX application

APEX applications are identified by unique number, what should be used to control user access. According to APEX experts some of requests are based on POST with application identifier embedded in payload, however in majority of cases application number is provided in query parameters. It's important that APEX engine takes always first occurrence of the argument, what was empirically tested by me on few cases. APEX uses interesting way to pass information about interaction with the application by encoding it in few parameters (different for various internal calls), having always application number at the front of parameter value. In below access control code application number of 102, and workspace is test4db1. Note that sometimes APEX uses direct application number, but im majority of cases it's a complex string with application number at the front of it. You may use this access list to expose any APEX hosted application, by changing 102 to any number including internal ones: 4000, 4050, 4500, 4550, 4600, or 4750. If you need to open access to series of apps, you need to register multiple access control lists.

(
 http.request.url.path == '/ords/f' &&
 i_starts_with(to_string(http.request.url.queryParameters.p[0]), '102')
) ||
i_starts_with(http.request.url.path, '/ords/r/test4db1/102/files') ||
(
 http.request.url.path == '/ords/wwv_flow.js_messages' &&
 http.request.url.queryParameters.p_app_id[0] == '102'
) ||
(
 http.request.url.path == '/ords/wwv_flow.ajax' &&
 i_starts_with(to_string(http.request.url.queryParameters.p_context[0]), '102')
) ||
(
 http.request.url.path == '/ords/wwv_flow.accept' &&
 i_starts_with(to_string(http.request.url.queryParameters.p_context[0]), '102')
) ||
(
 i_starts_with(http.request.url.path,'/ords/wwv_flow_customize') &&
 (
  i_starts_with(to_string(http.request.url.queryParameters.p_flow[0]), '102') ||
  i_starts_with(to_string(http.request.url.queryParameters.p_context[0]), '102')
 )
) ||
(
 http.request.url.path == '/ords/apex_authentication.logout' &&
 http.request.url.queryParameters.p_app_id[0] == '102'
)

7.3 APEX friendly URL

Apex introduced recently friendly URL with application name in the web address. It's nice, however still internally uses old style calls. To handle it different access control list id required. Below code allow access to opportunities application with id 102 defined in workspace test4db1.

i_starts_with(http.request.url.path, '/ords/r/test4db1/opportunities') ||
i_starts_with(http.request.url.path, '/ords/r/test4db1/102/files') ||
(
 http.request.url.path == '/ords/wwv_flow.js_messages' &&
 http.request.url.queryParameters.p_app_id[0] == '102'
) ||
(
    http.request.url.path == '/ords/wwv_flow.ajax' &&
    i_starts_with(to_string(http.request.url.queryParameters.p_context[0]), 'opportunities')
) ||
(
 http.request.url.path == '/ords/wwv_flow.accept' &&
 i_starts_with(to_string(http.request.url.queryParameters.p_context[0]), 'opportunities')
)

7.4 SQL Developer Web

Oracle Database provides Web based data processing tools. In OCI it's available under Database Actions button, which opens app under /ords/sql-developer path of the public server name. Typically it's not a good idea to expose such applications to public access, so this control list should be created on WAF Firewall associated with different Load Balancer, additionally protected by Network Security Group to permitted networks only. Notice source address check in the access list itself as well.

address_in(connection.source.address, ['0.0.0.0/0']) &&
starts_with(http.request.url.path, '/ords/') &&
(
 starts_with(http.request.url.path, '/ords/sql-developer') ||
 starts_with(http.request.url.path, '/ords/_adp') ||
 ends_with(http.request.url.path, '/sign-in/') ||
 i_contains(http.request.url.path, '/sign-in/') ||
 contains(http.request.url.path, '/_sdw/') ||
 contains(http.request.url.path, '/_/')  ||
 contains(http.request.url.path, '/_adp')  ||
 i_contains(http.request.url.path, '/signed-out/') ||
 contains(http.request.url.path, '/soda/latest')
)

Note: for some reason contains does not handle "-" properly, so I used case insensitive version of the function.

7.5 Default action

Each of above presented access lists, once evaluated to true, trigger "allow" action built into WAF engine, passing user access back to load balancer level. In case when none of them were positively evaluated WAF policy enforcement point executes default action, which may return any configured HTTP code with provided payload. Exemplary code shows error message, and some random graphics, which is sent back together with HTTP code 307 - temporary redirect. You can however redirect user to main application or any other kind of main page. WAF makes it possible to specify HTTP code, headers, and payload. Exemplary HTML is presented below:

<html>
<body>
<p>
    You are trying to do something wrong. Verify your web address.
</p>
<img src="https://www.computerhope.com/jargon/h/html.png">
</body>
</html>

8. Conclusion

Protecting Oracle Database Web interfaces, as APEX, ORDS, and SQL Developer Web is not an easy task, however proper configuration of Web Application Firewall, Load Balancer, and APEX makes it possible to protect against unintentional publishing internal services to the public Internet.

Using multiple security enforcement points eliminates risk of potential human error. We should keep in mind, that APEX administrator may make error by disabling HTTP header protection. Such situation will be covered by WAF "deny all not permitted" protection. In case of errors in WAF configuration, LB "deny known addresses" rule will help. Potential removal of LB header injection is covered by WAF ZENEDGE header, which may inform APEX to hide sensitive apps.
Combining "deny known addresses" offered by Load Balancer, with "allow known addresses, and deny all other" offered by Web Application Firewall, moves APEX security to much higher level.

Adding internal APEX protection based on HTTP header injected by Load Balancer, adds third level of APEX protection, what makes whole system very difficult to break.

Snails In Noble Software

Thursday, January 26, 2023

LB and WAF protecting APEX running on OCI