OAuth best practices - DB design when users belong to several apps

1.1k views Asked by At

I am designing a RESTful API which will serve as common backend for several mobile apps. So far, it consists of:

  • Users table: id (primary), name, email, password
  • Apps table: id (primary), name, description

Just independent tables...

I want to implement the OAuth Password Grant method to request OAuth Access Tokens from mobile apps, using an email / password form in each case so that I can use OAuth scopes to leverage access levels.

And I am just wondering which is the best approach for designing the database schema.

My tries, so far:

  1. Adding an app_id field to users table and making the email field unique, but this would limit them to use only the first app where they register. So that is not a solution. However, with this approach, I could implement OAuth scope-based permission system the easy way :(

  2. Adding an app_id field to users table but not making email field unique. Then I could have two rows with the same email and different app_ids. But the implementation of OAuth scope-based permission in this case would be inconsistent as I could have two rows with the same email / password when I called 'oauth/token' route...

  3. As many users can be registered in many apps, other idea was to create a pivot table between apps and users (app_user). That table would have:

    • app_id, user_id, and a role field to specify the role of each user in each app. So far so good with this, but the problem here comes when a user with high privileges in an app would use his/her golden Access Token to perform forbidden actions in another app... :(

Please, can someone shed some light on this topic?

Many thanks in advance.

0

There are 0 answers