Multiple Roles for Multiple Users in one database

[Terry]

I am trying to determine the best way to handle multiple roles per user in one database. Example: Multiple applications use the same "users" table for authentication. One user may be a manager with elevated rights in a request database, but may be a requestor in say, another application to submit an expense report.

Is it better to have the "users" table and the "roles" table joined as one to many to a third table, or to have a "roles" table per application? Thanks. 

[Mathilda]

On 10/25/2016 at 11:48 PM, Terry said:

I am trying to determine the best way to handle multiple roles per user in one database. Example: Multiple applications use the same "users" table for authentication. One user may be a manager with elevated rights in a request database, but may be a requestor in say, another application to submit an expense report.

Is it better to have the "users" table and the "roles" table joined as one to many to a third table, or to have a "roles" table per application? Thanks. 

Hi Terry,

I would recommend using one table for users and define roles using checkboxes, Then create a view to filter users based on role defined for each application. I suppose this article will be helpful