ORDS Standalone and URI Rewrites

My last post How to add an NCSA style Access Log to ORDS Standalone explained what the ORDS standalone is and that is based on Eclipse Jetty.  Jetty offers far more than ORDS exposed in it's standalone.  There's a long list of all the features and configuration options listed in the documentation, http://www.eclipse.org/jetty/documentation/9.2.21.v20170120/

A recent question came up for doing URL rewrites.  Jetty does offer this as well.  To take advantage of it the same jetty-http.xml file from my last post just needs a few more lines of xml added.

This example will be just a simple one that rewrites /catalog to /ords/klrice/metadata-catalog/ The better usage of this would be to have / redirect into an APEX application or some home page of the application.

The full list of options are listed in the Jetty Documentation 

http://www.eclipse.org/jetty/documentation/9.2.21.v20170120/rewrite-handler.html

<?xml version="1.0"?>
<!DOCTYPE Configure PUBLIC "-//Jetty//Configure//EN" "http://www.eclipse.org/jetty/configure.dtd">
<Configure id="Server" class="org.eclipse.jetty.server.Server">

<!-- REWRITE -->
  <Get id="oldhandler" name="handler"/>
  <Set name="handler">
   <New id="Rewrite" class="org.eclipse.jetty.rewrite.handler.RewriteHandler">
    <Set name="handler"><Ref refid="oldhandler"/></Set>
    <Set name="rewriteRequestURI"><Property name="rewrite.rewriteRequestURI" default="true"/></Set>
    <Set name="rewritePathInfo"><Property name="rewrite.rewritePathInfo" default="false"/></Set>
    <Set name="originalPathAttribute"><Property name="rewrite.originalPathAttribute" default="requestedPath"/></Set>
   </New>
  </Set>
  <Ref refid="Rewrite">

<!-- REWRITE of /catalog ---- /ords/klrice/metadata-catalog/ -->



<Call name="addRule">
         <Arg>
           <New class="org.eclipse.jetty.rewrite.handler.RewritePatternRule">
             <Set name="pattern">/catalog</Set>
             <Set name="replacement">/ords/klrice/metadata-catalog/</Set>
           </New>
         </Arg>
       </Call>
</Ref>

<!-- HTTP ACCESS LOGS -->
  <Ref id="Handlers">
    <Call name="addHandler">
      <Arg>
        <New id="RequestLog" class="org.eclipse.jetty.server.handler.RequestLogHandler">
          <Set name="requestLog">
            <New id="RequestLogImpl" class="org.eclipse.jetty.server.NCSARequestLog">
              <Set name="filename"><Property name="jetty.logs" default="/tmp/"/>ords-access-yyyy_mm_dd.log</Set>
              <Set name="filenameDateFormat">yyyy_MM_dd</Set>
              <Set name="retainDays">90</Set>
              <Set name="append">true</Set>
              <Set name="extended">false</Set>
              <Set name="logCookies">false</Set>
              <Set name="LogTimeZone">GMT</Set>
            </New>
          </Set>
        </New>
      </Arg>
    </Call>
  </Ref>
</Configure>

The result is as expected.  This could be used for anything from shorter REST points like this catalog example or nicer entry points into APEX application.



The example for an APEX URL would be:

<!-- REWRITE of /awesomeapp ---- /ords/f?p=105:2:::::: -->

<Call name="addRule">
 <Arg>
   <New class="org.eclipse.jetty.rewrite.handler.RewritePatternRule">
     <Set name="pattern">/awesomeapp</Set>
     <Set name="replacement">/ords/f?p=105:2::::::/</Set>
   </New>
 </Arg>
</Call>

DB Auditing and ORDS

There seems to be some confusion around how ORDS works with it's connection pooling yet running the REST call as the specified schema.

The connection pool

Consider a 50 PDB env and concurrent users per PDB running some REST stuff.  Using a connection pool per PDB would be 50 connection pools.  Then if a JET app ( or any HTML5/JS/.. ) is making REST calls Chrome will do this with 6 concurrent calls.  This makes it so that as simple as 5 concurrent users * 6 threads in Chrome * 50 PDBs would reach 1500 connections.

For this reason, in a CDB install the connection pool is established as a common user in the CDB names ORDS_PUBLIC_USER.  This allows for common connection pool that can be shared over all 50 PDBs.  As requested finish, the connection can be recycled back for any PDB to service the next request.

What about Auditing 

This is where the confusion begins.  If it's a common connection pool, it's un-auditable. FALSE

I made a trivial REST api that does nothing more than a select on EMP




Then I turned on Auditing of this table with the following

AUDIT SELECT, INSERT, DELETE
     ON klrice.emp
     BY ACCESS
     WHENEVER SUCCESSFUL;
     

Running the REST call and checking the DB level audit results in exactly what is expected.  There's no notion of the common ORDS_PUBLIC_USER anywhere.  The user KLRICE accessed this table making all normal DB auditing work well normal.

Then why all the FUD?

I think the confusion is that APEX using a different mechanism than ORDS for executing the user's sql with the parse_as options.  When run like this the object audit log shows that the common APEX_PUBLIC_USER is the user that accessed the object and the parse_as user of KLRICE in this case is not present.  However, APEX does populate the CLIENT ID so the audit trail has the runtime user that accessed the object.