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!
