Forum Stats

  • 3,741,286 Users
  • 2,248,404 Discussions
  • 7,861,719 Comments

Discussions

Oracle APEX 18.1 Social Sign-In google

jmarc
jmarc Member Posts: 399 Bronze Badge
edited Jun 11, 2018 4:48AM in APEX Discussions

hello,

for the apex.oracle.com ,the Social sign-in google gives the error :

pastedImage_0.png

it works with apexea.oracle.com

regards

jm

Kubilay Tsil Kara

Best Answer

Answers

  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 844 Employee
    edited Apr 23, 2018 11:53AM

    Hi JM,

    on apex.oracle.com, we are white-listing URLs for REST access and this URL was not yet in the list. Can you please try again?

    Regards,

    Christian

  • jmarc
    jmarc Member Posts: 399 Bronze Badge
    edited Apr 23, 2018 12:05PM

    hello christian,

    yes is works now.

    i have the same issue with microsoft account

    pastedImage_0.png

    best regards

    jm

  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 844 Employee
    edited Apr 23, 2018 12:15PM Accepted Answer

    Hi JM,

    I just added https://login.microsoftonline.com, too.

    Regards,

    Christian

  • jmarc
    jmarc Member Posts: 399 Bronze Badge
    edited Apr 23, 2018 12:31PM

    hello christian,

    it works perfect now.

    best regards

    jean-marc

  • partlycloudy
    partlycloudy Member Posts: 8,029 Silver Trophy
    edited May 17, 2018 10:16PM

    @Christian Neumueller-Oracle I followed the instructions here (changing the callback to apex.oracle.com instead of apexea.oracle.com of course) to setup Google authentication for application 47682 on apex.oracle.com and I get the Google login screen and I pick my account but APEX throws an error

    An unexpected internal application error has occurred. Please get in contact with your system administrator and provide reference# for further investigation.

    Am I doing something wrong or is this not supported on apex.oracle.com?

    Thanks

  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 844 Employee
    edited May 18, 2018 5:08AM

    Hi Vikas,

    debug output shows this

    JSON POST https://www.googleapis.com/oauth2/v4/token request got HTTP status 401

    {

    "error": "invalid_client",

    "error_description": "Unauthorized"

    }

    when APEX sends Client ID/Secret to get the access token. I think you must have a typo in the credentials, perhaps additional spaces.

    Regards,

    Christian

  • partlycloudy
    partlycloudy Member Posts: 8,029 Silver Trophy
    edited May 18, 2018 5:43AM

    Yup that's exactly what it was, the secret field is masked so you can't see trailing spaces!

    Follow up question about the Scope attribute in the authentication scheme... The field help says to use profile,email and to use APEX_JSON.GET_* in the post authentication procedure to get data. Not sure I understand what this means. Can you please explain how this works with an example? I'm guessing it's a mechanism to read some data elements from the user's Google profile and display them in the APEX application, right? Where does Google document the full list of values and data elements available for this attribute?

  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 844 Employee
    edited May 18, 2018 6:02AM

    The scope defines what groups of attributes you want to receive from the authentication provider. OpenID standardizes a few and Google implements OpenID. However, you should check out the documentation of the provider for what is available. Enable LEVEL9 in your session to see the returned JSON for debugging.

    Here is an example from one of my test apps:

    • Scope: profile,email,https://www.googleapis.com/auth/gmail.metadata
    • Username attribute: email
    • Post-authentication procedure:
      procedure post_auth is
      begin
        :G_USER_INFO := 'Authenticated via Google. '||chr(10)||
                        'Profile: '||apex_json.get_varchar2('profile')||chr(10)||
                        'Picture: '||apex_json.get_varchar2('picture')||chr(10)||
                        'Email: '||apex_json.get_varchar2('email')||chr(10)||
                        'ID Token: '||apex_json.get_varchar2('id_token')||chr(10)||
                        'Access Token: '||apex_json.get_varchar2('access_token');
      end post_auth;
    • PL/SQL region to query GMail labels (requires gmail.metadata scope)
      declare
          c clob;
      begin
          c := apex_web_service.make_rest_request (
               p_url                  => 'https://www.googleapis.com/gmail/v1/users/'||:APP_USER||'/labels',
               p_http_method          => 'GET',
               p_credential_static_id => 'GOOGLE' );
          htp.p(apex_escape.html(c));
      end;

    The Social Sign-In Authn saves the OAuth2 access token in the session credentials store, so you can make additional REST calls to the authentication provider, like above. However, the access token's lifetime is different than the APEX session lifetime. We have not implemented automatic refresh yet, so once the access token expires, these calls will fail. You can use them to fetch additional data to set up the APEX session, though.

    Google documentation:

    Regards,

    Christian

  • partlycloudy
    partlycloudy Member Posts: 8,029 Silver Trophy
    edited May 18, 2018 6:34AM

    Thanks that's very helpful. One last question... a) What instance level APEX configuration is needed to allow this? B) what database ACL configuration is needed to allow this?

  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 844 Employee
    edited May 18, 2018 6:39AM

    Social Sign-In uses APEX_WEB_SERVICE internally (or rather, it's implementation package). You have to set up your ACLs in a way that enables call-outs to the Google URLs (accounts.google.com, www.googleapis.com). If you are using the Authorized URLs feature, you need to white-list them, too.

    Regards,

    Christian

  • partlycloudy
    partlycloudy Member Posts: 8,029 Silver Trophy
    edited May 18, 2018 8:50AM

    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?

  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 844 Employee
    edited May 18, 2018 8:55AM

    Since I was using your app for the 1st time, Google displayed a consent page before redirecting back. I used this to enable LEVEL9 debug for the session via Workspace Admin. You could go to a public page first or, for fun, change your app to use multiple authentication schemes for testing. Make APEX Authn the default scheme and Google Authn the secondary, where you enable the new "Switch in Session" attribute. On the APEX login page, add an additional button that redirects to your app's start page and switches authentication, i.e. "f?p=&APP_ID.:1:&APP_SESSION.:APEX_AUTHENTICATION=Google" (where "Google" is your secondary Authn scheme's name).

    Regards,

    Christian

  • partlycloudy
    partlycloudy Member Posts: 8,029 Silver Trophy
    edited May 18, 2018 9:00AM
    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

  • partlycloudy
    partlycloudy Member Posts: 8,029 Silver Trophy
    edited May 18, 2018 9:09AM

    https://apex.oracle.com/pls/apex/f?p=47682:18

    Hmm G_USER_INFO doesn't contain the profile information, just the ID and Access tokens. I checked my Google account and all looks well. Mind taking a look?

  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 844 Employee
    edited May 18, 2018 10:18AM

    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

    Kubilay Tsil Kara
  • partlycloudy
    partlycloudy Member Posts: 8,029 Silver Trophy
    edited May 18, 2018 12:01PM

    Very helpful explanation to understand how all this works, appreciate it. I got it all working on my sample application. It authenticates using Google and clicking on the username in the nav bar goes to a page which displays the public profile picture. Very impressive, lots of potential, great work!

    I know this is not related to APEX, per se, but if you could indulge me for a little longer...Google has a nice interactive playground to experiment with their various APIs, I find it is a bit faster/easier to play with this to try different APIs instead of digging through APEX debug/trace logs.  So, silly question but when I set it up, which API/endpoint are we talking about here that APEX POSTs to get the profile & email?

    Thanks

  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 844 Employee
    edited May 18, 2018 12:25PM

    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

  • Kubilay Tsil Kara
    Kubilay Tsil Kara Member Posts: 17 Blue Ribbon
    edited Jun 9, 2018 4:27AM

    Hi Christian

    Your answers are very helpful! Social sign in is a great feature!

    I was reading about the Backend-auth in Google Docs here https://developers.google.com/identity/sign-in/web/backend-auth

    If I understand this correctly, It says that if you want to store / authenticate the Google signed in user with a backend server (database), you shouldn't just get the user info values returned from the Auth service and store them in the database. Before you store anything from the response you should crosscheck the values (CLIENT_ID etc...) in the id_token with what you have on the backend server. It seems you need another network call after the response via https://www.googleapis.com/oauth2/v3/tokeninfo?id_token= or programmatically, as it explains.

    Do we have to do this round trip before storing anything from the response or does the Apex built in social authentication takes care of it?

    Many thanks!

    Kubilay

  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 844 Employee
    edited Jun 11, 2018 4:48AM

    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

    Kubilay Tsil Kara
This discussion has been closed.