1

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.

andcl
  • 3,342
  • 7
  • 33
  • 61
  • 1
    Option 3 is the way to go. Basically, you are going to have multiple rows for a user in this table (one row per app) and each row can have a different value for role. So, what's the problem? – Sparrow Dec 28 '16 at 00:41
  • First, thanks for the reply ;) The problem comes when assigning OAuth Access Tokens to users, because the 'oauth/token' route only wants a 'username / password' pair to return the token... and it does not look for the app_id... Thus, for example an **admin** user in App1 (and **normal** user in App2) would get an 'admin' Access Token when hitting 'oauth/token' route, and then could use it to perform admin level actions in App2... :( ... Any suggestions? Thanks again. – andcl Dec 28 '16 at 11:41
  • 1
    I see...this is because oAuth is an Authorization protocol; not an Authentication one. Ok, The section called "Lack of audience restriction" is addressing your problem. Basically, you need to pass another parameter with your service calls, to be used for access restriction. Check out this link: https://oauth.net/articles/authentication/ – Sparrow Dec 28 '16 at 14:58
  • Ok, thank you very much for your time. It is just what I was thinking. Then, I can't use OAuth scopes to leverage privileges. Instead, I will use OAuth Tokens only for authorizing users (checking their credentials) and then once they have logged in using OAuth, I will implement a role-users-permissions system behind the scenes which will check in each case which app is accessing each user :( .... – andcl Dec 28 '16 at 16:44
  • I think you should use OpenID Connect (http://openid.net/connect/). It is built on top of OAuth 2. – Sparrow Dec 28 '16 at 19:29
  • Resolved here: http://stackoverflow.com/questions/41380882/laravel-5-entrust-users-belong-to-many-apps – andcl Jan 02 '17 at 19:41

0 Answers0