How to structure groups within a website? (admin, admin powers, user identity)

Question

I am going to be making a small user system but I have questions.

1. If I were to make a registration table (mysql), what is wrong with just storing the password and username into the database without encryption?

2. I am trying to think of how to build the admin portion. Should I just check a column in the database to see if user is admin or not? If true, then admin page will be revealed. 

3. For admin powers, let's say I have 3 powers: delete user, approve user, and move user. In a few scenarios, I may want to give some people only the ability to approve, or delete, or all, or any combination. How would I make this? I was thinking of having a column for each power and have the script check each column. Let's assume I have over 20 powers that will be added.

4. If I have a website where people can create groups and become admins of their groups and the these admins can give different combination of admin powers to people in their group (For ex, Zack creates and group called Mountain and grants one member the ability approve new group members and grants a second member the ability to delete members and assigns a third member the ability to delete and approve. How will I structure this in MySQL? Should I use a columns that say what group are they admin of and what ability do they have? E.g. columns: Delete, Approve, GroupMemberOf, GroupAdminOf and use checks.

I have an idea but I want to learn the more sophisticated ways.

Thanks for the answers so far, however, I am really looking for ideas on a structure ( Question 2 - 4 ). Please let me know if I can help clear up the question.

Answer 1

1. If you store passwords without encryption, you will be revealing all of your customer's passwords when someone manages to steal your database, or get read access to it. Sadly, most people reuse usernames and passwords between sites, so you are doing your users a big disfavor by not protecting their passwords. (Maybe they should know better, but most don't.)

Answer 2

2.. Yes

3.. That is simple if you have a finite number of permissions

4.. I wrote this a couple of years ago, and it's in heavy use at a large company. Contact me if you want more info on the schema.

Answer 3

1 - I would suggest you encrypt your passwords using a unique salt for each user. That way if your system is ever compromized, your users' passwords won't be available to the hackers (without more hacking on their part)

http://phpsec.org/articles/2005/password-hashing.html (a little old, but has some good info)

2, 3, 4 - All of this is possible with a wide array of web languages. I would suggest first reading up on database design and figuring out what sort of normalization and table schema would be ideal for your situation. If you decide to add more powers to the admin users in the future, you can design for that from the beginning and avoid any headaches in the future and still be within your current requirements.

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html http://www.peachpit.com/articles/article.aspx?p=30885

Answer 4

1. Hash user passwords with unique salts for each users, so that when your database can be accessed by an outsider, they cannot decrypt the passwords, and the salt mitigates rainbow table attacks.

2 - 4. Use a table for access levels (1: member, 2: moderator (approval), 3: admin), and use yet another different table for user permissions where you store many-to-many connections like this:

id (auto_increment)|usr_id|role_id|group_id
-------------------------------------------
1                  |1     |3      |-1
2                  |2     |2      |1
3                  |2     |3      |2
4                  |3     |1      |2

In your case, user 1 is admin for the whole site, user 2 is admin for group 3 and moderator for group 2, user 3 is member of group 2.

[EDIT:]

Some more thoughts on restricting powers for the different roles: Depending on your setup you should use some role enforcement on a per-page basis, e.g. in an MVC framework, I would extend the base controller to require a (role) authorization function that has to be called for each method, otherwise it should throw an exception. Methods (pages) that do not require the user to log in can use a dummy authorization.

So the authorization class will look like

class Authorization
{
    public $authorized = false;

    public function dummy()
    {
     $this->authorized = true;
    }

    public function member($usr_id, $group_id = null)
    {
     $sql = "SELECT usr_id FROM usr_roles WHERE usr_id = " . $usr_id . ($group_id !== null) ? " AND group_id " . $group_id : "";
     // count the results of $sql query
     // or some more stuff
     if ($results > 1)
     {
         $this->authorized = true;
     }
     else
     {
      $this->authorized = false;
     } 
    }

    // the other functions
}

Your new base controller class will look like this:

class BaseController extends Controller
{
    protected $authorization;
    public function __construct()
    {
     $this->authorization = new Authorization();
    }

    public function render()
    {
     if ($this->authorization->authorized === true)
     {
      parent::render
     }
     else
     {
      // redirect to not authorized page 
     }
    }
}

And finally in the end your controllers will look like:

class IndexController extends BaseController
{
    // some stuff, methods etc.

    // methods needs logged in user and user must be a member. 
    public function index()
    {
     $this->authorization->member($session_user->getId());
    }
}

[EDIT2:]

If you are not familiar with OOP, then you can do the following:

Here is a sample layout for a roles table:

role_id|role_name
-----------------
1      |member
2      |moderator
3      |admin

You can then make a function authorize() to include in all your files:

// role_name = "member", "moderator", "admin"
function authorize($usr_id = null, $role_name = null, group_id = null)
{
    // test for user in group and role, return boolean

}

In your files include this function and do the following

if (authorize($usr_id, "moderator", 2)
{
    // show stuff, if user with $usr_id is moderator for group 2
}
else
{
    // do something else
}
// stuff for all

Answer 5

1) Storing clear text passwords means that if anyone does get access to your database, they still won't know the password. The hype around salting and encryption passwords is quite silly, a simple method will do just fine for anyone.

$password = "Mypassword";
$salt = "a unique and constant string";
$password = md5($password.$salt);

What this does is encrypts a password through md5();, You can't get the password back after you encrypt it with md5(). Salting it also ensures that there's no way to just find the password.

If you want to compare and see if the password works, just md5 the input the exact same way like below:

$password = "check_this_password";
if(md5($password.$salt) === $originalPassword) 
{ 
    //same password
}

2) That question depends on how you want to integrate groups with the admin side of things. If there's no other permissions setup at all, it's safe to just store a single field in the database with the account level. Otherwise, use my next point to find out if they are an admin or not.

3) The simplest way to implement a permissions system is like this, there are other ways but it's best not to complicate it. If you wanted permissions for each group you could add an additional column like prm_group_id and find out each user in a particular group's permission. But anyway, here's how it works:

Create a table to hold the permissions

prm_user_id  | prm_permission
   0         | Admin
   0         | Delete
   1         | Add

Each row holds a flag or permission. Then it's quite simple to find out if they have the required permission through a bit of SQL and PHP.

function hasPermission($permission, $userID)
{
  $permissions = array();

  $sql = "SELECT prm_permission FROM user_permissions WHERE prm_user_id = $userID";
  $query = mysql_query($sql);

  while($data = mysql_fetch_array($query))
  {
     $permissions[] = $data['prm_permission'];
  }

  //Check if they have a permission with this:
  if(in_array($permission, $permissions))
  {
     return true;
  }
return false;
}

This allows you to add and delete permissions quite easily, and to check and see if they have a permission. Only downside is managing permissions can get abit tricky.

Answer 6

1. Obviously the suggested is to store it hashed, but there's benefits to not doing so as well. It really comes down to what you need.

2+. I threw together a little sql schema for this here

Basically, store the users, the groups, and the permbits you need in the separate tables. Then, associate users to groups, and permissions to the users in the groups. To give a person "all" you simply make sure that your programming logic (either PHP or MySQL stored proceedure) gives the new user all of the available permissions in the permissions table. To accomplish this in MySQL you could perform a query like this:

INSERT INTO group_user_permissions
    SELECT `group_user`.`groupid`, `group_user`.`userid`, `permission`.`permbit`
        FROM permission, `group_user`
    WHERE `group_user`.`groupid` = 1
        AND `group_user`.`userid` = 1

This will insert all possible permissions into the group_user_permission table for the groupid and userid given (in this case groupid 1 and userid 1).

Now, where ever you need to check permissions you can run a very simple query against the group_user_permission table to see if that user, in that group, has permission to do whatever it is they are trying to do. This is ultimately flexible because you won't have to alter tables if you need to rename permbits, add permbits, or remove permbits.

Additionally, because of the use of string keys in the permission table, you can use those values in a textual format that will make sense when reading them (vs using autoincrementing numbers). Making your PHP checks look like this:

if( $permbit === "approve" )

vs

if( $permbit === 1 )

Good luck!

Answer 7

I'm a little late to the game for this, but you might like to take a look at how CakePHP does its authorization. (If you're using PHP you might find that using the CakePHP framework gives you some of this control without having to code it.)

user-permissions-and-cakephp

Also, I've found it useful to just keep a list of actions (such as CRUD or Admin) against entities, so you'd apart from the normal user/password table you'd have a table with the fields as below.

PermID | Deny or Allow | Action | Entity

You then have another table that maps groups to PermId and another that maps users & groups (recursively if necessary) into groups (as people have already talked about).

The Deny or Allow means that you can create ACLs to allow use or DACLs to deny use if the default is to allow an action (which probably isn't the best idea normally), or if there is a smaller group that has to be denied access within a larger one. You'd generally check first to see if there was an explicit Deny on an action against an entity for a group/user.

So you might have something like:

PermID | Type  | Action  | Entity
-------+-------+---------+------------
  1    | Allow | Read    | User_Entry
  2    | Allow | Delete  | User_Entry
  3    | Allow | Move    | User_Entry
  4    | Allow | Approve | User_Entry
  5    | Allow | Create  | User_Entry

So, as an illustration, an admin group would map to entries 1-5, a "normal user" group might map to 1 & 5 only. (For group, read "person" or "user" if you want).

Id | Grp    | PermId
---+--------+-----
 1 | Admin  | 1
 2 | Admin  | 2
 3 | Admin  | 3
 4 | Admin  | 4
 5 | Admin  | 5
 6 | Normal | 1
 7 | Normal | 5

Obviously this is easily extensible as when a new action or entity (e.g. directory) arises in the system it's just a case of adding the appropriate entries into the tables without the need to change schema. You can use actions of "All", for example, and use a DACL for a particular exclusion. Here's a Power User that can do everything but delete a User_Entry.

PermID | Type  | Action  | Entity
-------+-------+---------+------------
  6    | Allow | All     | User_Entry
  7    | Deny  | Delete  | User_Entry


Id | Grp    | PermId
---+--------+-----
 8 | Power  | 6
 9 | Power  | 7

In your case I'd imagine that the Entities could incorporate the group name, or some token that would mean "your home group" or something e.g. {homegrp}/UserEntry, rather than just UserEntry which a sysadmin might have.

Sorry if that was a bit woffley but I hope you get the gist of it. (Apologies if any previous answer talked about this as I don't think I read all the way through...)

Answer 8

For points 2-4, you might want to look at Role-Based Access Control.