This question already has an answer here:

What would be the most efficient way to store a users role(in my case) in the database?

What my user table looks like:

id                         int(AI)
username                   varchar(80)
password                   varchar(255)

For example the rights would be like this:

There should be rights to see a page:

  • Page1
  • Page2
  • Page3
  • Page4

So in the case above you should be allowed to either have the right to see Page1 and Page2 but also just Page3

You can

  • Delete post
  • Create post
  • Edit post

on just Page2

All these possibilities of combinations should have their own role.

Now I dont know how to store this in a table.

I thought about creating a table like this:

role | page1 | page2 | page3 | page4 | page5 | page6 | delete | create | edit |
role1| yes   | yes   | no    | no    | no    | no    | yes    | yes    | yes  |

But I think there's way too many combinations to use this so there must be a better/efficient/easier way to do this.

So my question is:

What is the best/easiest way to do this?

marked as duplicate by Tulains Córdova, user40980, gnat, Bart van Ingen Schenau, user53019 Feb 17 '15 at 17:56

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.


You should define permissions for roles, not users. Users are assigned a role which specifies the type of access it has on a given page. Take a look at the following diagram:

Good examples for roles might be "manager", "admin", "user", "public". The advantage of doing it this way is that roles are easy to change later. If you add a page and decide that only admins can access it, changing it is as simple as changing the admin role, and all users with that role will take the change.

RolePermissions is where you will put the individual page permissions. It should have a name (that you will reference in your program as a key, or directly by resource name, though I would recommend by key), and a series of permissions. Typical permissions would likely be view, update, create, delete, though you should create at least a couple extra fields for custom permissions that may be specific to the page. View is a special permission that will determine whether or not the user can view that page, and if not, you should redirect to a 401 error page or a login page.

There is also the issue of what to do if you fail to find permissions for a given page. My thoughts are that you could automatically assume that the user has no permissions to view that page, however an alternative approach could be to have a special role that cannot (or should not at least) be deleted called public. If you find yourself in the circumstance that a user's role does not explicitly show permissions, you could refer automatically to public role permissions for the proper behavior. In this way, ultimately you'd only need to specify permissions that grant more leniancy towards non-public permission role rather than always having to specify permissions for every page and role.

Login information should be kept in Users table and should be separate from permissions. Also as @Encaitar mentioned in his answer, it would be wise to deal with password hashes, and not the passwords themselves, encrypted or otherwise.

  • According to the ER diagram a user can only be assigned one role, which is wrong. Take a look at this other ER diagram: programmers.stackexchange.com/a/206391/61852 – Tulains Córdova Feb 16 '15 at 23:58
  • @user61852 I feel one role to a user is perfectly acceptable in most cases. Otherwise you would have to take into consideration how multiple permissions coming from multiple roles are to be prioritized. I don't feel that simplicity is ever wrong nor is it a worthy excuse in itself of downvoting an answer. – Neil Feb 17 '15 at 11:52
  • What do you mean by "prioritize permissiones"? – Tulains Córdova Feb 17 '15 at 18:18
  • Also, in all places I've seen roles/groups being used, one user can de assigned more than one role or belong to more than one group. Examples: Oracle RDBMS, PostgreSQL RDBMS, MS SQL Server RDBMS, Linux OS, Unix OS, etc. Never seen a solution where users and roles don't have a many-to-many relationship. – Tulains Córdova Feb 17 '15 at 18:25
  • @user61852 And so logically you conclude that anything different is wrong? I'm not afraid to offer a simpler solution if there is nothing to be gained by having a more complicated solution. However, OP, if you prefer multiple roles to a user, you'd only have to create an intermediate table UsersRoles that associates any given user with any given role (rather than a single field in Users that determines a user's role). – Neil Feb 18 '15 at 8:48

"Good", "easy", and "efficient" are not only all subjective and ambiguous terms, very often they are in conflict with each other. Choosing a data representation is virtually always a trade-off between goodness of one kind and another kind.

Having separate fields for every read/write/edit right for each page makes for the simplest database layout: just a bunch of columns. But it's more effort to program against, since whatever code checks for edit rights on page 1 must be duplicated to work with page 2, page 3 etc. If you had a smarter data structure, the code would just have to be parametrized instead. Duplicated code is a well-known source of consistency defects: cut-and-paste jobs are 'simple' to write, but usually less simple to work with afterwards. If you expect to spend more time maintaining your program than on writing it in the first place, you should prioritize ease of maintenance. For instance, once a fourth page is invented, redoing the cut-and-paste is usually a tricky thing to get correct. Increasing a constant that represents a limit from 3 to 4 is trivial.

The downside of creating a structured data representation (e.g. a linking table with "page number", "user id" and "action type" columns) is that you expend a little more effort in planning and slightly more data storage in production. There are situations where this cost is prohibitive (e.g. if you have to fit your program into 4K bytes), but as an educated guess, I assume you're not in that situation, so you should invest a little effort up front to improve maintenance and readability of your code.

  • Yeah sorry with the "Good", "Easy", "Efficient" I didn't mean it like that. I meant more like what's the upside of using this and the downside of using something else. – Loko Feb 16 '15 at 8:26
  • So I think I'm going for the table with all the rights in columns. So the idea I already had. Im gonna wait for more answers first though. Thank you. – Loko Feb 16 '15 at 8:28
  1. Don't store passwords in a database, even encrypted passwords.
  2. Authentication and Authorisation are different concepts and unless space efficiency is very important in your database, you should consider splitting them up.
  3. Instead of having a column for each type of permission and what object that permission relates to you should take a look at encoding permissions like they do for *NIX file permissions.

Instead of storing passwords directly in a database you can hash (a function that is mathematically impossible to reverse) the password and store the result of the hash in the database. Then when a user tries to log in, do the same hash on the proposed password and compare it to the one stored in the database, if they match, the user entered the correct password. There are a number of issues with this; you need a hash function that doesn't have too much chance of collision (when more than one password resolve to the same output), complexity of the hash function (Moore's law will make it easier and easier to brute force attack), and there are pre-computed 'rainbow tables' that store password/hash pairs to worry about (you should 'salt' the passwords with a pseudo-random string so that they are different than the attacker is expecting).

If you keep the usernames/passwords(or hashes) and the authorisation separate then it will keep the logical units separate and you can split them up later if you end up using something like LDAP.

*NIX file permissions use octal and add the number together, so +4 for read, +2 for write, and +1 for execute, for owner, group, and other, so 751 would be read, write, execute for the owner, read and execute for anyone in the group with permissions and only execute permissions for everyone else.

  • Good point regarding password hash. – Neil Feb 16 '15 at 15:14
  • @Neil Im working with Laravel and Im not storing the password just like that. I use the Hash::make – Loko Feb 16 '15 at 15:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.