Thursday, April 13, 2017

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.






Thursday, January 12, 2017

How to add an NCSA style Access Log to ORDS Standalone

What ORDS Standalone is


     ORDS Standalone webserver which is Eclipse Jetty, https://eclipse.org/jetty/ .  For the standalone, ORDS sends output to STDOUT, it runs on the command line.  That means there's nothing like a control commands like startup, shutdown,status nor log files, access logs.  It's bare bones intentionally to get up and running fast.  Then it's recommended for anything with lots of volume more to a more full featured webserver like Tomcat or WLS.


Standalone Startup

    With all that said, it is quite simple to use the standalone for most cases.  When doing this the most important thing is just a startup script so that when the machine/db starts the ORDS instance starts also.  There's an init.d script in this blog post that can be used and adjusted as needed : http://krisrice.blogspot.com/2010/12/listener-startupshutdown-script.html


Standalone Access Logs

  The other thing much needed from a standalone instance of ORDS would be an access log to see what it's getting for traffic.  Here's where being Jetty based is a great thing.

  This is documented for some solaris workaround only so probably missed by most.  ORDS Standalone allows for customization of the Jetty server itself.  Looking at the ords configuration directory there's a <path>/standalone.  This will have a file named standalone.properties which looks like this which is the name/value pairs of what ORDS will configure.

#Thu Jan 12 09:56:19 EST 2017
jetty.port=8081
standalone.access.log=/tmp/ords_log
standalone.context.path=/ords
standalone.doc.root=/Users/klrice/workspace_ords_3.0.x/klr_vm_config/ords/standalone/doc_root
standalone.scheme.do.not.prompt=true
standalone.static.context.path=/i
standalone.static.do.not.prompt=true
standalone.static.path=/Users/klrice/workspace/apex_trunk/images

Jetty is configurable by xml documents and the documentation has some really good example of what's possible.  This blog post is going to focus on the Access Log section here: https://eclipse.org/jetty/documentation/current/configuring-jetty-request-logs.html

In that same directory, create a folder named "etc" and in there create a file named "jetty-http.xml" . Then drop this into the file

<?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">
    <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>



Now when ORDS is started again you'll have a file that was specified in the xml file.  This example is  /tmp/ords-access-2017_01_12.log  with an normal NCSA/Apache looking access log.

0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:32 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:33 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:33 +0000] "GET /ords/klrice/test/morse?text=3362 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:33 +0000] "GET /ords/klrice/test/morse?text=5728 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx.%29%29%22%29.%28%2C%27%2C HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%27jSwGPl%3C%27%22%3EbBKXcr HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%29%20AND%208651%3D9722%20AND%20%289950%3D9950 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%29%20AND%203741%3D3741%20AND%20%285130%3D5130 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%20AND%204853%3D4504 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%20AND%203741%3D3741 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%27%29%20AND%201525%3D5863%20AND%20%28%27aKGY%27%3D%27aKGY HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%27%29%20AND%203741%3D3741%20AND%20%28%27jcNS%27%3D%27jcNS HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%27%20AND%204691%3D5794%20AND%20%27rhDV%27%3D%27rhDV HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:34 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%27%20AND%203741%3D3741%20AND%20%27FJPb%27%3D%27FJPb HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:35 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%25%27%20AND%206423%3D5178%20AND%20%27%25%27%3D%27 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:35 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%25%27%20AND%203741%3D3741%20AND%20%27%25%27%3D%27 HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:35 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%20AND%209677%3D8181--%20embz HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:35 +0000] "GET /ords/klrice/test/morse?text=de%20n1jlx%20AND%203741%3D3741--%20ldng HTTP/1.1" 200 - 
0:0:0:0:0:0:0:1 -  -  [12/Jan/2017:15:22:35 +0000] "GET /ords/klrice/test/morse?text=%28SELECT%20%28CASE%20WHEN%20%284135%3D9257%29%20THEN%204135%20ELSE%204135%2A%28SELECT%204135%20FROM%20INFORMATION_SCHEMA.PLUGINS%29%20END%29%29 HTTP/1.1" 200 - 




Summary

Here's the simple steps:
1- mkdir <ords config directory>/standalone/etc
2- vi jetty-http.xml
3- paste the above xml file and adjust the log location
4- Restart ORDS

Wednesday, November 30, 2016

SQLcl custom Input prompt and validations

Another quick twitter inspired blog post inspired by the SQLcl account itself.



ACCEPT is a great way to get input but validation has to happen elsewhere as there's no hooks to do something like validate a number in a range without running a sql or plsql that does the validation.

As I've covered in many examples on this blog and in our github repo of examples, SQLcl has the ability to do scripting.  Using a few lines of javascript, I can prompt, validate, loop waiting for a better answer from the user.  This is a very simple example that loops waiting for the correct answer printing a message with each failure.  While this is trivial example it shows how easy it could be to have a custom script that prompts for values then with some scripting validate those values and put it into SQLcl's list of variable used for substitutions.












Thursday, November 17, 2016

ECMA Script 6 / Nashorn / Java 9 and SQLcl

This blog post brought to you by the letter M as in Martin .  . He asked me on twitter if SQLcl via Nashorn could use ECMA Script 6 features yet.  The answer is yes.  So, for the brave that want to try out the latest greatest ECMA Script 6 features it can be done with SQLcl.

This is a good reference for what's in version 6 that could be useful.  The only caution is not everything is built into nashorn quite yet.  For Example destructuring is not there.
   http://es6-features.org/#StringInterpolation

The Template Literals are probably to me the most interesting thing since it cleans up all things like string concats and multi line string.


Here's how to get it working:
Step 1: Get Java 9 https://jdk9.java.net/download/
Step 2: Set the enviroment variable : APP_VM_OPTS="-Dnashorn.args=--language=es6"
Step 3: Go.

Complete side note as you can see in my default PS1, apple doesn't and has never understood you don't add an 's to a noun ending in a s.

kriss-MacBook-Pro:bin klrice$ export APP_VM_OPTS="-Dnashorn.args=--language=es6"
kriss-MacBook-Pro:bin klrice$ ./sql klrice/klrice

SQLcl: Release 4.2.0 Production on Thu Nov 17 09:22:35 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Thu Nov 17 2016 09:22:36 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> script /Users/klrice/workspace/raptor_common/examples/sql.js
Using Binds:[object Object]

  ***************************************************************
  *****    SIMPLE LOOP OF LIST OF LIST                ***********
  ***************************************************************
 OBJECT_TYPE    OBJECT_NAME 
 TABLE    EMPLOYEES 



  ***************************************************************
  *****    SIMPLE LOOP OF LIST OF NAMES WITH BINDS     **********
  ***************************************************************
 TABLE    EMPLOYEES  
SQL> 


Now this probably looks just like the script from my first blog on how to use javascript http://krisrice.blogspot.in/2015/10/sqlcl-oct-13th-edition.html


However the script itself is much nicer to read ( to me anyway ) . What will stand out is multi line string with using a back tick.  Also is the evaluating of variables inside the quoted string. No more string concatenations!




Disqus for Kris' Blog