Back to the Future

Come with me on a short trip through space and time, taking a look at the history of building (web sites).


The Stone Age, circa 1995

Although it involved a lot of manual labor, mighty Stonehenge and the pyramids were built with very simple tools (or by aliens, but let's deal with that topic another day).

Web development was pretty basic back then, but conceptually simple enough.

The Dark Ages, circa 1998

Some pretty amazing castles were built during this age, and they still stand today.

The basic tools had improved a lot, but they were still recognizable. Even an Neanderthal from the previous age could probably be effective with these tools.

The Age of Enlightenment, circa 2002

The gothic cathedrals from around this time look awesome, but were (and still are) inhabited by unpleasant religious fanatics.

At this point, great thinkers with lots of free time combined art and science to build magnificent works of beauty and elegance.

The Age of the Astronauts and the Race for Space (via the Clouds), circa 2008

A lot of very expensive spaceships were built during this age.

The great empires fought a cold, mostly ideological, war to see who could be the first to reach the moon.

Of course this all ended with a ka-boom and the eventual retirement of the space shuttle program.

A New Hope, 2011

Realizing that space tourism and new houses on Mars might be out of reach for most people in the foreseeable future, there is a renewed interest in more lightweight approaches to building and transportation. You might even call it eco-friendly.

Even the otherwise lethargic Microsoft jumps on the bandwagon, to the acclaim of professional developers.

"WebMatrix is full 180 from the highly abstracted cathedral that is ASP.NET. (...) WebMatrix is focused on simplicity and the "Get It Done" developer. Which, to me, is a massive undersell as we're all "Get it Done" developers."

"... you have to use raw SQL to query the database. This is going to turn off the Ivory Tower crowd who prefer to wade through XML Soup and tedious designers - but that's to their detriment. As I've always said - the best DSL I've ever seen for working with data is SQL."

That's cool. This is what web development looks like with WebMatrix:

Looks strangely familiar, doesn't it? A mix of HTML markup and code, and direct data access without any of that ORM stuff? Yes, that's right. For comparison, let's bring up that screenshot from the Dark Ages (1998) again:

Conclusions & Take-Aways

(Because there always needs to be a take-away.)

I am (definitely) not saying that "Frameworks are evil", or that "Progress is uncool". I'm just as lazy as any programmer, but I prefer the simplest thing that could possibly work.

I prefer frameworks that don't try to obscure that what we (most of us) are trying to do (most of the time) is to generate some HTML based on data from an SQL database.

Do we really need something like this

If something like this can do the job?

The golden path is probably somewhere in-between those two extremes. But keep in mind: Stonehenge has lasted for 5,000 years, while the space shuttle is being retired after less than 50 years.

PL/SQL Utility Library

Most programmers have a collection of utilities and code libraries that they re-use for several projects.

I've created the PL/SQL Utility Library page on Google Code to host some generic utilities that I've written myself, some that I've collected from elsewhere (credits and links can be found in the relevant source code), as well as links to useful PL/SQL libraries that are actively maintained elsewhere.

Check out the links and download the source code, there's a lot of different stuff here; from parsing CSV files, integrating with Google Maps and generating JSON, to zipping/unzipping files with PL/SQL.

I plan to add several more packages as soon as I get the code cleaned up.

Drop a comment below if you know of any other PL/SQL libraries or utilities that should be added to the list!

SOAP Server in PL/SQL

Or how to expose PL/SQL packages as SOAP web services using pure PL/SQL

I have blogged before about the various options available for both consuming and exposing SOAP web services using PL/SQL. (And if you don't know what SOAP is, here is a tongue-in-cheek introduction.)

Here is yet another lightweight alternative, a small PL/SQL package that implements a simple SOAP server. It will generate a WSDL document on-the-fly for the packages you want to expose (subject to a whitelist). Functions (only) are invoked using dynamic SQL, and the results are returned in a SOAP envelope. Exceptions are handled using the SOAP Fault mechanism.

I have successfully tested this package on both the Embedded PL/SQL Gateway (DBMS_EPG) on Oracle XE 10g, as well as on Apache/OHS with mod_plsql (tested on a 10g database).

Here is a screenshot showing Web Service Studio used to test the Employee demo service (ie database package):

To try it out, follow these steps:

1. Download the source code
2. Modify the package body to suit your environment (particularly the g_schema_name constant, and the is_whitelisted function)
3. Install the package into your schema
4. If you want to run the package through the Apex DAD, remember to grant execute on soap_server_pkg to anonymous (on EPG) or apex_public_user (on mod_plsql), and modify the request validation function (wwv_flow_epg_include_mod_local) as appropriate. Create a synonym if you don't want to include the schema name in the URL.
5. Use a SOAP client such as Web Service Studio or SoapUI and navigate to http://your-server/dad-name/soap_server_pkg.wsdl?s=your_package_name

Issues and limitations

• This initial version only supports functions that return a single value (varchar2, number, date, clob). Functions returning complex (user-defined) types, object types or array types are not supported. But as the demo package shows, you can return complex values using a single CLOB formatted as XML.
• The OWA toolkit has a 32K limit on the size of CGI environment variables, which means the SOAP request body is similarly restricted. So although you can return responses of any length from your web services, the requests you can receive must be under 32K in length (including the XML tags in the SOAP request envelope).
• The Apex Listener (at least as of the EA release 1.1) differs from the EPG and mod_plsql in that it does not pass the SOAP_BODY request variable to the OWA toolkit, so this solution will not work with the Apex Listener. However, it should be trivial to add to the Listener, so if you would find it useful, then you should file an enhancement request with Oracle and ask for it.

I'll end with a note of caution: This package executes dynamic SQL. While care has been taken to sanitize the input and to implement a whitelist, you should carefully review these security measures in terms of your own environment before you expose your database on the network.

Good news about Oracle XE 11g

Oracle OpenWorld 2010 came and went without any announcement of Oracle 11g Express Edition (XE).

However, according to Bradley D. Brown at TUSC, Oracle has now (re-) confirmed that they are still working on it.

For some time now the ACE Director community has been asking Mark Townsend (product manager for the Oracle DB) when the 11g version of Express was going to be available.  He was consistent in saying that they wouldn't even consider releasing it until 11gR2 was available.  Well...it's available now!  So the ACED community said "OK, Mark..." and he responded with - it's already in the works and should be out...when asked "when..." we got the standard answer.  The good news is that it's coming!  Another piece of good news is that they raised the DB size limit from 4GB to 10GB.  That makes for a nice free DB engine.

This is good news, since it has been a very long time since XE 10g was released. With Microsoft releasing updates to its free version of SQL Server at the same time as the full version, I think Oracle should do the same.


But hey, it's good, it's free, and it's coming eventually, so I'm not complaining! :-)

Proxy Authentication with Thoth Gateway

Christian Vind submitted an enhancement request for the Thoth Gateway to support Oracle proxy authentication by passing on the current Windows username to the database connection string.

The point of proxy authentication is that

• The proxy user only has "create session" privileges but can't do much else.
• The real user does not have "create session" privileges and cannot log on to the database without knowing the proxy user name and password (and that is only set on the web/application server).
• The USER function returns the real user name, and all standard database auditing, roles, etc. work as usual.

As of version 1.3 of the Thoth Gateway, proxy authentication is now supported. Here is how it works:


IIS Setup

Set up the application (virtual directory) in IIS where the gateway runs with Integrated Windows Authentication, so that the CGI environment variable LOGON_USER will be populated with the client's Windows username. (If the user is using Internet Explorer to browse the site, his identity will be passed on to the web server/gateway automatically; if using another browser, then an explicit logon is required.)

Oracle Setup

Define an "application server user", ie the common user that connections will be established through:


-- Log on as DBA (SYS or SYSTEM) that has CREATE USER privilege.


create user appserver identified by eagle;



create user end_user identified by secret;
grant create session to end_user;
alter user end_user grant connect through appserver;




Now test the setup with SQL*Plus, by connecting with the "application server user", and then "becoming" the end user:



-- note we don't specify the end_user password, but still become that user


SQL> connect appserver[end_user]/eagle
Connected.
SQL> select user from dual;


USER
------------------------------
END_USER


SQL>

Note that since the point of this is to take advantage of existing Active Directory accounts, you probably want to create your users like this:

create user "your_domain\end_user" identified externally;

grant create session to "your_domain\end_user";

alter user "your_domain\end_user" grant connect through appserver;

Thoth Gateway Setup

In web.config, modify the DAD settings (the following example assumes a local Oracle XE installation):


param name="DatabaseConnectString" value="//127.0.0.1:1521/xe"
param name="DatabaseConnectStringAttributes" value="Enlist=false;Proxy User Id=appserver;Proxy Password=eagle;"
param name="DatabaseUserName" value="LOGON_USER"
param name="DatabasePassword" value=""


Notice the value "LOGON_USER" specified for the DatabaseUserName parameter. This is a reserved string that will be replaced with the actual value of the LOGON_USER value from the web request (ie. the user's Windows username, typically "domain\username"). You can also specify "LOGON_USER_NO_DOMAIN" to strip away the domain part of the user name -- what you use will depend on how you have set up your user accounts in Oracle.

Testing It

To test that everything works at this point, create a procedure similar to the following, and execute it via the gateway (don't forget to grant execute privileges on it to the end-user's account, and create a public synonym for it unless you prefix with the procedure owner's name in the URL).


procedure test_proxy_auth
as
begin


  htp.header(1, 'Proxy authentication');


  htp.ulistopen;
  htp.listitem ('USER = ' || user);
  htp.listitem ('Proxy user = ' || sys_context('userenv', 'proxy_user'));
  htp.listitem ('CGI LOGON_USER = ' || owa_util.get_cgi_env('LOGON_USER'));
  htp.ulistclose;


end test_proxy_auth;

If successful, the USER function should return the end-user's Windows username, and the Proxy User should display as "appserver".

Postscript: A little enigma

Actually, if you do as described above, you could possibly get this error when you try to run the procedure via the gateway:

ORA-1045: user %s lacks CREATE SESSION privilege; logon denied

At least, that's what I got . To get around it, I had to explicitly grant this to the "appserver" user:

grant create session to appserver;

The funny thing is that my example above, tested via SQL*Plus, shows that this works without the grant! But when attempting the same connection via ODP.NET, it gives the above error unless the grant is made.

And if I revoke the "create session" from the end_user, the above example doesn't work in SQL*Plus, because of the missing privilege. Which seems to contradict the purpose of proxying, as defined at the top of this blog post.

If anybody knows why SQL*Plus and ODP.NET show different behaviour here, please let me know.

Thoth Gateway version 1.3 available

There is a new version of the Thoth Gateway, a mod_plsql replacement for IIS, available for download. The latest version is 1.3.

It contains the following bug fixes and enhancements:

• Bug Fix: Issue with parsing client IP address: Added exception handling to prevent error when parsing client IP address with invalid format.
• Ignore additional request parameters: Certain tools and frameworks may dynamically add additional parameters to a request, which causes the corresponding PL/SQL call to fail, since these parameters are not defined in the procedure signature. As of this version, the gateway will now retry the call after dropping (ignoring) any parameters that cannot be found in the Oracle data dictionary for the procedure being called.
• Support for Oracle proxy authentication (and Single Sign On) via dynamic username substitution: Oracle proxy authentication, combined with Integrated Windows Authentication in IIS, allows you to pass the end-user's identity from the client to the database session (so the function USER will return the end-user's Windows username, with no login required). This is useful in an intranet scenario where users are defined in an Active Directory domain and use Internet Explorer to access the PL/SQL web application.

For existing installations, simply overwrite the existing PLSQLGatewayModule.dll file in the "bin" folder with the latest version from the downloaded archive.

Apex on Thoth Gateway and IIS 7

Several people have asked for instructions on how to run the Thoth Gateway (a mod_plsql replacement) on Microsoft Internet Information Server (IIS) 7.

I finally had the chance (and the time!) to test the configuration on a server running IIS 7. The biggest challenge was understanding the new administration console user interface for IIS 7; it was was slightly confusing for someone who is used to IIS 6.

After a bit of fiddling, I got the gateway up and running:

I have updated the installation instructions in the latest download package (version 1.2.1) with separate sections for IIS 6 and IIS 7. So download, unzip and read the instructions in the "doc" folder.

And leave a comment if you run into problems (or leave a comment if you managed to get it up and running using the instructions!).