Over the last couple of months, whilst working on several projects at work, I’ve also been developing our base base framework, which is built on top of CakePHP, that we use as a start point for all our projects.

I finally got to the point where I needed to add in authentication and access control to the CMS for the projects, enter CakePHP Auth component and ACL behavior/component.

All went smoothly until I got to the point that after logging in, I wanted to load all the permissions for the User into the session, so I can do things like check if the user has permission to add a new anything, before displaying a link to that action.

I used Mark Story‘s excellent ACL extras shell to scan my controllers (with some of my own additions to scan plugin controllers, which I’ll add through git hub shortly), and I ended up with 250 ish acos.

After logging in I used acos->find all then iterated through the list doing ACL->check with the logged in user id.

Each check does 4 queries (in my app):

1. Find the aro for model User, foreign_key <logged in user id>
2. Find the aco for the alias <controller>/<action>
3. x2 Checks for permissions (could could be more ore less depending on your acos/aros hierarchies)

Queries 1 and 3 were quick, but the 2nd one to find the aco was painful at 300ms due to 3 self joins on the acos table.

I hunted around for some solutions and found a couple of posts suggesting additional indexes (indices?) on the table, so I tried adding them and it had a positive effect, halving the query time down to ~150ms, but stil not good enough. Logging in was taking a lengthy minute on my development machine, which  is fairly high spec.

Clearly the CakePHP ACL component check method was not a viable solution – it’s fine for checking access to a single aco, but throw any number greater than about 10 into the mix and your starting to notice a significant lag in your login process.

With debug at 2 I sent the problem query to a friend and colleague who is a little bit good at SQL.

He sent it back with some conditions on the joins rather than in WHERE clause, thus reducing the initial size of the result set that the database engine needs to create before applying the WHERE conditions, and this speeded it up by a factor of 5 – returning in 30ms.

But, 30 x 250 is still… a lot of milliseconds, and I couldn’t be bothered to figure out how to integrate this aco finding query into the ACL check without hacking the core, so I promised my friend/colleague a pint of beer if he could figure out how to get all the permissions for a given user out of the database in one go.

And the little diamond only went and bloody did it didn’t he?

Not only that – even with 250 acos it’s lightning quick as well.

Tests so far show it’s working a treat on my aros/acos/aros_acos setup, which is pretty standard.

I’ve got users and groups, where users belong to group, ~250 acos, with “controllers” as my root node, then a list of controller names under that, e.g. Posts, Widgets etc, then a list of actions under each controller, e.g. admin_add, admin_edit etc.

I’ve granted a super user group full access to controllers, i.e. everything, and the client group full access to most controllers, but not ones that are system and admin settings like Groups etc.

The query takes 70ms. Don’t ask me how it works – I ain’t got a scooby, but it does, for this setup anyway. So here it is in a method in my User model:

  function permissions($userId) {
    $this->id = $userId;
    $node = $this->node();
    $aroId = $node[0]['Aro']['id'];

    $sql = "SELECT DISTINCT alias
  FROM
    (
  SELECT
  user_id.aro_id
  ,(
  SELECT
case when controler is null then method WHEN master is null then concat(controler,'/',method) else concat(master,'/',controler,'/',method) end as alias
FROM
  (SELECT id,`alias` as method, (SELECT `alias` FROM acos WHERE id=t.parent_id) as controler, (SELECT `alias` FROM acos WHERE id=(SELECT `parent_id` FROM acos WHERE id=t.parent_id)) as `master` FROM acos t) Aco
WHERE Aco.id=u.aco_id OR Aco.id=p.aco_id
  ) as name,
  CASE WHEN u.aco_id=0  OR u.aco_id is NULL  THEN p.aco_id  ELSE u.aco_id  END as aco_id,
  CASE WHEN u._create=0 OR u._create is NULL THEN p._create ELSE u._create END as _create,
  CASE WHEN u._read=0   OR u._read is NULL   THEN p._read   ELSE u._read   END as _read,
  CASE WHEN u._update=0 OR u._update is NULL THEN p._update ELSE u._update END as _update,
  CASE WHEN u._delete=0 OR u._delete is NULL THEN p._delete ELSE u._delete END as _delete
  FROM
  (SELECT 'a' as flag,$aroId as aro_id) user_id
   LEFT JOIN
  (
  SELECT 'a' as mark1,aco_id,`_create`,`_read`, `_update`, `_delete` FROM `aros_acos` AS `Permission` LEFT JOIN `aros` AS `Aro` ON (`Permission`.`aro_id` = `Aro`.`id`) LEFT JOIN `acos` AS `Aco` ON (`Permission`.`aco_id` = `Aco`.`id`) WHERE `Permission`.`aro_id` = $aroId  ORDER BY `Aco`.`lft` ) u ON u.mark1=user_id.flag
  LEFT JOIN
    (
  SELECT 'a' as mark2,aco_id,`_create`,`_read`, `_update`, `_delete`  FROM `aros_acos` AS `Permission` LEFT JOIN `aros` AS `Aro` ON (`Permission`.`aro_id` = `Aro`.`id`) LEFT JOIN `acos` AS `Aco` ON (`Permission`.`aco_id` = `Aco`.`id`) WHERE `Permission`.`aro_id` = (
              SELECT id
              FROM aros
              WHERE id=(SELECT parent_id from aros WHERE id=$aroId)
            )
 )p ON p.mark2=user_id.flag
WHERE u._create=1 OR u._read=1 or u._update=1 or u._delete=1
OR p._create=1 OR p._read=1 or p._update=1 or p._delete=1
           ) tt
                      LEFT JOIN
           (
SELECT `Aco`.`id` , `Aco`.`parent_id` , `Aco`.`model` , `Aco`.`foreign_key` , `Aco`.rght,`Aco`.lft,
case when controler is null then method WHEN master is null then concat(controler,'/',method) else concat(master,'/',controler,'/',method) end as alias
FROM
  (SELECT *, `alias` as method, (SELECT `alias` FROM acos WHERE id=t.parent_id) as controler, (SELECT `alias` FROM acos WHERE id=(SELECT `parent_id` FROM acos WHERE id=t.parent_id)) as `master` FROM acos t) Aco
           ) tmp_aco
           ON tmp_aco.id=tt.aco_id OR tmp_aco.alias like concat(tt.name,'%')
    ";

    $permissions = $this->query($sql);
    $permissions = Set::extract('/tmp_aco/alias', $permissions);
    return $permissions;
  }

The query my friend/colleague gave me had the ‘*’ (all fields) wildcard instead of the ‘DISTINCT alias’ string in the fields list at the start of the query. This returned all the aros_acos fields including the CRUD flags, so adding this back in might work for you too if you use the CRUD flags in your ACL implementation. But I think you’ll need to add a GROUP BY alias clause as well, because without the DISTINCT keyword I got a few duplicates in the result set.

To add the permissions to your session at login, be sure to set Auth->autoRedirect to false and have something like this in your UsersController:

  function webadmin_login() {
    if ($this->Auth->user()) {
      $this->Session->write('Auth.Permissions', $this->User->permissions($this->Auth->user('id')));
      $this->redirect($this->Auth->redirect());
    }
  }

Now you can add a permission check method to your AppHelper ….

  function hasPermission($url) {

    if (!is_array($url)) {
      return false;
    }

    extract($url);

    if (!isset($controller)) {
      $controller = $this->params['controller'];
    }

    $controller = Inflector::camelize($controller);

    if (!isset($action)) {
      $action = $this->params['action'];
    }

    $_admin = Configure::read('Routing.admin');

    if ((isset(${$_admin}) && ${$_admin}) || $this->params['action'][$_admin]) {
      $action = $_admin.'_'.$action;
    }

    $permission = 'controllers/'.$controller.'/'.$action;

    return in_array($permission, $this->Session->read('Auth.Permissions'));

  }

…and call it in your views, where the $url param is exactly what you’d send to $html->link($title, $url), i.e. an array containing optional keys for ‘controller’, ‘action’ and ‘<whatever your Routing.admin is set to>’.

To simplify the “echo link if permitted” concept, I’ve also written a helper method called, wait for it, $app->linkIfPermitted() ;-)

It accepts the same params as HtmlHelper::link(), but does a hasPermission check before returning the link.

So hey, try it out, if it works for you, and you like it, send love and best wishes to Ben Roberts, and come back and leave me a comment to let me know.

If the SQL part doesn’t work for you, don’t come back and ask me how to get it to work, ‘cus I won’t be able to help you!