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!).

Replacing Apex? More like Find and Replace...

I was getting my daily dose of Apex blog posts when I noticed this advertisement:

I was curious, so I clicked the link and got the following page (http://www.wavemaker.com/solutions/oracleforms.html):

"Well, this is strange", I thought... it says "Oracle Forms" there in the URL, and in the illustration in the middle of the page, yet the advertisement and the page heading talks about replacing "APEX". There is also a claim that the latter "costs a fortune", but as we all know Application Express is a no-cost option in the Oracle database. WTF?

There is also a link to an 8-page whitepaper on "Migrating Oracle Apex Applications to Java" (http://www.wavemaker.com/pdf/Migrating-Oracle-Apex-Apps-To-Java-With-WaveMaker.pdf). This whitepaper includes the following screenshot:

As well as this table:

OK, so it is clearly Oracle Forms that is depicted and described here, but labeled as if it was Oracle Apex.

At best, this is a clueless mistake made by some marketing sod who did a global Find & Replace from FORMS to APEX. At worst, this is deliberately misleading.

In any case, it's just wrong, wrong, wrong. I don't know about you, but I would not trust a company that is either incompetent, dishonest, or both. I'll stick with Apex, thank you.

Small patch for the Thoth Gateway (Apex on IIS)

Just a quick note to announce that a minor patch release (version 1.2.1) of the Thoth Gateway is available for download. The Thoth Gateway allows you to run Oracle Apex applications on the Microsoft IIS web server (a replacement for Apache and mod_plsql).

The following has changed:

• Bug Fix for Content-Length: Fixed an issue where the content-length header would be incorrectly set for non-AL32UTF8 databases if the page contained multibyte characters.
• Set ODP.NET connection string attributes: Added option to specify additional connection string attributes in the DAD configuration. This allows you to fine-tune the connection properties. See the ODP.NET documentation for more details.

Upgrade for existing installations: Simply download the latest version, unzip the archive and drop the PLSQLGatewayModule.dll file into your bin folder on the web server.