Implementing Roles based access control in php application

Question

I have been working on a PHP application for my college that requires role based access control. The approach I followed was to create a separate MySQL user for each role. So every role had a separate db user. Each of these db users had privileges on a minimal set of tables that was required for the role.

Now, my question is whether this is infact a proper way to handle a role based application. The reason I have come into this doubt is because now when the time has come to get the system online (with GoDaddy hosting) I found out that they do not allow creating users with table specific privileges. Infact they don't even allow creating users directly through a SQL script(Role creation on the system involved creating users through a php script executing a SQL command).

Due to this I am now thinking if my approach was infact the correct approach or not. What is the standard way of implementing such role based systems?

This was my first live project so I don't really had much previous experience in terms of actually delivering a real project.

Answer 1

Unless you are not a hoster you probably don't want to create database users dynamically via scripts. Think about it:

A database user is a component that interacts with your application and not with your end-users.

So stick with one database for your application and use dedicated tables to build your ACL. Your database schema could look like this:

users( id:pk, name )
roles( id:pk, name )
permissions( id:pk, key, description )
permission_role( permission_id:fk, role_id:fk )
role_user( role_id:fk, user_id:fk )

You basically have three things here:

• Users: Nothing special. Just a user that can be uniquely defined by it's id
• Permissions: Basically just a store of keys that will be queried within your script to check for permissions.
• Roles: The glue between Users and Permissions. The sum of roles that a User belongs to and it's permissions will define the entirety of what a user is permitted to do and what not.

That's the basic setup. The other tables are just helper tables to join pieces together.

Your code handles the rest. Setup a (or better more) class that do the heavy lifting. Then pass an instance of your ACL to your User class (other implementations are of course possible. See at the bottom of the post).

<?php 

class Acl implements AclInterface {

    public function hasPermissionTo( $action )
    {
        // Query DB and check if a record exists
        // in the role_user table where the 
        // user_id matches with the current user
        // and join the role_id with `roles` and then
        // with `permission_role` to see if the user
        // is permitted to perform a certain action
    }

}

class User {

    protected $acl;

    public function __construct( AclInterface $acl )
    {
        $this->acl = $acl;
    }

    public function hasPermissionTo( $action )
    {
        return $this->acl->hasPermissionTo( $action );
    }

}

You should get the basic concept. The actual implementation is up to you. Things you may want to consider:

• Do you want your ACL to be part of your User or rather a standalone component?
• How do you want to pass the current user to the ACL? Do you want to pass an instance of the current user to the ACL, or rather only the user ID?

Those question depend on what you like and on your architecture. Happy Coding!