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:

Register your application

User Visits your web application

Your web application redirects the user to Google Authorization page

User grants your web application access

Google redirects the user back to your web application
and returns an authorization code

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:

Client ID

Client Secret

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

Keep the Web Application radio button selected, and enter
the site or host name of your web application.

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:

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.

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.

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:

Access token can be used for all subsequent requests to Fusion
Tables, until the token expires

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:

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.

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:

Access token can be used for all subsequent requests to
Fusion Tables, until the token expires

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:

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)

(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:

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:

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:

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:

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:

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.

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.