JasonDavis JasonDavis - 3 months ago 12
MySQL Question

Simple PHP/MySQL ACL System

I have a simple ACL system in PHP and MYSQL started. I need help finishing it though...

I have 2 Database tables shown below...

user_link_permissions : Holds a record for every user, on every entity/link that permissions apply to...

--
-- Table structure for table `user_link_permissions`
--
CREATE TABLE IF NOT EXISTS `user_link_permissions` (
`id` int(100) NOT NULL AUTO_INCREMENT,
`user_id` int(30) NOT NULL,
`link_id` int(30) NOT NULL,
`permission` int(2) NOT NULL DEFAULT '0',
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2055 ;


intranet_links : Is basically the entity that the permission gives or revokes user access to

--
-- Table structure for table `intranet_links`
--
CREATE TABLE IF NOT EXISTS `intranet_links` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`description` text NOT NULL,
`url` varchar(255) DEFAULT NULL,
`notes` text,
`user_login` varchar(255) DEFAULT NULL,
`user_pw` varchar(255) DEFAULT NULL,
`active` int(2) NOT NULL DEFAULT '1',
`sort_order` int(11) DEFAULT NULL,
`parent` int(10) NOT NULL DEFAULT '1',
`local_route` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `local_route` (`local_route`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;





To save these permissions settings I have a matrix style grid like this below where each checkbox is a record in the user_link_permissions table...

enter image description here




I need help creating a simple ACL function in PHP which can check if a user has permission or not to view a link/entity based on the database results.

On page load I am thinking I can query the
user_link_permissions
DB table for all records with a matching user ID of the logged in user and store them to a session array variable.

A function could then use that array to check for a link/entity permission using that array value on the entity key.

I just can't visualize how it might look at the moment in PHP.

Any help please?

function aclCanAccess($user_id, $entity_id){


}


$entity_id = 123;
if(aclCanAccess(1, $entity_id){
// yes user can see this item
}else{
// NO user permission denied
}

Answer

I will leave writing the code to you for fun.

Assume you are storing all the previously queried permissions in a variable called $_SESSION['acl']

Your ACL function should:

  1. check the session if you already queried that entity
  2. if it is not set, read it from the db

in short

function..... {
    if(!isset($_SESSION['acl'][$entity_id])) {
        $_SESSION['acl'][$entity_id] = query here to return to you if he has access or not
    }
    return $_SESSION['acl'][$entity_id];
}

You can also read the entire array when you log in the user. That might also be appropriate. In that case you should be able to just

return $_SESSION['acl'][$entity_id];

But I would then try and catch an exception in case it is not set.