8 Replies Latest reply on Jun 1, 2017 2:54 PM by giannid

    How can we configure ORDS to allow cross origin resource sharing for the PL/SQL gateway functionality?

    user11978485

      We are using ORDS to provide the PL/SQL gateway facilities that used to be provided by mod_plsql. But ORDS has to be deployed to a different domain from the web page that wants to invoke the PL/SQL function via a POST command. We are using a Chrome browser, and it is unable to access the PL/SQL via ORDS because it does not have cross origin resource sharing set to allow it.

       

      I've found in the document "ORDS Installation, Configuration, and Development Guide" the PL/SQL function ORDS.SET_MODULE_ORIGINS_ALLOWED, which looks like it might do the job, but if we have to use that, what module do we specify to set the origins of? We have not created any ORDS modules ourselves, and we are not using APEX, we are just using the default PL/SQL Gateway setup. The ORDS PL/SQL Package Reference does not seem to specify any way to list the modules. I found a table called ORDS_MODULES in the database (in an ords metadata schema), but it seems to be empty so that didn't help. Maybe there isn't a module in our scenario - but in that case how do we specify allowed origins?

       

      This is ORDS version 3.0.6. Strangely enough the problem doesn't happen in version 3.0.2. Maybe that older version just ignores CORS altogether, which I suppose would have been a deficiency, but 3.0.6 is only an improvement if we can find the way to specify the allowed origins. We obviously can't afford to be stuck on version 3.0.2 for ever.

       

      In case it matters ORDS is deployed on a Weblogic 10.3.6 server. (I've seen another thread in which someone had a problem with CORS when deployed on Apache and Tomcat which required a change to their configuration too, so I suppose the same might be true of weblogic, but haven't been able to find anything about it - and the ORDS Installation, Configuration, and Development Guide section C.8.6 about CORS seems to be just saying that it is an ORDS configuration matter.)

        • 2. Re: How can we configure ORDS to allow cross origin resource sharing for the PL/SQL gateway functionality?
          PCHIU

          I am having the same problem when I upgrade from apex listener v2 to ords 3.0.8. It happened in Chrome but not Firefox.

           

          The request cannot be processed because this resource does not support Cross Origin Sharing requests, or the request Origin is not authorized to access this resource. If ords is being reverse proxied ensure the front end server is propagating the host name, for mod_proxy ensure ProxyPreserveHost is set to On

           

          I added the followings to tomcat 7 web.xml and the problem still there.

           

           

          <filter>
            <filter-name>CorsFilter</filter-name>
            <filter-class>org.apache.catalina.filters.CorsFilter</filter-class>
            <init-param>
            <param-name>cors.allowed.origins</param-name>
            <param-value>*</param-value>
            </init-param>
            <init-param>
            <param-name>cors.allowed.methods</param-name>
            <param-value>GET,POST,HEAD,OPTIONS,PUT</param-value>
            </init-param>
            <init-param>
            <param-name>cors.allowed.headers</param-name>
            <param-value>Content-Type,X-Requested-With,accept,Origin,Access-Control-Request-Method,Access-Control-Request-Headers</param-value>
            </init-param>
            <init-param>
            <param-name>cors.exposed.headers</param-name>
            <param-value>Access-Control-Allow-Origin,Access-Control-Allow-Credentials</param-value>
            </init-param>
            <init-param>
            <param-name>cors.support.credentials</param-name>
            <param-value>true</param-value>
            </init-param>
            <init-param>
            <param-name>cors.preflight.maxage</param-name>
            <param-value>10</param-value>
            </init-param>
          </filter>
          <filter-mapping>
            <filter-name>CorsFilter</filter-name>
            <url-pattern>/*</url-pattern>
          </filter-mapping>

          • 3. Re: How can we configure ORDS to allow cross origin resource sharing for the PL/SQL gateway functionality?
            Erik Raetz

            When using ORDS services under the source type of "plsql/block" you can create your own HTTP headers for the response.

            Would adding Access-Control-Allow-Origin to the response header solve your problem?

             

            Like so:

            PROCEDURE OutputResponse(

              presponse IN OUT NOCOPY CLOB

            )

            IS

            BEGIN

              --response http header

              OWA_UTIL.MIME_HEADER('application/json',FALSE,'utf-8');

              htp.p('Access-Control-Allow-Origin: http://www.example.com');

              OWA_UTIL.HTTP_HEADER_CLOSE;

              --response body

              PrintClob(presponse); --print using htp.prn

            END;

             

            PROCEDURE DoStuff(

              pcontenttype      IN VARCHAR2 DEFAULT NULL,

              poauthclient      IN VARCHAR2 DEFAULT NULL,

              prequest          IN BLOB DEFAULT NULL

            )

            IS

              lrequest CLOB;

              lresponse CLOB;

            BEGIN

              lrequest := BlobToClob(prequest); --convert

              --write request to table

              --...

              --read data from json via jsontable

              --...

              --create output

              lresponse := '{}';

              OutputResponse(presponse => lresponse);

            END;

             

            ORDS Handler

            --POST dev/test

            ords.define_handler(

              p_module_name => 'dev', p_pattern => 'test', p_method => 'POST',

              p_source_type => 'plsql/block',

              p_source => 'DECLARE

            BEGIN

              DoStuff(pcontenttype => :content_type, poauthclient => :current_user, prequest => :body);

            END;',

              p_items_per_page => NULL

            );

             

            Though I'm not sure what you mean you have no modules?

            How did you define your services? Haven't you used ords.define_service to define any service?

            We do not use APEX either and used that procedure to define any of our services for a specific module.

            Our webserver is Tomcat.

             

            You can use this select under the Oracle schema that has ORDS enabled to show the list of modules:

            select * from user_ords_modules order by 2,3;

            • 4. Re: How can we configure ORDS to allow cross origin resource sharing for the PL/SQL gateway functionality?
              rwendel

              I have just finished figuring it out for me. I broke some things on purpose, injected some debugging code and came up with a way that works for me. Specifically I am working with Ellucian Banner - Self Service (SSB) which is an old-style PLSQL Gateway application.

              One of the first things you're looking for is when, and that is 3.0.4 that added this issue.

               

              "Resolve 403 Forbidden Error" When Trying To Access The Apex Application Configured with ORDS 3.0.4 Or Higher Using the Google Chrome Browser (Doc ID 2139195.1)

               

              This note points to some help from the following setting:

               

              <entry key="security.forceHTTPS">true</entry>

               

              It should be noted, however, that when you set this it also defaults the port to 443 only, so if you're using default ports on https as your proxy then try that out. It's something I hope maybe being identified could be changed soon, in addition to plsql gateway management of xss policies.

               

               

               

              | PCHIU

               

              oracle.dbtools.http.cors is the base for ords's CORS policy management it would seem. It doesn't seem that they would read apache tomcat's settings for it (probably because of how the REST connections can be managed in the database), but I think we are just in this donut hole of old-style PL/SQL Gateway DAD replacements.

              • 5. Re: How can we configure ORDS to allow cross origin resource sharing for the PL/SQL gateway functionality?
                rwendel

                And, to add, the easiest solution I am doing is using Tomcat specifically, and using the HTTPD proxy front end with AJP connector, then using  following additional parameters in my AJP connector:

                 

                scheme="https" secure="true" proxyName="website.school.edu" proxyPort="9443"

                 

                This forces servlet calls (.getScheme, .isSecure, .getServerName, and .getServerPort) to return those values above instead of the 'detected' values. Normally PreserveProxyHost would be enough, but the scheme doesn't come across. My guess is I could do without the rest and just set scheme, but now it's just overkill to make sure.

                • 6. Re: How can we configure ORDS to allow cross origin resource sharing for the PL/SQL gateway functionality?
                  user11978485

                  I suppose I should update this, in case other people have the same problem. Basically, Oracle support said that they are never going to bring back the ability to do cross-origin resource sharing in the ORDS PL/SQL Gateway functionality, which lost this ability in ORDS version 3.0.2. Their suggestion was to give up on the ORDS inbuilt PL/SQL Gateway facility altogether and resort to creating REST services to provide the equivalent capability ourselves.

                  • 7. Re: How can we configure ORDS to allow cross origin resource sharing for the PL/SQL gateway functionality?
                    3418561

                    How can this issue be fixed, if we use oracle iplanet web server in front?

                     

                    Thanks

                    • 8. Re: How can we configure ORDS to allow cross origin resource sharing for the PL/SQL gateway functionality?
                      giannid

                      Try set Origins Allowed in the Service Module definition as a comma separated list of origins.

                      Only notice that the usual wildchar "*" does not work!

                      So i had to specify my own origin.

                      E.g. if you are using postman for testing  insert chrome-extension://fhbjgbiflinjbdggehcddcbncdddomop in the origin list