This discussion is archived
5 Replies Latest reply: May 18, 2007 7:08 AM by Jeff E RSS

Application Express user authentication using Google Account

484861 Newbie
Currently Being Moderated
http://prpi.blogspot.com/2006/05/application-express-user.html
  • 1. Re: Application Express user authentication using Google Account
    60437 Employee ACE
    Currently Being Moderated
    Przemek - Excellent!

    Scott
  • 2. Re: Application Express user authentication using Google Account
    Jes Oracle ACE
    Currently Being Moderated
    I'll second that, fantasic job.
  • 3. Re: Application Express user authentication using Google Account
    228446 Newbie
    Currently Being Moderated
    Scott,

    This link is moved or no more exists. Could you explain how it was done??

    Thanks
  • 4. Re: Application Express user authentication using Google Account
    60437 Employee ACE
    Currently Being Moderated
    Sorry, don't know.
    Scott
  • 5. Re: Application Express user authentication using Google Account
    Jeff E Expert
    Currently Being Moderated
    Google Cached copy:
    http://64.233.167.104/search?q=cache:osReeGhrjRgJ:prpi.blogspot.com/search/label/google+prpi+apex+google+authentication&hl=en&ct=clnk&cd=1&gl=us

    Przemek Piotrowski Blogging

    If you are looking for a reliable Single Sign-On solution, this might be a perfect choice for your Application Express project. Authenticating users using their Google Account information and Google API is actually very straightforward.

    Before anything else we need to familiarize with the fact that Google API requires HTTPS so you need PKCS12 certificate for Thawte SGC CA (issuer of Google's certificate). With Oracle's Wallet Manager (10g Enterprise) it is trivial to obtain one: create an empty wallet, import the SGC and then export it to a path referenced below.

    Now use your favorite tool to create own authentication function like the one below, changing set_wallet path and password to values applicable to your configuration.
    CREATE OR REPLACE
    FUNCTION gaauth(p_username VARCHAR2, p_password VARCHAR2) RETURN BOOLEAN AS
    req UTL_HTTP.REQ;
    resp UTL_HTTP.RESP;
    google_auth_url VARCHAR2(100) := 'https://www.google.com/accounts/ClientLogin';
    params VARCHAR2(200);
    Email VARCHAR2(100);
    Passwd VARCHAR2(100);
    amp CHAR(1) := chr(38);
    v_msg VARCHAR2(80);
    name VARCHAR2(200);
    val VARCHAR2(200);
    BEGIN
    utl_http.set_response_error_check(enable=>true);
    utl_http.set_wallet('file:D:\dev\Wallet', 'test1234');
    req := utl_http.begin_request(url=>google_auth_url, method=>'POST');
    params := 'Email='||p_username|| amp||'Passwd='||p_password|| amp||'service=xapi'|| amp||'source=PRPI-ApexGoogleAuthentication-1.0';
    utl_http.set_header(r=>req, name=>'Content-Type', value=>'application/x-www-form-urlencoded');
    utl_http.set_header(r=>req, name=>'Content-Length', value=>length(params));
    utl_http.write_text(r=>req, data=>params);
    resp := utl_http.get_response(r=>req);
    RETURN true;
    EXCEPTION
    WHEN OTHERS THEN
    RETURN false;
    END;
    Now at the target application create a new or alter an existing authentication scheme (Home>Application Builder>Application 101>Shared Components>Authentication Schemes) and at the Authentication Function field type return gaauth. Please note that you need to use the whole e-mail address for login name, including @gmail.com if you own GMail account.