Monday, March 14, 2011

How I hacked the Apex 4 Websheets

Or, to be more specific (and less sensationalistic), how I struggled to, and finally succeeded in, using the new Apex 4 Websheets feature in a "Runtime-Only" environment.

What is an Apex runtime environment?

"Oracle recommends that you run any sensitive production Oracle Application Express applications with a runtime installation of Oracle Application Express. A runtime installation does not expose the Web-based application development environment, thus preventing the use of Application Builder, SQL Workshop, and related utilities on a production installation. Additionally, a runtime environment only includes the Oracle Application Express database objects and privileges necessary to run applications, making it a more hardened environment."

So, naturally, I set up Apex production environments using the runtime-only installation. But I hit a problem when I tried to deploy a Websheet application to such a production environment.

The setup

Here is what I did:

  1. Installed Oracle XE
  2. Installed Apex 4 runtime (apxrtins.sql)
  3. Manually created an application schema (SMALL_APPS) in the database
  4. Manually created the Websheet tables (APEX$ tables) in the SMALL_APPS schema (to do this I had to manually export the table scripts from the Apex development instance using TOAD, but I guess it could also have been done through the SQL Workshop in Apex itself)
  5. Manually created an Apex workspace for the SMALL_APPS schema (via the APEX_INSTANCE_ADMIN package)
  6. Exported the Workspace definition from the development instance. The generated workspace script contains the statements to create the workspace users. I removed the part of the script that creates the workspace itself (as I had already done this in the preceding step), and changed the workspace ID to the newly created workspace ID before I ran the script.


So far, so good.

The problem

I then proceeded to import the actual Websheet application.

This, however, threw up the following error:

WEBSHEET APPLICATION 112 - WebSheetSandbox
Set Credentials...
Check Compatibility...
API Last Extended:20100513
Your Current Version:20100513
This import is compatible with version: 20100513
COMPATIBLE (You should be able to run this import without issues.)


Set Application ID...
...Remove Websheet Application
...Create Websheet Application
...Create Access Control List
...Create Application Authentication Set Up
...Create Data Grid
Rollback

Error starting at line 163 in command:
declare
  q varchar2(32767) := null;
begin
q := null;
wwv_flow_api.create_ws_worksheet (
  p_id => 1311502709921962+wwv_flow_api.g_id_offset,
  p_flow_id => 4900,
  p_page_id => 2,

(snip...)

ORA-02291: integrity constraint (APEX_040000.WWV_FLOW_WORKSHEETS_FLOW_FK) violated - parent key not found
ORA-06512: at "APEX_040000.WWV_FLOW_API", line 14562
ORA-06512: at line 5
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause:    A foreign key value has no matching primary key value.
*Action:   Delete the foreign key or add a matching primary key.


From this it was evident that the data grids in the exported application are linked to application 4900, which is the built-in Websheets application. However, the Apex runtime-only installation does not install application 4900, hence the integrity error.

At this point I started to wonder if Websheets are supported in a runtime-only installation of Apex, and I posted the question in the Apex discussion forum on OTN.

But the only answer there was silence, so after a few days I decided to just go ahead and try to install application 4900 in the runtime environment, by running the f4900.sql script (from the apex\builder folder).

More problems

With application 4900 installed successfully, I was able to install my own Websheets application.

However, after login I was greeted with the following error message:

ORA-06550: line 9, column 46: PLS-00201: identifier 'WWV_FLOW_F4000_PLUGINS.RENDER_SEARCHBOX' must be declared ORA-06550: line 9, column 1: PL/SQL: Statement ignored

So, a package is missing. I located the package scripts (in the apex\core folder).

The comments in the package header (wwv_flow_f4000_plugins.sql) actually state:

"RUNTIME DEPLOYMENT: YES"

But evidently the package is not installed by the runtime installation script, so either this is a bug or the comment is wrong.

So I added the missing package by running:

alter session set current_schema = APEX_040000;


@wwv_flow_f4000_plugins.sql
@wwv_flow_f4000_plugins.plb

The final problem

This fixed the PLS-00201 error, but now I got several other errors, all similar to the following:

Unable to bind ":WS_APP_ID"
Unable to bind ":WEBPAGE_ID"
ORA-20001: run_query error q=select id, title, content, section_type, data_grid_id, report_id, data_section_style, nav_start_webpage_id, nav_max_level, nav_include_link, created_by from apex$_ws_webpg_sections where ws_app_id = :WS_APP_ID and webpage_id = :WEBPAGE_ID order by display_sequence
ORA-01003: no statement parsed


Still no feedback in the discussion forum, so I decided to dig deeper into the Apex internals (an interesting exercise in itself).

A solution!

In the end, the solution turned out to be simple. The WWV_FLOW_COMPANY_SCHEMAS table contains the workspace to schema mappings. This table contains a column called IS_APEX$_SCHEMA, and this needs to be set to "Y" (the APEX_INSTANCE_ADMIN.ADD_WORKSPACE procedure leaves the column value as NULL).

So just update the column to enable Websheets:

update wwv_flow_company_schemas
set is_apex$_schema = 'Y'
where schema = 'SMALL_APPS';

Voila! I now have a working Websheet application in my runtime-only Apex environment.


Postscript

While I was typing up this for the blog post, I stumbled across the following statement in the Apex Administration Guide:

"Tip: Websheets are not supported in an Oracle Application Express runtime environment."

I wish somebody could have pointed that out to me in the discussion forum thread. But then again, if they did, I probably wouldn't have discovered how to make it work anyway.

And if anyone from Oracle is reading this, consider this an enhancement request for Apex 4.1: Support Websheets in a runtime-only environment by:

  • Including application 4900 in the runtime installation
  • Including the wwv_flow_f4000_plugins package in the runtime installation
  • Add a parameter to the ADD_WORKSPACE and ADD_SCHEMA procedures to specify whether websheets should be enabled or not

9 comments:

SydOracle said...

Wanna bet whether it breaks when you get to upgrade Apex ?

Morten Braten said...

@Gary: We'll see...! But since the data is in the APEX$ tables in my own schema, it probably is just a matter of re-importing application 4900.

And I won't mind if my existing stuff breaks as long as the runtime deployment of Websheets becomes a supported option in Apex 4.1!

- Morten

Joel R. Kallman said...

Hi Morten,

Interesting blog post and an excellent job of deduction.

For any customers wishing to do this on their own production instances, I can assure you that not everything may work, Oracle Support won't be able to help if you have issues, and you may also encounter difficulties when upgrading.

Websheets in a runtime environment will not be a supported option in Application Express 4.1.

Joel

Morten Braten said...

@Joel: Thanks for the feedback. I've just started exploring websheets and I think they can help in getting rid of many spreadsheets within an organization. However, they won't be of much help if they can't be deployed to a production environment...

I guess I'm a bit disappointed that this option won't be supported in Apex 4.1. Do you have plans to support it further down the line?

- Morten

Joel R. Kallman said...

Hi Morten,

Anything is possible. Right now, no, but anything can change based on sufficient customer demand.

Joel

Morten Braten said...

@Joel: Thanks again for the info. Can I ask why you don't plan on supporting it, given that the changes required to make it work seem rather trivial?

- Morten

Joel R. Kallman said...

@Morten - it isn't solely about how trivial it is to get this working or not. The original purpose of a runtime installation is to minimize the attack surface of an APEX instance. This continues to be the goal.

I hope this helps.

Joel

Morten Braten said...

@Joel: What about providing websheets as a runtime option by specifying an additional parameter to the runtime installation script, so you can choose between including it or not ("runtime only" or "runtime with websheets")?

As I said, I like the concept of Websheets and think it has potential, but I don't want to be forced to install the (less secure) full Apex environment just to use it. After all, minimizing the attack surface (and still using Websheets) was the whole point of this blog post, since at the moment the only supported option is less secure.

- Morten

Joel R. Kallman said...

@Morten,

Note that I did not say "never". You asked if it was targeted for 4.1, and I said it was not.

Joel