Kudos for spawning a new thread.
Check it's not file complaints first
- where your app is hosted
- where supporting files for plugin are used
ie -both that EC2 instance? Are all files on same server?
It's probably the communication itself. I note the plugin refers to an Oracle Wallet.
I can't help you with wallet setup, I have an engineer for that.
Here is a primer on ACLs
And an example of all you'd need
just to expand a bit information already provided by Scott... Please, read this about ACLs Re: Enabling e-mail in Apex 4.2.3.00.08 with Oracle 11g R2 on Windows 2008 Server (you can safely skip the part about sacrificing a goat, it came out that it's not necessary, which is quite surprising, I must admit). Also your DB version is very important, in some older versions including XE you'll have to setup a reverse proxy as described in the link above because SHA2 based certificates are not supported (supposedly used by both Google and FB). If you're on 12c, you should be able to setup a wallet using Oracle Wallet Manager https://docs.oracle.com/database/121/DBIMI/walet.htm#DBIMI160 without the need of reverse proxy. Here https://oracle-base.com/articles/misc/utl_http-and-ssl you can read a detailed procedure how to get server certificates, import them to a wallet and finally verify that it works. Then make sure in APEX administration that the wallet is set properly in your instance settings.
Probably the simplest way to verify if sites are reachable, ACLs and the wallet is configured properly could be a simple select like this (assuming you have execute privileges on utl_http)
select UTL_HTTP.REQUEST('https://secured.site.com',null,'file:/u01/app/oracle/wallet','wallet_password') Output from dual;
I was enabling 'Allow-Control-Allow-Origin' Chrome extension. And it was returning that screen I posted above. I disabled it, and now I am getting this:
Following your advice, I have run the following query:
select UTL_HTTP.REQUEST('https://accounts.google.com/ ',null,'file:C:\app\ORACLE_HOME\product\12.1.0\dbhome_1\owm\wallets\oracle', 'wallet_pwd') Output from dual;
And the site is returned successfully, so I assume the wallet is set up correctly.
I also set the same wallet path and password in APEX administration -> instance settings
Also, I run the following query:
select acl, principal from dba_network_acl_privileges;
and it returned
/sys/acls/acl_test_for_tests.xml APEX_050100 /sys/acls/acl_test_for_tests.xml APEX_PUBLIC_USER /sys/acls/acl_test_for_tests.xml APEX_REST_PUBLIC_USER /sys/acls/acl_test_for_tests.xml APEX_LISTENER /sys/acls/acl_test_for_tests.xml TEST
where TEST is the workspace parsing schema.
Where else do I need to check to find the cause of this error?
I am on 12c
Yes, The DB 12c, web server on the same EC2 instance. The plugin is installed on the workspace parsing schema. From @Pavel_p reply, following the validation queries, it seems to me the wallet is set up correctly (as I replied to him). I am not sure if this is on APEX, or ords side. Is there any way to validate the settings?
Since you're able to succesfully invoke the https call to the target server, we can quite safely assume that ACLs and the wallet is set properly. I took a brief look at the plugins source and (if I'm not terribly mistaken) all the communication is happening between the two servers, e.g. the Oracle database and the target Gooogle/FB...whatever server, so it has nothing in common with your browser settings. The API call is performed purely server-side, so it seems you're digging in the wrong place. The plugin creates a SEPAPEX.S4SA_SETTINGS table where are stored all the necessary settings, so I would suggest to very carefully inspect all the settings first, especially the S4SA_API_PREFIX record. Since your ACLs and the wallet is set properly, you don't need a reverse-proxy and the author recommends "All requests are prefixed with this. use http:// to bypass the reverse proxy", however I think in your case there should be "https://".
Then I suggest to enable debugging for your application (paste apex_debug.enable(); to your application's Shared Components => Security Attributes => Initialization PL/SQL code), run it and inspect the Debug trace (eventually paste it here, maybe it will give us some clues what could be possibly wrong).
Hi Pavel, Thank you very much for trying to help. Below is what I tried:
- I checked the table S4SA_SETTINGS, and set the value of S4SA_API_PREFIX to https:// , and I got the same error above
- I enabled the debugging. Below is the debug trace from APEX:
3. I tried to look into the plugin code, and the last statement I could trace was this:
owa_util.redirect_url ( t_url );
where t_url value was:
I am still getting the same error. Here is my browser console/network request:
unfortunately the debug trace does not seem to be very helpful. The package code does not contain any debug messages whatsoever, however maybe you might find something interesting in the S4SA_REQUESTS table.
I really don't like to give up, but I have no idea what could be possibly wrong, not this way without the ability to "touch" it. The entire thing is really complex and really tough to reproduce the problem because even to reasonably setup the environment is quite a lot of work, moreover I don't have currently access to any 12c instance, which is actually not a big problem as there exists a rebuilt image for Oracle VM with all the necessary stuff. But then set ACLs and a wallet... I'm sending you a friend request, maybe we could find out some other way as this does not lead towards any solution.
Just to update you, I managed to bypass that error (OPTIONS 405), by changing some parts in the plugin code. In particular in package s4sg_auth_pck I replaced the follwing lines
owa_util.redirect_url ( t_url );
apex_util.redirect_url ( t_url );
I followed https://docs.oracle.com/database/122/AEAPI/AEAPI.pdf page 632.
I was redirected to Google account page, and after granting the account permission, I was redirected back to APEX. However, I got Forbidden (403) this time. Which I think has to do with ORDS settings. I posted a separate a question for that here