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.

Thursday, January 21, 2016

If you use Internet Explorer, change is coming for you in Oracle Application Express 5.1

With the ever-changing browser landscape, we needed to make some tough decisions as to which browsers and versions are going to be deemed "supported" for Oracle Application Express.  There isn't enough time and money to support all browsers and all versions, each with different bugs and varying levels of support of standards.

A position that's been adopted for the Oracle Cloud services and products is to support the current version of a browser and the prior major release.  We are adopting this same standard for Oracle Application Express beginning with Oracle Application Express 5.1.  This will most likely have the greatest impact on those people who use Microsoft Internet Explorer. 

Beginning with Oracle Application Express 5.1, the planned minimum version of Internet Explorer to both build and deploy applications, will be Internet Explorer 11.  I say "planned", because it's possible (but unlikely) that Microsoft releases a new browser version prior to the release of Oracle Application Express 5.1.

Granted, even Microsoft themselves has already dropped support for any version of IE before Internet Explorer 11.  And with no security fixes planned for any version of IE prior to Internet Explorer 11, hopefully this will be enough to encourage all users of IE to adopt IE 11 as their minimum version.

Oracle APEX development and multiple developers/branches

Today, I observed an exchange inside of Oracle about a topic that comes up from time to time.  And it has to do with the development of APEX applications, and how you manage this across releases and a larger number of developers.  This topic tends to vex some teams when they start working with Oracle Application Express on broader development projects, especially when people are not accustomed to a hosted declarative development model.  I thought Koen Lostrie of Oracle Curriculum Development provided a brilliant response, and it was worth sharing with the broader APEX community.

Alec from Oracle asked:
"Are there any online resources that discuss how to work with APEX with multiple developers and multiple branches of development for an application?  Our team is using Mercurial to do source control management now. 
The basic workflow is that there are several developers who are working on mostly independent features.  There are production, staging, development, and personal versions of the application code.  Developers implement bug fixes or new features and those get pushed to the development version.  Certain features from development get approved to go to staging and pushed.  Those features in staging may be rolled back or promoted to go on to production.  Are there resources which talk about implementing such a workflow using APEX?  Or APEX instructors to talk to about this workflow?"

And to which I thought Koen gave a very clear reply, complete with evidence of how they are successfully managing this today in their Oracle Curriculum Development team.  Koen said:

"I think a lot of teams struggle with what you are describing because of the nature of APEX source code and Database-based development.  I personally think that the development flow should be adapted to APEX rather than trying to use an existing process and apply that for APEX.

Let me explain how we do it in our team:

  • We release patches to production every 3 weeks. We have development/build/stage and production and use continuous integration to apply patches on build and stage.
  • We use an Agile-based process. At the start of each cycle we determine what goes in the patch.
  • Source control is done on Oracle Developer Cloud Service (ODCS)  – we use git and source tree. We don’t branch.
  • All developers work directly on development (the master environment) for bugs/small enhancement requests. We use the BUILD OPTION feature of APEX to prevent certain functionality from being exposed in production. This is a great feature which allows developer to create new APEX components in development but the changes are not visible in the other environments.
  • For big changes like prototypes, a developer can work on his own instance but this rarely happens. It is more common for a developer to work on a copy of the app to test something out. Once the change gets approved. it will go into development.

From what I see in the process you describe, the challenge in your process is that new changes get pulled back after they have made it to stage. This is a very expensive step. The developers need to roll back their changes to an earlier state which is a very time consuming process. And… very frustrating for the individual developer.  Is this really necessary ? Can the changes not be reviewed when in development ? Because that is what is proposed in the Agile methodology: the developer talks directly to the person/team that requests the new feature and they review as early as on development.  In our case stage is only for testing changes. We fix bugs when the app is in stage, but we  don’t roll back features once they are in stage – worst case we can delay the patch entirely but that happens very rarely.

There is a good paper available by Rob Van Wijk. He describes how each developer works on his own instance but keeps his environment in sync with the master. In his case too, they’re working on a central master environment. The setup of such an environment is quite complex. You can find the paper here: http://rwijk.blogspot.com/2013/03/paper-professional-software-development.html"