User roles in database

Question

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?

Answer 1

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.

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

Answer 3

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.