Friday, September 30, 2016

Correlating APEX Sessions to Database Sessions

I received the following question via email today:

"Had a question from a client yesterday concerning the subject:  I want to know which database session (APEX_PUBLIC_USER)  is servicing which APEX session. Poking around in the V$ tables, I can see that in v$SQL, the module column will reveal the APEX Application and Page, but not the Session ID.  Even if the session ID was in there, I don’t see an obvious way to join back to V$SESSION." 

It's a bit of a puzzling problem for DBA's and developers - being able to correlate a database session with a specific APEX application and session.  As I wrote about earlier, all database sessions in the database session pool of ORDS are connected as APEX_PUBLIC_USER (or ANONYMOUS, if you're using the embedded PL/SQL Gateway).  If a user is experiencing slowness in their APEX application, how can a DBA look under the hood, identify the database session associated with the request from that user, and also look at the active SQL statement and possibly any wait events with that session?

This question comes up a lot, and should really be covered in the Oracle Application Express documentation.  But in the meantime, here's the definitive answer:

APEX populates the following information in GV$SESSION for ACTIVE sessions:

client_info: Workspace ID:Authenticated username
module: DB Schema/APEX:APP application id:page id
client_identifier: Authenticated username:APEX Session ID

For example, for a recent request I did on apex.oracle.com, I had the following values in the DB session that executed my request:

client_info: 3574091691765823934:JOEL.KALLMAN@FOOBAR.COM
module: JOEL_DB/APEX:APP 17251:4
client_identifier: JOEL.KALLMAN@FOOBAR.COM:12161645673208

There is no permanent, fixed correlation between an APEX session and a database session.  The assignment of a session in the session pool to service an APEX request is essentially unpredictable and not constant.  That's why this correlation can only be done for active database sessions, which are actively servicing APEX requests.

There's one caveat.  A developer could overwrite these values using the database-provided PL/SQL API, which I've seen customers do occasionally.  Otherwise, for active database sessions, you'll see these three elements populated in GV$SESSION, and module & client_identifer will also be present in the Active Session History.  Carlos Sierra has an excellent blog post about how to query the Active Session History and identify poor performing APEX applications and their associated SQL.


Wednesday, September 14, 2016

Lessons Learned in 20 Years at Oracle

I've known Mark, a technical pre-sales consultant at Oracle, for a number of years.  I was bcc'd on the farewell email message that he sent out today, his last working day at Oracle.  As he said, he's learned a few things over his past 20 years at Oracle and thought he would share them.  They were quite simple and powerful reminders - and important enough that I shared them with our entire development and QA teams.

OWN IT —  Things sometimes go off track.   Whether it was something on your team that went sideways or it was another team's responsibility, step up, own the problem and deliver a resolution.  
LEARN IT —  There are always new technologies, solutions, processes, and procedures.   Set aside time to learn and master what is new so that you are prepared when the time comes.   
TEACH IT — When you master something new, find someone else with whom you can share it.  
GROW IT — Your team is incredibly valuable.   Take the time to invest in your teammates and equip them with new capabilities. 
OVERLOOK IT — People can make poor decisions.   Fight the urge to gossip about them.   Look for the best and ignore the rest.  

Sunday, July 24, 2016

Securing Oracle Application Express when using Oracle REST Data Services (ORDS)

If you are using Oracle REST Data Services as the "PL/SQL Gateway" for Oracle Application Express, ensure that your ORDS configuration includes the following line:

wwv_flow_epg_include_modules.authorize

It is important that you do this, and let me explain why.

Fundamentally, the APEX "engine" is really nothing more than a big PL/SQL program running inside the Oracle Database.  When a browser makes a request for a page in an APEX application, that request is mapped to a PL/SQL procedure which is running inside the database.  If you examine an APEX URL in your browser, you may see something like 'f?p=...', and this is invoking a PL/SQL procedure in the database named 'F' with a parameter named 'P'.

There are a number of procedures in the APEX engine which are intended to be invoked from a URL.  But there may be other procedures in your database, possibly owned by users other than the Application Express user, which are not intended to be called from a URL.  In some cases, these other procedures could leak information or introduce some other class of security issue.  There should be a simple list of procedures which are permitted to be invoked from a URL, and all others should be blocked.  This is known as a "whitelist", and fortunately, there is a native facility in APEX which defines this whitelist.  You just need to tell ORDS about this whitelist.

When you configure ORDS with the following entry in the configuration file:

wwv_flow_epg_include_modules.authorize

You are instructing ORDS to validate the PL/SQL procedure requested in the URL using the PL/SQL function wwv_flow_epg_include_modules.authorize.  This whitelist will contain all of the necessary entry points into the APEX engine, nothing more, nothing less.

If you rely upon functionality in your application which makes use of PL/SQL procedures not defined in this whitelist, this functionality will break when you specify the security.requestValidationFunction.  I often encounter customers who invoke PL/SQL procedures in their application schema to download files, but there are better (and more secure) ways to do this, which would not break when implementing this whitelist.

Like any change to infrastructure or configuration, you should thoroughly test your applications with this setting prior to introducing it into a production environment.  But if one or two things break because of this change, don't use that as an excuse to not implement this configuration change.  Identify the issues and correct them.  While there is a method in place to extend the whitelist, in practice, this should be seldom used.

If you're using ORDS as a mod_plsql replacement for your PL/SQL Web Toolkit application and not using APEX, then please avoid this configuration setting.  APEX typically won't be installed in your database, and the whitelist will be irrelevant for your application.

The function wwv_flow_epg_include_modules.authorize has been around for more than 10 years (our teammate Scott added it in 2005), and it has been a part of the embedded PL/SQL Gateway and mod_plsql default configuration for a long time.  And while it has been documented for use with ORDS, a reasonable person might ask why this isn't simply part of the default configuration of APEX & ORDS.  I did confirm with the ORDS team that this will be included in the default configuration when using the PL/SQL Gateway of ORDS, beginning in ORDS 3.0.7.

Monday, July 04, 2016

APEX session isolation across multiple browser tabs - Problem Solved (in APEX 5.1)

Since the genesis of Oracle Application Express, customers have asked for a way to open multiple browser tabs (or windows) of an APEX application and have the session state isolated between the respective tabs.  There is one and only one APEX session associated with a client, and because of this behavior in APEX, customers would find that the session state manipulated in one browser tab would collide with the session state of the other browser tab.

This has always been a vexing problem to solve for many years.  Back in 2007, I remember Carl Backstrom had spent countless hours researching for some handle or unique identifier to a browser window that we could correlate with a distinct browser session cookie, but he was never able to identify a feasible solution.  Customers have long asked for a solution, but all we were able to propose were rather cumbersome work arounds (ensure all items necessary for session state were posted with the page, or use the multiple DNS aliases "trick" for each tab).

In October 2015, our friends from BiLog arranged an informal meeting with a couple large enterprise customers from Croatia.  Goran, who was from one of the enterprise customers in the insurance industry, stated that the session management behavior of APEX presented a real problem for them.  Their typical scenario involved a sales representative who would meet with a customer in-person.  Because they wanted to offer insurance quotes or initiate insurance applications on multiple products, the sales representative would open up multiple tabs of their APEX application.  Of course, the session state across all of these tabs would collide and effectively corrupt the quoting process.  As Goran stated at the time, it became more and more difficult to justify the use of APEX because of this troublesome behavior.  I had no immediate answer, but I told him we would redouble our efforts and look at this problem again.

In February of this year, I had one of those lightbulb moments, and realized that we had been thinking about this problem the wrong way, and we needed to turn it inside out.  In APEX, there is always a single browser session cookie associated with an APEX session.  We were always trying to come up with a way to generate a new and differentiated browser session cookie every time a new tab was opened, and then associate this new browser session cookie with a new APEX session.  But the new approach was to simply keep the one and only one browser session cookie, and have this associated with multiple APEX sessions on the server.  I expressed my idea to the supremely intelligent Christian Neumueller of the APEX development team, and he went about with a masterful design and implementation of this feature.

In Application Express 5.1, we are introducing a new request to the APEX engine named APEX_CLONE_SESSION.  When requested from an existing APEX session, this will generate a new APEX session identifier and associate it with the existing browser session cookie.  Additionally, it will copy all of the session state values from the old session to the new session.  You, the developer, would have to provide a link for your end users to open up new browser tabs, and include APEX_CLONE_SESSION in the request of the URL.  So instead of your end users manually opening up a new tab from your APEX application, you would have to give them a prescribed way to open new tabs - could be a dynamic action or a button or a link.  The URL in the new tab should include APEX_CLONE_SESSION in the "Request" portion of the APEX URL.

An example URL would be:
f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:APEX_CLONE_SESSION

Because we were a bit paranoid about this feature until we could thoroughly vet the security of it, by default, this capability is turned off.  You can override this setting for a specific workspace by using the Administration API:

apex_instance_admin.set_workspace_parameter(
    p_workspace => 'JOELS_WORKSPACE',
    p_parameter => 'CLONE_SESSION_ENABLED',
    p_value     => 'Y');

or you can enable it for the entire instance using:
apex_instance_admin.set_parameter(
    p_parameter => 'CLONE_SESSION_ENABLED',
    p_value     => 'Y');

This feature is enabled instance-wide on the Application Express 5.1 Early Adopter site at https://apexea.oracle.com.  We would welcome your feedback about this feature.  And if you're reading this blog post after APEX 5.1 is generally available, please feel free to try it in your own APEX 5.1 (or later) instance or on https://apex.oracle.com.

Tuesday, March 29, 2016

An Important Change Coming for Oracle Application Express in Oracle Database 12cR2

A minor but important change is happening for Oracle Application Express in the forthcoming Oracle Database 12cR2.  Specifically, Oracle Application Express will not be installed by default in the Oracle Database.  This change was made specifically at our request.  We thought the pros far outweighed the cons, and we thought this was good for our customers and consistent with our recommendations.


Pros

  1. Provides flexibility for a DBA to run multiple APEX versions in an Oracle Multitenant Container Database.
  2. Customers are always advised to install latest version of APEX.  The version of APEX that is bundled with the Oracle Database is quickly out of date.
  3. Reduces the Oracle Database upgrade time if APEX is not installed.
  4. Will result in less space consumption on disk and in the Database.
  5. Consistent with the deployment of Application Express in the Oracle Database Cloud
  6. Consistent with our recommendations in Oracle documentation and from Mike Dietrich, Product Manager for Oracle Database Upgrade & Migrations.
  7. Reduces the attack surface for Oracle Multitenant Container Databases which do not require APEX.

Cons

  1. Requires more steps for customers to get up and running with APEX in a new Oracle Database.  (Granted, this would be with the version of APEX that is bundled with the Oracle Database, which as cited earlier, can get out of date rather quickly).

With all this said, a few things remain unchanged:
  • Oracle Application Express will continue to be a fully supported and included Oracle Database feature
  • Oracle Application Express will continue to ship with the Oracle Database 12cR2, in directory $ORACLE_HOME/apex.
  • It will continue to be supported to install and run Oracle Application Express in the "root" of an Oracle Multitenant Container Database
  • It will continue to be supported to install and run Oracle Application Express locally in a pluggable database in an Oracle Multitenant Container Database
  • Oracle Application Express will be an installable component in the Oracle Database Creation Assistant (DBCA)
The only thing that's changing is the out-of-the-box configuration of APEX, to not be installed by default.


Saturday, January 30, 2016

Oracle Database 12c Features Now Available on apex.oracle.com

As a lot of people know, apex.oracle.com is the customer evaluation instance of Oracle Application Express (APEX).  It's a place where anyone on the planet can sign up for a workspace and "kick the tires" of APEX.  After a brief signup process, in a matter of minutes you have access to a slice of an Oracle Database, Oracle REST Data Services, and Oracle Application Express, all easily accessed through your Web browser.

apex.oracle.com has been running Oracle Database 12c for a while now.  But a lot of the 12c-specific developer features weren't available, simply because the database initialization parameter COMPATIBLE wasn't set to 12.0.0.0.0 or higher.  If you've ever tried to use one of these features in SQL on apex.oracle.com, you may have run into the dreaded ORA-00406.  But as of today (January 30, 2016), that's changed.  You can now make full use of the 12c specific features on apex.oracle.com.  Even if you don't care about APEX, you can still sign up on apex.oracle.com and kick the tires of Oracle Database 12c.

What are some things you can do now on apex.oracle.com? You can use IDENTITY columns.  You can generate a default value from a sequence.  You can specify a default value for explicit NULL columns.  And much more.

You might wonder what's taken so long, and let's just say that sometimes it takes a while to move a change like this through the machinery that is Oracle.

P.S.  I've made the request to update MAX_STRING_SIZE to EXTENDED, so you can define column datatypes up to VARCHAR2(32767).  Until this is implemented, you're limited to VARCHAR2(4000).

Tuesday, January 26, 2016

Is Oracle Application Express Secure?

Is Oracle Application Express secure?  That's the question I received today, from the customer of a partner.  The customer asked:
"Do you know if Oracle or a third-party has verified how secure APEX is against threats or vulnerabilities? It would be nice to have something published saying how secure APEX is and how it’s never been compromised."
Now I imagine smart people like David Litchfield or Pete Finnigan or Alexander Kornbrust would hope that I say something daft here.  But that's not going to happen.  As I replied to the partner:

Sorry, but this doesn't make sense, and for a couple reasons:

  1. There have been published security vulnerabilities in Application Express in the Oracle Critical Patch Update, and they have been fixed in subsequent releases of APEX.  It is incorrect to say that there have never been bugs in APEX itself.  Here's an example:  http://www.oracle.com/technetwork/topics/security/cpujul2015-2367936.html
  2. Secondly, even if APEX never had any security bugs in its existence, if someone built an APEX application which is susceptible to SQL Injection or cross site scripting, does that mean that APEX was compromised?
The request of this customer isn't practical for any piece of software.  If something has never been compromised, does that mean its secure?  If I find no bugs in an application written by your company, does that mean it's bug-free?

I can offer you the following:

  1. APEX 5.0.3 is the most secure version of APEX in our history.
  2. APEX 5.0.3 has more security features than any release of APEX in our history.
  3. We are never permitted to release any version of APEX with known security vulnerabilities, whether they are internally or externally filed.
  4. We routinely scan APEX itself for security vulnerabilities across a variety of threats, and do this for multiple times in a release cycle
  5. Oracle Database Cloud Schema Service runs APEX, and has endured yet another set of multiple rounds of Cloud Security testing.
  6. The Oracle Store runs APEX.
  7. APEX is used in countless military agencies and classified agencies around the globe.
  8. Even inside of Oracle, IT hosts an instance of APEX used by practically every line of business in the company, and it's cleared for the most strict information classification inside of Oracle.
  9. APEX is even used in the security products from Oracle, including Oracle Audit Vault & Database Firewall, Oracle Key Vault and Oracle Real Application Security.
There is security of APEX, and then there is security of the application you've written.  You can assess the security of an application via tools.  One of the best tools on the market is ApexSec from Recx Ltd., which we use internally for APEX applications, is used internally by the security assessment teams at Oracle for other APEX applications, and is used by numerous military and other classified agencies.