What is the best way to setup permissions for web site users?

Question

I'm working on a web site where each user can have multiple roles/permissions such as basic logging in, ordering products, administrating other users, and so on. On top of this, there are stores, and each store can have multiple users administrating it. Each store also has it's own set of permissions.

I've confused myself and am not sure how best to represent this in a db. Right now I'm thinking:

users roles users_roles stores stores_users

But, should I also have stores_roles and stores_users_roles tables to keep track of separate permissions for the stores or should I keep the roles limited to a single 'roles' table?

I originally thought of having only a single roles table, but then what about users who have roles in multiple stores? I.e., if a user is given a role of let's say 'store product updating' there would need to be some method of determining which store this is referring to. A stores_users_roles table could fix this by having a store_id field, thus a user could have 'store product updating' and 'store product deletion' for store #42 and only 'store product updating' for store #84.

I hope I'm making sense here.

Edit

Thanks for the info everyone. Apparently I have some thinking to do. This is simply a fun project I'm working on, but RBAC has always been something that I wanted to understand better.

Answer 1

It seems likely to me that if I have permission to do certain roles in a set of stores, then I would probably have the same permissions in each store. So having a single roles table would probably be sufficient. So "joe" can do "store product updating" and "store product deletion", then have a user_stores table to list which stores he has access to. The assumption is for that entire list, he would have the same permissions in all stores.

If the business rules are such that he could update and delete in one store, but only update, no delete, in another store, well then you'll have to get more complex.

In my experience you'll usually be told that you need a lot of flexibility, then once implemented, no one uses it. And the GUI gets very complex and makes it hard to administer.

If the GUI does get complex, I suggest you look at it from the point of view of the store as well as the point of view of the user. In other words, instead of selecting a user, then selecting what permissions they have, and what stores they can access, it may be simpler to first select a store, then select which users have access to which roles in that store. Depends I guess on how many users and how many stores. In a past project I found it far easier to do it one way than the other.

Answer 2

Put a store_id in the user_roles table.

If this is Rails, the user model would have_many :stores, :through => :roles

Answer 3

Your model looks ok to me. The only modification I think you need is as to the granularity of the Role. Right now, your role is just an operation.

But first, you need a store_role table, a joint table resolving the Many-to-many relationship b/w a role and a store. ie, one store can have many roles and one role can be done in many stores.

Eg: StoreA can CREATE, UPDATE, DELETE customer. and DELETE customer can be done in StoreA, StoreB and StoreC.

Next, you can freely associate users to store_role_id in the user_store_roles table.

Now, a user_store_role record will have a user_id and a store_role_id:

A collection of

SELECT * FROM USER_STORE_ROLE WHERE user_id = @userID

returns all permitted operations of the user in all the stores.

For a collection of users's roles in a particular store, do an inner join of the above to user_store table adding a WHERE part of like

where STORE_ROLE.store_id = @storeID

Answer 4

This is probably obvious to you by now, but role based access control is hard. My suggestion is, don't try to write your own unless you want that one part to take up all the time you were hoping to spend on the 'cool stuff'.

There are plenty of flexible, thoroughly-tested authorization libraries out there implementing RBAC (sometimes mislabeled as ACL), and my suggestion would be to find one that suits your needs and use it. Don't reinvent the wheel unless you are a wheel geek.