Mysql – User roles and permissions

database-designMySQLPHP

Look at the attached graphic.

I'm trying to keep this as simple as possible.

Basically I want to have multiple admins(or superusers) who own regular users who own sites.

  • Admins can give regular users permission or role to a website.
  • Admins can see and edit all sites of the users they own.
  • Users can only see & edit their own sites. (based on the role).
  • One site can be managed by multiple users.

I don't know if I should have permissions table with multiple rows for each role so one user can have many rows for each role or maybe use an array in one row only… Idk. I'm stuck there.

What's the best way to do this simply?

P.S: I know the graphic has User2 under site1 and site2 but I don't need it that complex. Same users for same site but different roles is sufficient. No need for 1 regular user to edit 2 sites.

enter image description here

Best Answer

The simplest structure would be a PERMISSIONS table like this:

create table PERMISSIONS
( Site_ID int
, User_ID int
, Role char(1)
, primary key (Site_ID, User_ID, Role) -- scenario 1
-- OR:
, primary key (Site_ID, User_ID) -- scenario 2
)

Use values for Role such as:

  • A = Administrator
  • P = Publisher
  • E = Editor

How many rows you need depends on how your code interprets the data. You could have very simple code which interprets each permission strictly at face value. To do an administrative task, the user would need an 'A' permission. To do a publishing task, they would need a 'P' permission, etc. In this case an administrator would need three records per site (one each of A, P, E). Similarly publishers would need two records per site and editors only one. There is a risk in this approach that data consistency errors could creep in, like someone could be a publisher, but not an editor (by virtue of the 'E' record being missing).

Alternatively, you could make your code more complex, in which case each user only needs one permission record per site. In this scenario, administrative tasks require an 'A' permission, publishing requires an 'A' or a 'P' and editing requires 'A', 'P', or 'E'. The advantage of this approach is that there is less data to maintain and you won't have inconsistencies.

Note too that you do not need to have the Users.Parent column. If an administrator has an 'A' permission for a site, that automatically gives them control of all of the users for that site.

Related Question