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.

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.