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

I am designing a RESTful API with Passport and L5.3 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.

jekinney

9 years ago

Level 47

If your consuming sub apps use roles and permission I suggest the sub apps deal with each user's role and permission on an app level.

Each sub app should consume the user as needed. Your o auth app doesn't need to know that info and it will get confusing.

For example you have a todo list app and a blog app. Your oauth doesn't need to deal with if a user in your todo app has a role to author articles. That aspect should be the responsibility of the blog app. Your blog app doesn't need to know if the user has a role to manage a teams todo list etc etc.

Now obviously banning a user (generally a Boolean on the user model anyways) the oauth server needs to know. But should the blog be able to ban a user, or the oauth app?

Yes, there will be overlap, but from my experience each app needs to handle it's self. The purpose of a single login is just that.

Now the oauth server though should have knowledge of applicable apps via the tokens, to control authentication of sub apps (cors headers etc). Probably a dashboard to handle users globally etc.

2 likes

andcl85

9 years ago

Level 1

Thanks for the reply. Ok @jekinney, I will reformulate my question: If I use a package like Entrust for managing user roles and permissions...

How would I have to modify it to assign them in an app basis? That is, each user can have many roles (Entrust solves it), but also in many apps (that is what I do not know)...

So... should I add an app_id field to the pivot role_user table? Then, how would I check the roles in each case? By overriding Entrust methods?

Thanks in advance.

Please or to participate in this conversation.