9 Replies Latest reply on May 7, 2017 9:51 AM by Hawk333

    New authentication scheme getting 403 (Forbidden)

    Hawk333

      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.

        • 1. Re: New authentication scheme getting 403 (Forbidden)
          Scott Wesley

          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

          • 3. Re: New authentication scheme getting 403 (Forbidden)
            Pavel_p

            Hi,

            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;
            

            Regards,

            Pavel

            • 4. Re: New authentication scheme getting 403 (Forbidden)
              Hawk333

              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

              • 5. Re: New authentication scheme getting 403 (Forbidden)
                Hawk333

                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?

                • 6. Re: New authentication scheme getting 403 (Forbidden)
                  Pavel_p

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

                  • 7. Re: New authentication scheme getting 403 (Forbidden)
                    Hawk333

                    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.00100
                    Reset NLS settings
                    4

                    8%

                    05-MAY-17 01.35.12.308000 PM +00:00
                    0.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:00
                    0.002000.00000
                    ...NLS: Set Decimal separator="."
                    4

                    0%

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

                    0%

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

                    0%

                    05-MAY-17 01.35.12.309000 PM +00:00
                    0.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:00
                    0.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:00
                    0.003000.00000
                    NLS of database and client differs, characterset conversion needed
                    4

                    0%

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

                    0%

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

                    0%

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

                    8%

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

                    0%

                    05-MAY-17 01.35.12.311000 PM +00:00
                    0.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:00
                    0.004000.00000
                    ...NLS: Set Decimal separator="."
                    4

                    0%

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

                    0%

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

                    0%

                    05-MAY-17 01.35.12.311000 PM +00:00
                    0.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:00
                    0.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:00
                    0.004000.00000
                    ...Setting session time_zone to +00:00
                    4

                    0%

                    05-MAY-17 01.35.12.311000 PM +00:00
                    0.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:00
                    0.005000.00000
                    Authentication check: S4S oAuth2 (PLUGIN_NL.S4S.OAUTH2)
                    4

                    0%

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

                    0%

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

                    0%

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

                    0%

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

                    0%

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

                    8%

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

                    0%

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

                    0%

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

                    8%

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

                    0%

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

                    0%

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

                    0%

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

                    0%

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

                    0%

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

                    0%

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

                    0%

                    05-MAY-17 01.35.12.314000 PM +00:00
                    0.007000.00000
                    Validations:
                    4

                    0%

                    05-MAY-17 01.35.12.314000 PM +00:00
                    0.007000.00100
                    Perform basic and predefined validations:
                    4

                    8%

                    05-MAY-17 01.35.12.314000 PM +00:00
                    0.008000.00000
                    Perform custom validations:
                    4

                    0%

                    05-MAY-17 01.35.12.315000 PM +00:00
                    0.008000.00000
                    Branches - point: BEFORE_PROCESSING
                    4

                    0%

                    05-MAY-17 01.35.12.315000 PM +00:00
                    0.008000.00000
                    Processes - point: AFTER_SUBMIT
                    4

                    0%

                    05-MAY-17 01.35.12.315000 PM +00:00
                    0.008000.00000
                    ...Process "Set Username Cookie" - Type: NATIVE_PLSQL
                    4

                    0%

                    05-MAY-17 01.35.12.315000 PM +00:00
                    0.008000.00100
                    ...Execute Statement: begin apex_authentication.send_login_username_cookie ( p_username => lower(:P101_USERNAME) ); end;
                    4

                    8%

                    05-MAY-17 01.35.12.315000 PM +00:00
                    0.009000.00000
                    ...Process "Login" - Type: NATIVE_PLSQL
                    4

                    0%

                    05-MAY-17 01.35.12.316000 PM +00:00
                    0.009000.00100
                    ...Execute Statement: begin apex_authentication.login( p_username => :P101_USERNAME, p_password => :P101_PASSWORD ); end;
                    4

                    8%

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

                    0%

                    05-MAY-17 01.35.12.317000 PM +00:00
                    0.010000.00200
                    ...Execute Statement: begin declare  begin wwv_flow_plugin_api.g_authentication_auth_result := TEST.s4sa_oauth_pck.authenticate( p_authentication => wwv_flow_plugin_api.g_authentication, p_plugin         => wwv_flow_plugin_api.g_plugin, p_password => :p_password); end; end;
                    4

                    15%

                    05-MAY-17 01.35.12.317000 PM +00:00
                    0.012000.00000
                    Stop APEX Engine detected
                    4

                    0%

                    05-MAY-17 01.35.12.319000 PM +00:00
                    0.012000.00100
                    Stop APEX Engine detected
                    4

                    8%

                    05-MAY-17 01.35.12.319000 PM +00:00
                    0.01300-
                    Final commit
                    4

                    0%

                    05-MAY-17 01.35.12.320000 PM +00:00

                     

                    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:

                     

                     

                    https://accounts.google.com/o/oauth2/auth?client_id=923149771424-o2md7bk7bvvc5moi0reeu4kvkdjt451a.apps.googleusercontent.com&redirect_uri=ec2-54-82-48-165.compute-1.amazonaws.com:8080/ords/mizaajj.s4sg_auth_pck.oauth2callback&scope=profile+email+https%3A%2F%2Fwww%2Egoogleapis%2Ecom%2Fauth%2Fplus%2Elogin+https%3A%2F%2Fwww%2Egoogleapis%2Ecom%2Fauth%2Fcalendar+https%3A%2F%2Fwww%2Egoogleapis%2Ecom%2Fauth%2Fdrive+https%3A%2F%2Fmail%2Egoogle%2Ecom%2F+https%3A%2F%2Fwww%2Egoogle%2Ecom%2Fm8%2Ffeeds&state=33634189251519:101000:10000:101&response_type=code&approval_prompt=force
                    

                     

                    I am still getting the same error. Here is my browser console/network request:

                     

                    1.PNG

                    2.PNG

                     

                    • 8. Re: New authentication scheme getting 403 (Forbidden)
                      Pavel_p

                      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

                      • 9. Re: New authentication scheme getting 403 (Forbidden)
                        Hawk333

                        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