Understanding Joolma! ACL tables

I noticed strange behavior with my Joomla site regarding user groups in the backend user manager. The usergroup (author, publisher, etc) was listed incorrectly. When clicking on the user to get the full details it was listed correctly. Checking with phpMyAdmin revealed the usertype and gid fields were set correctly so what's going on? More poking around the database revealed some strange tables prefixed with _core_acl. Access control list? Ahhh….

A quick note of the more interesing fields of these tables: 

*Start from the users table*

jos_users.id

jos_users.usertype = Registered/Author/Administrator/Public Frontend/etc

jos_users.gid

jos_users.name (Full name, not username)

*List of access request objects, ie users!*

jos_core_acl_aro.value = jos_users.id (user id)

jos_core_acl_aro.name = jos_users.name

jos_core_acl_aro.id = 25 for Andy

jos_core_acl_aro.section_value = 'users' for all

*List of user access types familiar to Joomla*

jos_core_acl_aro_groups.id = jos_users.gid

jos_core_acl_aro_groups.parent_id = id of object with next less permission

jos_core_acl_aro_groups.name = jos_users.usertype

jos_core_acl_aro_groups.lft = ???

jos_core_acl_aro_groups.rgt = ???

jos_core_acl_aro_groups.value = jos_users.usertype = jos_core_acl_aro_groups.name

*Currently empty*

jos_core_acl_aro_map

*ARO sections, currently only users defined*

jos_core_acl_aro_sections.id = 10

jos_core_acl_aro_sections.value = users

jos_core_acl_aro_sections.name = Users

*groups to aro map - is this where the magic happens?*

jos_core_acl_groups_aro_map.group_id = jos_users.gid

jos_core_acl_groups_aro_map.aro_id = jos_core_acl_aro.id

jos_core_acl_groups_aro_map.section_value = currently empty

From looking at the data in these tables this is how I think (I'm using educated guesswork here!) it all works:

Link users to ARO objects, using user id:-

jos_users.id = jos_core_acl_aro.value

Map aro objects to access groups, using the magic map:-

jos_core_acl_aro.id = jos_core_acl_groups_aro_map.aro_id

Link access groups to access group description using group id:-

jos_core_acl_groups_aro_map.group_id = jos_core_acl_aro_groups.id

In a perfect world after this link 

jos_core_acl_aro_groups.id will equal jos_users.gid and

jos_core_acl_aro_groups.name will equal jos_users.usertype

 

Lets try a SQL query to test this, which will list the jos_users version of the user type alongside the same obtained from the acl tables:

SELECT jos_users.name, jos_users.gid, jos_users.usertype, 

 jos_core_acl_aro_groups.id, jos_core_acl_aro_groups.name

 FROM `jos_users`, `jos_core_acl_aro`, `jos_core_acl_aro_groups`, `jos_core_acl_groups_aro_map`

 WHERE jos_users.id = jos_core_acl_aro.value

 AND jos_core_acl_aro.id = jos_core_acl_groups_aro_map.aro_id

 AND jos_core_acl_groups_aro_map.group_id = jos_core_acl_aro_groups.id;

 

What has caused my problems is I've mistakenly altered the gid and usertype fields in jos_users directly so it doesn't correlate with the correct ACL values. To correct I need to alter the magic map table:

UPDATE `jos_core_acl_groups_aro_map`, `jos_users`, `jos_core_acl_aro`, `jos_core_acl_aro_groups`

 SET jos_core_acl_groups_aro_map.group_id = jos_users.gid

 WHERE jos_users.id = jos_core_acl_aro.value

 AND jos_core_acl_aro.id = jos_core_acl_groups_aro_map.aro_id

 AND jos_core_acl_groups_aro_map.group_id = jos_core_acl_aro_groups.id;

 

This seems to now work with the backend user list now showing the correct user type.

 

Result!