2 Replies Latest reply on Feb 5, 2016 8:16 PM by jflack

    Run Web PL/SQL from an ADF Application


      I have some reports that are written in Web PL/SQL.  No REST Services, no APEX - just calls to the HTP, HTF and OWA_* packages.  What they really are is PL/SQL Server Pages compiled to PL/SQL procedures with loadpsp.


      Up to now, we run these reports through a report server - used to be an Oracle HTTP Server running mod_plsql.  Currently, it is on a server running an older version of the Application Express Listener.  The reports are called from a link in an Oracle ADF 12.1.3 application.


      However, while the ADF application uses authentication and authorization, the report server does not.  That means that if you know the URL for one of the reports, and if you know one of the PKs for a row in the database that the report uses as an input parameter, you can run the report.  Security by obscurity - not likely that you'll know what you need to know but not impossible.  The boss says we need to get these reports under the same authentication and authorization controls as the ADF application.


      One way to do it might be to use a Single Sign On for both the ADF application and the report server.  But I don't think we want to do this - at least we aren't ready now to switch our Identity infrastructure.


      But - could we somehow add ORDS as an additional servlet inside the ADF application?  I noticed that ORDS comes with Javadocs for an API - which I assume means that it is supported to call relevant ORDS classes from a Java application.  But I can't find much documentation on this or how to use it.

      Or does someone have a better solution?

        • 1. Re: Run Web PL/SQL from an ADF Application
          Dietmar Aust

          Hi John,


          yes, the best approach would certainly involve setting up some sort of SSO, either using a login server or even using a reverse proxy with Apache / IIS or something to authenticate against AD with kerberos (what I use frequently with APEX) and forward all requests to a J2ee app server behind it.


          But for a "poor man" scenario I would shoot for a token based implementation.


          I assume that the ADF application and your pl/sql based reporting application use the identical database in the background. Therefore when clicking on a report (link or button) in your ADF application the following flow will be started:

          • the current adf session id and a random request id will be registered in a reporting request table. Each reporting request will only be valid for a few seconds / minutes.
          • the adf application redirects the user to the reporting pl/sql app and passes the adf session id and the reporting request id as parameters.
          • the reporting pl/sql application will check the valid entry in the reporting request table (time not expired and entry exists).
          • the entry in the reporting table will be deleted or invalidated
          • the report will be rendered successfully


          This approach should make sure that the current ADF session is used and that the request is executed only ONCE. So even if anybody "saw" the url and the tokens the report cannot be run again, thus a replay is not possible.


          Hope that helps,


          1 person found this helpful
          • 2. Re: Run Web PL/SQL from an ADF Application

            Interesting idea, Dietmar.  Take a little work to implement, but certainly doable.  I wonder if the boss would consider it an acceptable solution.  I'm still hoping that one of the ORDS developers or product managers will respond to the question about ORDS as a servlet,