Google Fusion Tables API

Using OAuth 2.0 for Authorization to Fusion Tables in Web Applications

Kathryn Hurley, Developer Programs Engineer, Fusion Tables
June 2011

Fusion Tables is a great way to store and share your data and makes creating maps and other visualizations easy. The Fusion Tables SQL API allows you to create tables, insert data, update data, and delete data using an SQL-like syntax. While this is straightforward, the challenge is usually at the authentication / authorization step. Since we've seen ClientLogin #FAIL, it is time to move to a more robust means of authorization, which is best met with OAuth 2.0.

To begin, here's the typical OAuth 2.0 workflow:

  1. Register your application
  2. User Visits your web application
  3. Your web application redirects the user to Google Authorization page
  4. User grants your web application access
  5. Google redirects the user back to your web application and returns an authorization code
  6. Your application requests an access token and refresh token from Google
  7. Google returns access token, refresh token, and expiration of access token
  8. Access token can be used for all subsequent requests to Fusion Tables, until the token expires
  9. When the access token expires, the refresh token is used to request a new access token

I also encourage you to watch the video from the Identity and Data Access: OpenID and OAuth Google I/O presentation for a great explanation of these steps.

Here's some python sample code that can be run from the command line that demonstrates the various steps:

#2. Your web application redirects the user to Google Authorization page
#3. User grants your web application access
print 'Visit the URL below in a browser to authorize'
print '%s?client_id=%s&redirect_uri=%s&scope=%s&response_type=code' % \
  ('https://accounts.google.com/o/oauth2/auth',
  client_id,
  redirect_uri,
  'https://www.googleapis.com/auth/fusiontables')

#4. Google redirects the user back to your web application and
#   returns an authorization code
auth_code = raw_input('Enter authorization code (parameter of URL): ')

#5. Your application requests an access token and refresh token from Google
data = urllib.urlencode({
  'code': auth_code,
  'client_id': client_id,
  'client_secret': client_secret,
  'redirect_uri': redirect_uri,
  'grant_type': 'authorization_code'
})
request = urllib2.Request(
  url='https://accounts.google.com/o/oauth2/token',
  data=data)
request_open = urllib2.urlopen(request)

#6. Google returns access token, refresh token, and expiration of
#   access token
response = request_open.read()
request_open.close()
tokens = json.loads(response)
access_token = tokens['access_token']
refresh_token = tokens['refresh_token']

#7. Access token can be used for all subsequent requests to Fusion Tables,
#   until the token expires
request = urllib2.Request(
  url='https://www.google.com/fusiontables/api/query?%s' % \
    (urllib.urlencode({'access_token': access_token,
                       'sql': 'SELECT * FROM 123456'})))
request_open = urllib2.urlopen(request)
response = request_open.read()
request_open.close()
print response

#8. When the access token expires,
#   the refresh token is used to request a new access token
data = urllib.urlencode({
  'client_id': client_id,
  'client_secret': client_secret,
  'refresh_token': refresh_token,
  'grant_type': 'refresh_token'})
request = urllib2.Request(
  url='https://accounts.google.com/o/oauth2/token',
  data=data)
request_open = urllib2.urlopen(request)
response = request_open.read()
request_open.close()
tokens = json.loads(response)
access_token = tokens['access_token']

But where do you begin? And how does OAuth relate specifically to Fusion Tables?

Register your application

First and foremost, you will need to register your application. Registration can be done via the Google API Console. Registering your application will give you 3 important pieces of information that are vital to using OAuth 2.0:

  1. Client ID
  2. Client Secret
  3. Redirect URL

As you can see in the python sample code above, you need this information to request the authorization code and the access and refresh tokens. Here's a very brief list of the steps you need to take to register your application (a full set of instructions can be found here):

  1. Go to the Google API Console.
  2. If this is your first time using the Console, add a new project.
  3. If you haven't done so already, turn on the Fusion Tables API.
  4. Select API Access in the left navigation.
  5. Click Create an OAuth 2.0 client ID...
  6. Enter in your Product Name.
  7. Keep the Web Application radio button selected, and enter the site or host name of your web application.
  8. Click Create client ID.

Your web application is now registered with Google; and the Client ID, Client Secret and Redirect URL are available for use in your code.

What type of App are you Building?

Now let's talk about what you want to do with your Fusion Tables application, since the application you develop will determine how use OAuth. There are 2 categories that most Fusion Tables applications fall into:

  1. Application uses Fusion Tables as a database where all data from the application is stored in a single Fusion Table. An example of this might be a crowd sourcing application that uses Fusion Tables to store information from users about the number of potholes found around the community.

  2. Application allows users to access/edit Fusion Table data in their own personal accounts. A great example of this is Shape Escape, that allows users to upload shape files directly to their own Fusion Table accounts.

Which category does your application fall into?

Application 1 - Using Fusion Tables like a Database*

Let's go through the process for Application 1, which uses Fusion Tables as a database, since this process is actually easier, but might not be as clear how to accomplish with OAuth.

Remember steps 1-8 from the typical OAuth 2.0 workflow? Well, all you really need to worry about is steps 7 and 8:

  1. Access token can be used for all subsequent requests to Fusion Tables, until the token expires
  2. When the access token expires, the refresh token is used to request a new access token

"What?!" you might ask. "But how? and why?"

There are 2 things that make this possible:

  1. The access token gives your application access to the associated user's account. In other words, whoever granted access in step 3 of the OAuth workflow, that's whose data you will be able to access.
  2. The refresh token is valid until the user revokes it.

In other words, if you own a Fusion Table in your own account that you want to serve as a database in your application, you simply need to get the access and refresh tokens for that account, and use the access token for all requests to Fusion Tables. When the access token expires, a 401: Unauthorized message is returned, and the refresh token can be used to request a new access token.

Luckily, it's easy to get the access and refresh tokens for your account by using this python script. Simply download the file and run from the command line using the command "python oauth_tokens.py". Follow the instructions, and your access and refresh tokens will be returned.

You can then securely save the access and refresh tokens somewhere accessible by your application, and use them in your code to make the requests to Fusion Tables. Going back to the python code provided above, you would now only need the following:

#7: Access token can be used for all subsequent requests to Fusion Tables,
#   until the token expires
request = urllib2.Request(
  url='https://www.google.com/fusiontables/api/query?%s' % \
    (urllib.urlencode({'access_token': access_token,
                       'sql': 'SELECT * FROM 123456'})))
request_open = urllib2.urlopen(request)
response = request_open.read()
request_open.close()
print response

#8: When the access token expires,
#   the refresh token is used to request a new access token
data = urllib.urlencode({
  'client_id': client_id,
  'client_secret': client_secret,
  'refresh_token': refresh_token,
  'grant_type': 'refresh_token'})
request = urllib2.Request(
  url='https://accounts.google.com/o/oauth2/token',
  data=data)
request_open = urllib2.urlopen(request)
response = request_open.read()
request_open.close()
tokens = json.loads(response)
access_token = tokens['access_token']

Application 2 - Creating an application that allows users to access their own Fusion Table data

With this type of application, you need to implement all the steps in the OAuth workflow. Here are the steps again to refresh your memory:

  1. Register your application
  2. User Visits your web application
  3. Your web application redirects the user to Google Authorization page
  4. User grants your web application access
  5. Google redirects the user back to your web application and returns an authorization code
  6. Your application requests an access token and refresh token from Google
  7. Google returns access token, refresh token, and expiration of access token
  8. Access token can be used for all subsequent requests to Fusion Tables, until the token expires
  9. When the access token expires, the refresh token is used to request a new access token

The first time a user visits your web application, steps 1-6 are executed. Once the access and refresh token are retrieved for that user, you can optionally (and securely) save this data, and reuse the tokens when the same user visits your site for steps 7-8.

I created a sample App Engine / OAuth 2.0 / Fusion Tables application that I will be referring to throughout this section to demonstrate the various OAuth steps. The domain of the application is basicftoauthexample.appspot.com. Please note that this demo is a very basic version of a typical application. It is intended strictly for learning purposes and not intended for reuse.

Set-up

There are a couple set-up steps that you should consider doing before anything else:

  1. Make sure to designate a URL on your site to handle requests to the redirect URI obtained when you registered your application. In the app engine OAuth demo, this demonstrated on the following line:
    ('/oauth2callback', OAuthCallback)
    
  2. (Optional) If you want to save the access and refresh token for each user, make sure to set up your database to save this information. For example:
    class UserTokens(db.Model):
      user = db.UserProperty()
      access_token = db.StringProperty(required=True)
      refresh_token = db.StringProperty(required=True)
    
  3. Now let's go through the implementation of the OAuth workflow.

    Steps 1-2

    When a user first visits your web application, you need to redirect the user to the Google authorization URL (https://accounts.google.com/o/oauth2/auth) to initiate the authorization process. It is often recommended to perform this as a response to a user operation on your site, such as a click on a Login button. Four URL parameters are required at the end of the Google authorization URL:

    • Client ID - obtained during registration of your web application
    • Redirect URI - obtained during registration of your web application
    • Scope - Fusion Tables scope is https://www.googleapis.com/auth/fusiontables
    • Response Type - designates a type of flow. Use 'code' for a server-side flow.

    In the App Engine demo, steps 1-2 are represented in the MainHandler class by the following line of code:

    self.redirect(
      '%s?client_id=%s&redirect_uri=%s&scope=%s&response_type=code' % \
        ('https://accounts.google.com/o/oauth2/auth',
        client_id,
        redirect_uri,
        'https://www.googleapis.com/auth/fusiontables')
    )
    

    Steps 3-4

    Steps 3 and 4 take place on the Google Authorization page. When your user clicks either the Allow Access or No Thanks button on this page, Google redirects the user back to the Redirect URI provided as a URL parameter in Step 2. Google also adds a code parameter to the end of the Redirect URI. If the user selected Allow Access, the code will be equal the authorization code. If the user selected No Thanks, it will be set to "access_denied". In summary, the URL will look something like the following:

    http://basicftoauthexample.appspot.com/oauth2callback?code={<authorization_code> | access_denied}
    

    In the App Engine demo, the OAuthCallback class handles the redirect. The following code retrieves the code parameter from the URL:

    # Get the authorization code that's a parameter of the URL
    authorization_code = self.request.get('code')
    

    The remainder of the code assumes that the user selected Allow Access. In your own application, you would want to handle the case when the user selects No Thanks.

    Steps 5-6

    Using the authorization code obtained above, an access and refresh token can now be requested from Google. The request for an access and refresh token is sent as a POST to https://accounts.google.com/o/oauth2/token. The following data is required in the body of the request:

    • Authorization code - obtained in Step 4
    • Client ID - obtained during registration of your web application
    • Client Secret - obtained during registration of your web application
    • Redirect URI - obtained during registration of your web application
    • Grant Type - set to "authorization_code"

    The response from Google is JSON-formatted data containing the access token, refresh token, and time to expiration (in seconds) of the access token.

    In the App Engine code, steps 5-6 are performed in the OAuthCallback class:

    # Request access and refresh tokens from Google
    data = urllib.urlencode({
      'code': authorization_code,
      'client_id': client_id,
      'client_secret': client_secret,
      'redirect_uri': redirect_uri,
      'grant_type': 'authorization_code'
    })
    response = urlfetch.fetch(
      url='https://accounts.google.com/o/oauth2/token',
      payload = data,
      method = urlfetch.POST,
      headers = {'Content-Type': 'application/x-www-form-urlencoded'},
      deadline = 10).content
    tokens = simplejson.loads(response)
    access_token = tokens['access_token']
    refresh_token = tokens['refresh_token']
    

    At this point, you can optionally save the user's access and refresh token. Do this securely with proper encryption (not shown).

    user = users.get_current_user()
    userTokens = UserTokens(
      user = user,
      access_token = access_token,
      refresh_token = refresh_token)
    userTokens.put()
    

    Step 7

    Now that you have an access token for the user, you can use this token to send requests to Fusion Tables. Send the access token as a URL parameter or as an authorization header (more information here). In the App Engine sample, the token is being sent as a parameter in the URL along with the GET request to SHOW TABLES:

    def send_query(self, access_token):
      """ Sends query to Fusion Tables to SHOW TABLES,
          OAuth access token sent as parameter """
      return urlfetch.fetch(
        url = 'https://www.google.com/fusiontables/api/query?%s' % \
          urllib.urlencode({
            'sql': 'SHOW TABLES',
            'access_token': access_token
          }),
        deadline = 10
      )
    

    If you're running a query that requires a POST, make sure to set the request method to "POST" and send the sql query as part of the body (payload) of the request. For more information on which queries require POST, please see the Fusion Tables Developer's Guide.

    Step 8

    When the access token expires, a 401 status code will be returned. To distinguish between an expired access token and the user simply not having permission to the given table or resource, make sure the response content does not have the message "User does not have permission".

    The request to refresh the access token is sent as a POST request to https://accounts.google.com/o/oauth2/token. The following data is required in the body of the request:

    • Client ID - obtained during registration of your web application
    • Client Secret - obtained during registration of your web application
    • Refresh Token - obtained in Step 6
    • Grant Type - set to "refresh_token"

    Google returns a JSON-formatted response, which contains the Access Token. You can use this new access token to send requests to Fusion Tables.

    Note: the user can optionally revoke access to your application in their Accounts Page. When this occurs, the refresh token will no longer be valid and a 401 error will be returned when requesting a new access token. When this occurs, you need to restart the authorization process at step 1.

    In the App Engine code, step 8 is performed in the Show class:

    if response.status_code == 401 and \
        not response.content.find("User does not have permission") != -1:
      access_token = self.refresh_token(user_token)
    ...
    
    def refresh_token(self, user_token):
      """ Refresh access token using refresh token """
      data = urllib.urlencode({
        'client_id': client_id,
        'client_secret': client_secret,
        'refresh_token': user_token.refresh_token,
        'grant_type': 'refresh_token'
      })
      response = urlfetch.fetch(
        url='https://accounts.google.com/o/oauth2/token',
        payload = data,
        method = urlfetch.POST,
        headers = {'Content-Type': 'application/x-www-form-urlencoded'},
        deadline = 10).content
      tokens = simplejson.loads(response)
      access_token = tokens['access_token']
    
      return access_token
    

    If you decided to save your user's data in a database, make sure to update the access token for that user in the database:

    # Replace the old access token in the database
    user_token.access_token = access_token
    user_token.save()
    

    Summary

    This article demonstrates how to use OAuth 2.0 for authorization to Fusion Tables in web applications. It has shown you how to implement OAuth for two standard Fusion Tables web applications: either using Fusion Tables as a database, or allowing users access to their own Fusion Tables data.

    When using Fusion Tables as a database, only steps 7 and 8 of the OAuth workflow are really necessary in your application. You simply need to obtain the access and refresh tokens once for the account owning the table(s), and then use these for subsequent requests to Fusion Tables.

    When giving users access to their own Fusion Tables data, steps 1 to 8 need to be implemented in your application. You can optionally save the access and refresh tokens for each user the first time they visit your site. When the access token for the account expires, you can use the refresh token to request a new access token.

    For further reading, please see the following documentation:

    *Note that ClientLogin using a role account is still supported for authorization when accessing Fusion Tables as a database.

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.