Pelotonia10

Sorry, this is not a post about anything Oracle-related. But as our dear, departed friend Carl used to say - it's my blog.

I had the privilege of participating in this year's Pelotonia bike tour. What is Pelotonia, you may ask? "Pelotonia is a grassroots bike tour with one goal: to end cancer. Pelotonia raises money for innovative and life saving cancer research at The Ohio State University Comprehensive Cancer Center - James Cancer Hospital and Solove Research Institute."

This event had special significance for me, as our next-door neighbor, Jacob Carlino, was diagnosed with a rare form of cancer earlier this year and is undergoing treatment now. Jacob is 12 years old. A child like Jacob or any person, for that matter, shouldn't have to suffer through this horrible disease. Jacob's father Dave organized a team in support of his son Jacob, and I had the privilege of being on this team. Above is the team - Dan, Matt, Scott, me and Dave. On the front of our shirts is a picture of Jacob who we were honoring with our team's tour.

Pelotonia was a most-impressive and well-organized event. There were over 4,000 riders and thousands of volunteers in addition to a great deal of corporate sponsorship. The estimate is that this one event will raise over $8 million, with 100% of this money going directly to fund cancer research at the Ohio State University Cancer Care Center and Solove Research Institute. The honorary chair of this year's Pelotonia tour was Ohio State football legend Chris Spielman, who he himself lost his wife to cancer just 9 short months ago.

My goal for this blog post? To simply raise awareness of this disease, and to also let the world know that there is an ever-growing army of people right here in Ohio who is contributing in ways small and large to the eradication of this disease. Lastly, I would like to recognize and thank the sponsors of me for this ride - I am grateful for your generous support: Sue, Sergio & Priscila, Jason & Shelley, Eric, Bob & Marge, Matt & Gretchen, Tim & Susie, Anton & Chris, Kathy & Terry, Neil & Margaret, Tom, Nada & Matt, Frank, Harry, and Mike & Anita.

Application Express 4.0.1 released

Today, Oracle Application Express 4.0.1 was released. This corrects a number of issues discovered in the original Application Express 4.0 release. In the README of the Application Express 4.0.1 patch set, there is a listing of all of the bugs corrected in this patch set. You can view a copy of it here.

We have also repackaged the Oracle Application Express full distribution. If you have never installed or upgraded to Oracle Application Express 4.0, you can download the latest full distribution and install this and you'll have Application Express 4.0.1 out-of-the-box. Since this always creates confusion for customers (do you need the patch set or the full distribution?), use this guide:

1. If you have Oracle Application Express release 4.0 installed, download the Oracle Application Express 4.0.1 patch set from My Oracle Support and apply it. Look for patch number 9976149. Even though the file is named p9976149_11201_GENERIC.zip, seemingly only for DB 11.2.0.1, it can actually be used against any supported database version running Application Express 4.0.
   
   
2. If you have Oracle Application Express release 3.2.1 or earlier installed (including Oracle HTML DB release 1.5), download and install the entire Oracle Application Express 4.0.1 release from the Oracle Technology Network (OTN).
   
   
3. If you do not have Oracle Application Express installed, download and install the entire Oracle Application Express 4.0.1 release from the Oracle Technology Network (OTN).

And how do you determine what version you're running? Well, if you're a DBA, you can connect to the database as SYS using SQL*Plus and issue:

select version from sys.dba_registry where comp_id = 'APEX';

If you can login to the Application Express development environment, you'll see the version number in the lower right of every page.

Oracle Application Express at http://apex.oracle.com had the 4.0.1.00.03 patch set applied on Saturday, August 14, 2010.

Application Express and parsing of SQL

Recently, I worked with Oracle Support who was helping a customer who had a variety of questions about Oracle Application Express. One of the questions was:

"Please provide a detailed explanation of how apex interacts with database security. It appears that apex is manipulating queries prior to presenting them tot he database to be run either by wrapping them in a pl/sql block that prevents role based security from working or doing a pre-security check that doesn't check role level security prior to issuing a query, or some other even that bypasses role based security. "


Since there really isn't any great explanation of this anywhere, I'd like to provide a brief but lucid explanation about how parsing of SQL works in Oracle Application Express.

• The Oracle database ships with a supplied PL/SQL package named DBMS_SQL, to perform the execution of dynamic SQL.
  
  
• The Oracle database also ships with a supplied but undocumented PL/SQL package named SYS.DBMS_SYS_SQL. This package enables the execution of dynamic SQL but it also enables the parsing of a statement as a specific database user and with the privileges of this specific database user. This PL/SQL package is highly privileged, obviously. The EXECUTE privilege on this package is not granted to database user, by default. It should almost never be granted to any database user, ever. When asked to grant execution on this package to someone, a smart DBA will always say "no."
  
  
• Until Database version 10.2.0.3, both DBMS_SQL and DBMS_SYS_SQL would not observe roles when parsing SQL. This was consistent with database object access in PL/SQL itself (database roles are not observed in PL/SQL, in case you didn't know).
  
  
• In Database 10.2.0.3 and later (and XE), SYS.DBMS_SYS_SQL was changed to support a flag which enabled the observation of database roles when parsing SQL.
  
  
• In Application Express 2.1 and later, support was added to Oracle Application Express SQL Workshop on DB versions 10.2.0.3 and later to observe database roles, to be consistent with SQL*Plus. However, the execution of SQL in an Application Express application still does not observe roles when parsing user SQL. This remains true for the recently released Application Express 4.0.

So this explains a few things:

1. If you've ever wondered how Application Express can parse SQL as a specific database user and with the privileges of this database user but without ever connecting as that database user, this is the answer - DBMS_SYS_SQL.
   
   
2. When a new database user/schema is provisioned through Application Express, the discrete system privileges are granted to this new database user and not through any database role.
   
   
3. When you look the underlying database view V$SESSION, it will show that the database sessions associated with Application Express applications are connected as the minimally privileged database user APEX_PUBLIC_USER (or ANONYMOUS, if you're using the embedded PL/SQL gateway). But within that session, the underlying Application Express engine is being invoked and, after determining who the SQL can be parsed as for that specific page view, the SQL is being parsed as a different database user.
   
   
4. This also explains why, in some database versions, it will appear that roles are enabled when you issue DML statements from SQL Commands in SQL Workshop, but in the development of your application and in the execution of your application, it will appear that database roles are not enabled and that direct object privileges are required.