2 Replies Latest reply on Jan 8, 2019 7:07 PM by d3bruts1d

    ORDS 18.3 - Trouble with jdbc.auth.enabled

    d3bruts1d

      DB Server:

      • OS - RHEL 7.5
      • DB - DBEE 18.4
      • Single instance, no grid, ASM, etc.
      • APEX - 18.2

       

      APP Server (different host from DB):

      • OS - RHEL 7.5
      • WLS/OHS - 12.2.1.3
      • SSL & mod_wl_ohs
      • EnforceValidBasicAuthCredentials = false
      • ORDS 18.3 (deployed managed server - WLS_ORDS)

       

      I'm in the process of migrating one of our ancient systems to a supported platform. The old system had APEX and 2 other apps deployed via mod_plsql/DAD. So, these are being moved over to ORDS. I've installed and configured APEX with ORDS and can access it without any issue. I've created the additional "database pools" for use with the other applications that are being migrated from the DAD. Once configured, these two apps display as expected... https://server/ords (APEX); https://server/app1 and https://server/app2.

       

      I was good until I found out the 2 legacy apps used a custom, in-house, long abandoned, authentication process. So I look into how to configure these two apps to authenticate using DB accounts, and come across the "jdbc.auth.enabled" configuration. When I fist enabled this, I was getting the expected login dialogue but after successful authentication ORDS was giving me a 500 error (related to CRLF in the header). After using httpdebug on the server, I found the issue to be the "connection accounts" lacked create session privileged. I resolved that, but now I am no longer getting prompted for login (no, I did not save the login info)... no matter how many times I restart ORDS, OHS, the DB, or close my browser (or clear my cache/temp files). I'm at a loss as to why I am not being prompted to login.

       

      Also.. when it was "working", I noticed that if I went to one app and logged in and then when to the 2nd app, it did not prompt me. This was undesirable (each app should prompt). I can deal with this issue later.

       

      I have reviewed/used the following MOS Docs so far...

      Doc ID 2366419.1

      Doc ID 2409263.1

      Doc ID 2228856.1

      Doc ID 2316603.1

      Doc ID 2373039.1

       

      Configuration files...

       

      defaults.xml

      <?xml version="1.0" encoding="UTF-8" standalone="no"?>
      <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
      <properties>
      <comment>Saved on Thu Oct 11 07:54:13 EDT 2018</comment>
      <entry key="cache.caching">false</entry>
      <entry key="cache.directory">/u01/app/oracle/ords/tmp/cache</entry>
      <entry key="cache.duration">minutes</entry>
      <entry key="cache.expiration">15</entry>
      <entry key="cache.maxEntries">500</entry>
      <entry key="cache.monitorInterval">60</entry>
      <entry key="cache.procedureNameList"/>
      <entry key="cache.type">lru</entry>
      <entry key="db.hostname">my.server.com</entry>
      <entry key="db.port">1521</entry>
      <entry key="db.servicename">devl</entry>
      <entry key="debug.debugger">true</entry>
      <entry key="debug.printDebugToScreen">true</entry>
      <entry key="error.keepErrorMessages">true</entry>
      <entry key="error.maxEntries">50</entry>
      <entry key="jdbc.DriverType">thin</entry>
      <entry key="jdbc.InactivityTimeout">1800</entry>
      <entry key="jdbc.InitialLimit">3</entry>
      <entry key="jdbc.MaxConnectionReuseCount">1000</entry>
      <entry key="jdbc.MaxLimit">10</entry>
      <entry key="jdbc.MaxStatementsLimit">10</entry>
      <entry key="jdbc.MinLimit">1</entry>
      <entry key="jdbc.statementTimeout">900</entry>
      <entry key="log.logging">false</entry>
      <entry key="log.maxEntries">50</entry>
      <entry key="misc.compress"/>
      <entry key="misc.defaultPage">apex</entry>
      <entry key="security.disableDefaultExclusionList">false</entry>
      <entry key="security.maxEntries">2000</entry>
      </properties>
      
      

       

      conf/apex.xml (the other apex*.xml files are basically the same):

      <?xml version="1.0" encoding="UTF-8" standalone="no"?>
      <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
      <properties>
      <comment>Saved on Thu Oct 11 07:54:13 EDT 2018</comment>
      <entry key="db.password">@05B3CA46EDB8850FFA9EE042847B886122864D45975A7B138E</entry>
      <entry key="db.username">APEX_PUBLIC_USER</entry>
      <entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>
      <entry key="security.validationFunctionType">plsql</entry>
      </properties>
      

       

      conf/app1.xml (app2 is the same with different user/pass and default page):

      <?xml version="1.0" encoding="UTF-8" standalone="no"?>
      <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
      <properties>
      <comment>Saved on Wed Dec 12 09:59:31 EST 2018</comment>
      <entry key="db.password">@05E00561B07E32E69084EF5A3C47A181B3CB08C4B9D2A6C36046D9C28A29B8C750</entry>
      <entry key="db.username">APP1USER</entry>
      <entry key="jdbc.auth.enabled">true</entry>
      <entry key="misc.defaultPage">app1.main</entry>
      </properties>
      

       

       

      So... does anyone have any thoughts on why, despite the jdbc.auth.enabled setting, I am not being prompted to log in? Do the devs need to update their apps (obviously yes... ) to send the browser a request to authenticate? I know very little about these apps and what they are actually doing.

        • 1. Re: ORDS 18.3 - Trouble with jdbc.auth.enabled
          Nigel-M

          We had a similar issue. Have you checked the privileges in DBA_TAB_PRIVS that APP1USER has against the package/procedure that you're calling in the request?

           

          That user shouldn't have any execute privileges on the object - if they do, it bypasses the auth.enabled stuff and connects directly as that user. That includes "public" execute privileges.

           

          (I've just noticed that this was already stipulated in the Doc ID 2409263.1)

           

          ..

          3. The database user configured for ORDS to initially connect with the database should not have any relation to the procedure (i.e not privilege to execute it).  But must be an open and valid db user schema (i.e scott).

          ..

           

           

          N

          • 2. Re: ORDS 18.3 - Trouble with jdbc.auth.enabled
            d3bruts1d

            Thank you!

             

            I had seen that and given it a try, however when I revoked the privileged I ended up getting a 500 internal server error. The 500 ISE was actually my fault... I created a simple "hello world" procedure to test with. The procedure I was calling was not specified in the pool specific security.inclusionList. Added the procedure, restarted the managed server, and was prompted as expected.