Forum Stats

  • 3,759,907 Users
  • 2,251,613 Discussions
  • 7,870,862 Comments

Discussions

New authentication scheme getting 403 (Forbidden)

Hawk333
Hawk333 Member Posts: 126
edited May 7, 2017 5:51AM in APEX Discussions

In my App, I used this plugin as an authorization scheme, where when I click login, it should take me to Google account to perform login. However, I am getting (403 Forbidden) error as you can see.

Capture.PNG

From my previous question here, I think it has to do with Wallet and ACL settings. However, I do not have any idea on how to investigate the problem. What are the steps I should take in order to check whether ACL is set correctly or not, and how to debug that. Is there any logging method to help identify ACL problem.

Hawk333

Answers

  • Scott Wesley
    Scott Wesley Member Posts: 6,089 Gold Crown
    edited May 2, 2017 9:23PM

    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

    Let's Wreck This Together...with Oracle Application Express!: Application Express, Network ACLs and Oracle Database 11gR…

    And an example of all you'd need

    https://gist.github.com/ajin/fdd8167799f9d307537d

    Hawk333
  • Scott Wesley
    Scott Wesley Member Posts: 6,089 Gold Crown
    edited May 2, 2017 9:25PM
  • Pavel_p
    Pavel_p Member Posts: 2,305 Gold Trophy
    edited May 3, 2017 5:23AM

    Hi,

    just to expand a bit information already provided by Scott... Please, read this about ACLs (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;

    Regards,

    Pavel

    Hawk333
  • Hawk333
    Hawk333 Member Posts: 126
    edited May 3, 2017 10:22AM

    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:

    Capture1.PNG

    From my understanding I cannot access https://accounts.google.com from http://my-host.com due to the SSL issue. Am I correct?

    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.xmlAPEX_050100
    /sys/acls/acl_test_for_tests.xmlAPEX_PUBLIC_USER
    /sys/acls/acl_test_for_tests.xmlAPEX_REST_PUBLIC_USER
    /sys/acls/acl_test_for_tests.xmlAPEX_LISTENER
    /sys/acls/acl_test_for_tests.xmlTEST

    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

  • Hawk333
    Hawk333 Member Posts: 126
    edited May 3, 2017 10:10AM

    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?

  • Pavel_p
    Pavel_p Member Posts: 2,305 Gold Trophy
    edited May 4, 2017 4:20AM

    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).

  • Hawk333
    Hawk333 Member Posts: 126
    edited May 5, 2017 9:54AM

    Hi Pavel, Thank you very much for trying to help. Below is what I tried:

    1. I checked the table S4SA_SETTINGS, and set the value of S4SA_API_PREFIX to https:// , and I got the same error above
    2. I enabled the debugging. Below is the debug trace from APEX:
    0.001000.00100Reset NLS settings
    4

    8%

    05-MAY-17 01.35.12.308000 PM +00:000.002000.00000
    alter session set  NLS_COMP='BINARY' NLS_SORT='BINARY' NLS_CALENDAR='GREGORIAN' NLS_TERRITORY='AMERICA' NLS_LANGUAGE='AMERICAN'
    4

    0%

    05-MAY-17 01.35.12.309000 PM +00:000.002000.00000
    ...NLS: Set Decimal separator="."
    4

    0%

    05-MAY-17 01.35.12.309000 PM +00:000.002000.00000
    ...NLS: Set NLS Group separator=","
    4

    0%

    05-MAY-17 01.35.12.309000 PM +00:000.002000.00000
    ...NLS: Set g_nls_date_format="DD-MON-RR"
    4

    0%

    05-MAY-17 01.35.12.309000 PM +00:000.002000.00000
    ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"
    4

    0%

    05-MAY-17 01.35.12.309000 PM +00:000.002000.00100
    ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"
    4

    8%

    05-MAY-17 01.35.12.309000 PM +00:000.003000.00000
    NLS of database and client differs, characterset conversion needed
    4

    0%

    05-MAY-17 01.35.12.310000 PM +00:000.003000.00000
    ...Setting session time_zone to +00:00
    4

    0%

    05-MAY-17 01.35.12.310000 PM +00:000.003000.00000
    R E Q U E S T accept GGL_LOGIN
    4

    0%

    05-MAY-17 01.35.12.310000 PM +00:000.003000.00100
    Metadata: Fetch application definition and shortcuts
    4

    8%

    05-MAY-17 01.35.12.310000 PM +00:000.004000.00000
    Reset NLS settings
    4

    0%

    05-MAY-17 01.35.12.311000 PM +00:000.004000.00000
    alter session set  NLS_COMP='BINARY' NLS_SORT='BINARY' NLS_CALENDAR='GREGORIAN' NLS_TERRITORY='AMERICA' NLS_LANGUAGE='AMERICAN'
    4

    0%

    05-MAY-17 01.35.12.311000 PM +00:000.004000.00000
    ...NLS: Set Decimal separator="."
    4

    0%

    05-MAY-17 01.35.12.311000 PM +00:000.004000.00000
    ...NLS: Set NLS Group separator=","
    4

    0%

    05-MAY-17 01.35.12.311000 PM +00:000.004000.00000
    ...NLS: Set g_nls_date_format="DD-MON-RR"
    4

    0%

    05-MAY-17 01.35.12.311000 PM +00:000.004000.00000
    ...NLS: Set g_nls_timestamp_format="DD-MON-RR HH.MI.SSXFF AM"
    4

    0%

    05-MAY-17 01.35.12.311000 PM +00:000.004000.00000
    ...NLS: Set g_nls_timestamp_tz_format="DD-MON-RR HH.MI.SSXFF AM TZR"
    4

    0%

    05-MAY-17 01.35.12.311000 PM +00:000.004000.00000
    ...Setting session time_zone to +00:00
    4

    0%

    05-MAY-17 01.35.12.311000 PM +00:000.004000.00100
    NLS: wwv_flow.g_flow_language_derived_from=0: wwv_flow.g_browser_language=en
    4

    8%

    05-MAY-17 01.35.12.311000 PM +00:000.005000.00000
    Authentication check: S4S oAuth2 (PLUGIN_NL.S4S.OAUTH2)
    4

    0%

    05-MAY-17 01.35.12.312000 PM +00:000.005000.00000
    ... sentry+verification success
    4

    0%

    05-MAY-17 01.35.12.312000 PM +00:000.005000.00000
    ...Session ID 2397075867086 can be used
    4

    0%

    05-MAY-17 01.35.12.312000 PM +00:000.005000.00000
    Session State: fetch from database (exact)
    4

    0%

    05-MAY-17 01.35.12.312000 PM +00:000.005000.00000
    ...Setting session time_zone to +00:00
    4

    0%

    05-MAY-17 01.35.12.312000 PM +00:000.005000.00100
    ...Check for session expiration:
    4

    8%

    05-MAY-17 01.35.12.312000 PM +00:000.006000.00000
    ...Metadata: Fetch Page, Computation, Process, and Branch
    4

    0%

    05-MAY-17 01.35.12.313000 PM +00:000.006000.00000
    ...Parse JSON
    4

    0%

    05-MAY-17 01.35.12.313000 PM +00:000.006000.00100
    ...Execute Statement: begin apex_debug.enable(); end;
    4

    8%

    05-MAY-17 01.35.12.313000 PM +00:000.007000.00000
    ...Check authorization security schemes
    4

    0%

    05-MAY-17 01.35.12.314000 PM +00:000.007000.00000
    Session State: Save form items and p_arg_values
    4

    0%

    05-MAY-17 01.35.12.314000 PM +00:000.007000.00000
    Processes - point: ON_SUBMIT_BEFORE_COMPUTATION
    4

    0%

    05-MAY-17 01.35.12.314000 PM +00:000.007000.00000
    Branches - point: BEFORE_COMPUTATION
    4

    0%

    05-MAY-17 01.35.12.314000 PM +00:000.007000.00000
    Process point: AFTER_SUBMIT
    4

    0%

    05-MAY-17 01.35.12.314000 PM +00:000.007000.00000
    Tabs: Perform Branching for Tab Requests
    4

    0%

    05-MAY-17 01.35.12.314000 PM +00:000.007000.00000
    Branches - point: BEFORE_VALIDATION
    4

    0%

    05-MAY-17 01.35.12.314000 PM +00:000.007000.00000
    Validations:
  • Pavel_p
    Pavel_p Member Posts: 2,305 Gold Trophy
    edited May 5, 2017 5:39PM

    Hi,

    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.

    Regards,

    Pavel

  • Hawk333
    Hawk333 Member Posts: 126
    edited May 7, 2017 5:51AM

    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 );

    with this:

    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

This discussion has been closed.