Oracle APEX 18.1 Social Sign-In google

Christian - After fixing the client secret, I was able to successfully login but now it is throwing the same error. How did you see the debug output? Typically you run the page and click on the Debug link on the dev toolbar but for login issues the page doesn't render. I tried using the LEVEL9  keyword in the debug position  and queried apex_debug_messages for messages in the past 1 hour using the SQL Workshop on apex.oracle.com but couldn't see any errors. Help?

I used this to enable LEVEL9 debug for the session via Workspace Admin.

Ah, I forgot you have keys to the kingdom on the hosted site :-) Turns out that when I pasted your code snippet into the Post Auth. I didn't actually define the G_USER_INFO application item, my bad. But shouldn't this error be clearly logged (no such item) in the LEVEL9 debug messages?

Didn't realize that multiple authentication schemes could be used for an app, very nice feature! Can't wait for the 18.1 GA release!

Thanks

No "keys to the kingdom" required for this one :-)

1. Open app
   -> Google Sign in page shows
2. In separate tab, go to Workspace Admin > Monitor Activity > Active Sessions
3. Select session with "nobody" user and set Debug Level to APEX Trace, then save changes
4. In 1st tab, sign in
5. In 2nd tab, reload Page Views report
6. Click on Debug ID for Authentication Callback request

The additional values are all empty in post_auth because APEX takes a shortcut. Between all the debug gibberish are these lines:

  Authenticating via JWT, email="..."

  requesting userinfo
  Skipping userinfo endpoint, no other user attrs requested and username in JWT
  execute_login p_username=>...

Based on the declarative information in your authentication scheme, you only need the email address. That is already available in the ID token, which we get back together with the access token. Hence, APEX does not even send the request for additional userinfo data. Just add the other attributes (minus id_token and access_token, they are always available and merged in from the first request) to the "Additional User Attributes", to let APEX know that it needs to make sure that they are populated. I must have copied the post_auth from an older version of that sample app, sorry.

Regards,

Christian

Thanks!

The APEX_WEB_SERVICE requests plus request parameters and responses are all in the LEVEL9 debug output. Not sure if you can use the userinfo endpoint https://www.googleapis.com/oauth2/v3/userinfo with the playground app. You could also convert the parsed JSON values back to a session bind variable and display that, e.g. (untested):

  apex_json.initialize_clob_output;
  apex_json.write(apex_json.g_values);
  :MY_JSON_STRING := apex_json.get_clob_output;

Regards,

Christian

Hi Kubilay,

APEX does not implement Google's Browser-based login flow. The requests which return the ID token and other user information are sent from the DB server to Google, not from the client. Taken from https://developers.google.com/identity/protocols/OpenIDConnect:

5. Obtain user information from the ID token

An ID Token is a JWT (JSON Web Token), that is, a cryptographically signed Base64-encoded JSON object. Normally, it is critical that you validate an ID token before you use it, but since you are communicating directly with Google over an intermediary-free HTTPS channel and using your client secret to authenticate yourself to Google, you can be confident that the token you receive really comes from Google and is valid. If your server passes the ID token to other components of your app, it is extremely important that the other components validate the token before using it.

Regards,

Christian