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:
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 :(
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...
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.