oAuth 2.0 Database structure

Question

I am looking to implement oAuth in my current application. What is a good database structure to store information required, such as token etc-era. Are there any standards?

Answer 1

I was considering the same thing. In general, I'm doing:

user_oauth_info
-------------------------------
id (int auto-inc)
user_id (int)
oauth_provider (varchar 20)
acccess_token (varchar 40)
refresh_token  (varchar 40)   
expiry_date (datetime)

A refresh_token is provided by SalesForce; does not expired and is used to get refreshed access_tokens. They only give you one if your callback URL is a mobile device, though, which is irritating.

Answer 2

You could start with what VS2012 suggests for their MVC framework:

webpages_OAuthMembership

Provider nvarchar(30) (clustered primary key)
ProviderUserId nvarchar(100) (clustered primary key)
UserId int

webpages_Membership

UserId int (Primary Key)
CreateDate datetime
ConfirmationToken nvarchar(128)
IsConfirmed bit
LastPasswordFailureDate datetime
PasswordFailuresSinceLastSuccess int
Password nvarchar(128)
PasswordChangedDate datetime
PasswordSalt nvarchar(128)
PasswordVerificationToken nvarchar(128)
PasswordVerificationTokenExpirationDate datetime

Then define your own Users table, something like:

UserID int (Primary Key)
UserName nvarchar(80)
Name nvarchar(80)
Surname nvarchar(80)

I don't really have a reason for doing it this way, but I guess that the Microsoft people that came up with this schema know way more about this than I do, so I think it's great place to start.