3

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?

Improve this question
1

3 Answers 3

Reset to default
6

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.

Improve this answer
7
  • 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 Commented Feb 16, 2015 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. Commented Feb 17, 2015 at 11:52
  • What do you mean by "prioritize permissiones"? Commented Feb 17, 2015 at 18:18
  • 1
    @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). Commented Feb 18, 2015 at 8:48
  • 1
    @user61852 That is needlessly complicating things imho. While it's true what you say, chances are, if you need to add permission G to manager role, you'll want all users with manager role also with permission G as well. Permissions generally don't change often, and so you're complicating both your program and the user interface just so that your client can create a "managerG" role that only has permissions for G. It's more complete, I'll give you that. That said, I wouldn't recommend it in most cases. It is this type of thinking that creates convoluted and incomprehensible programs. Commented Feb 20, 2015 at 10:48
2

"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.

Improve this answer
2
  • 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. Commented Feb 16, 2015 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. Commented Feb 16, 2015 at 8:28
1
  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.

Improve this answer
2
  • Good point regarding password hash. Commented Feb 16, 2015 at 15:14
  • @Neil Im working with Laravel and Im not storing the password just like that. I use the Hash::make Commented Feb 16, 2015 at 15:40

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.