Recommended database structure for OAuth Provider

Question

I am implementing an OAuth Provider using DevDefined library.

I wonder if there is any recommended database structure for storing consumer and token data on the server side.

Any advice on this would be appreciated.

January

Answer 1

NB: The answer below is applicable mostly to OAuth 1.0

I don't really know anything about the DevDefined library. But here is a non-technical description of the database design I ended up working with in my latest project, using an SQL database.

It should cover everything needed to follow the basic specification. I've tried to keep it down to an absolute minimum.

RequestTokens

• token (I use an MD5 here, primary key)
• consumerKey (the unique identifier for the consumer)
• secret (SHA1)
• createTime (timestamp)
• callback

AccessTokens

• token (MD5, primary key)
• secret (SHA1)
• consumerKey
• userID (refers to the resource owner)
• createTime

Consumers (registered third party applications)

• consumerKey (MD5, primary key)
• consumerSecret (SHA1)
• userID (refers to the developer who registered the application, not unique)
• description (a text to describe the application)
• name (the name of the application)
• callback

UsedNonces

• nonce
• timestamp

The handling of nonces was really the biggest design question for me. OAuth tells you to never allow the same nonce to be used with the same timestamp ever again. But that would make for an infinitely huge database. I think most providers batch away old nonces at least once in a while.

I routinely clear away nonces older than 5 minutes, based on the premise that all requests with a timestamp older than 5 minutes are rejected. I am slightly forgiving when checking timestamps, they need to be UTC and either not older than 5 minutes, and not ahead of my server time more than one minute.

Raceway

Why do you store request token into database while you know it's a temporary token? Isn' it OK to store request tokens in session on server? – January

There are no guarantees that either the request token or the access token requests go through a browser. If you only want to support browser based authorization then I guess you could skip persisting the request token. The OAuth specification does not make any assumptions about the type of client (except in the actual authorization step, where a browser is used), but you are free to enforce that of course. – Raceway

Don't you need to store the user ID in the request token too? – Dedie

No, because you can't know which user is going to authorize the request token yet. That info is added in the authorization step. – Raceway

How much, if at all, does this change for OAuth 2.0? – Stylus

Answer 2

There are a few ways to approach this, one example of an application implementing both provider and consumer functionality is Atlassian's Jira - here is their structure:

    <prim-key field="id"/>

    <index name="oauth_consumer_token_key_index" unique="true">
        <index-field name="tokenKey"/>
    </index>
    <index name="oauth_consumer_token_index">
        <index-field name="token"/>
    </index>
</entity>

 <entity entity-name="OAuthConsumer" table-name="oauthconsumer" package-name="">
    <field name="id" type="numeric"/>
    <field name="created" type="date-time"/>
    <field name="name" col-name="consumername" type="long-varchar"/>
    <field name="consumerKey" type="long-varchar"/>
    <field name="service" col-name="consumerservice" type="long-varchar"/>
    <field name="publicKey" type="very-long"/>
    <field name="privateKey" type="very-long"/>
    <field name="description" type="very-long"/>
    <field name="callback" type="very-long"/>
    <field name="signatureMethod" type="short-varchar"/>
    <field name="sharedSecret" type="very-long"/>

    <prim-key field="id"/>

    <index name="oauth_consumer_index" unique="true">
        <index-field name="consumerKey"/>
    </index>
    <index name="oauth_consumer_service_index" unique="true">
        <index-field name="service"/>
    </index>
</entity>

<!-- OAUTH ServiceProvider-->
<entity entity-name="OAuthServiceProviderConsumer" table-name="oauthspconsumer" package-name="">
    <field name="id" type="numeric"/>
    <field name="created" type="date-time"/>
    <field name="consumerKey" type="long-varchar"/>
    <field name="name" col-name="consumername" type="long-varchar"/>
    <field name="publicKey" type="very-long"/>
    <field name="description" type="very-long"/>
    <field name="callback" type="very-long"/>

    <prim-key field="id"/>

    <index name="oauth_sp_consumer_index" unique="true">
        <index-field name="consumerKey"/>
    </index>
</entity>

<entity entity-name="OAuthServiceProviderToken" table-name="oauthsptoken" package-name="">
    <field name="id" type="numeric"/>
    <field name="created" type="date-time"/>
    <field name="token" type="long-varchar"/>
    <field name="tokenSecret" type="long-varchar"/>
    <field name="tokenType" type="short-varchar"/>
    <field name="consumerKey" type="long-varchar"/>
    <field name="username" type="long-varchar"/>
    <field name="ttl" type="numeric"/>
    <field name="auth" col-name="spauth" type="short-varchar"/>
    <field name="callback" type="very-long"/>
    <field name="verifier" col-name="spverifier" type="long-varchar"/>
    <field name="version" col-name="spversion" type="short-varchar"/>

    <prim-key field="id"/>

    <index name="oauth_sp_token_index" unique="true">
        <index-field name="token"/>
    </index>
    <index name="oauth_sp_consumer_key_index">
        <index-field name="consumerKey"/>
    </index>
</entity>

Normally the basics mimic the specification - except for custom extensions you may introduce to deal with:

• IP Address restrictions
• Time to live for the token
• Allowing for refreshing/renewing tokens
• The list goes on...

Attitudinize