phpbb2 User & Group Security Audit

The security settings in phpbb2 make it rather difficult to get a good idea of what a user has access to. Sure you can see permissions for a group and see permissions for a user (and group memberships), but it's a lot of jumping around and analyzing settings.

The forum I maintain makes heavy use of groups, so this query makes it easy to identify outliers - specifically users with additional settings beyond the group level.

                WHEN G.`group_name` = '' THEN 'User'
                ELSE 'Group'
              END) AS `Type`,
                WHEN G.`group_name` = '' THEN 
                  (SELECT U.`username` FROM `phpbb_user_group` UG LEFT JOIN `phpbb_users` U ON UG.`user_id`=U.`user_id` WHERE UG.`group_id`=AA.`group_id` LIMIT 1)
                ELSE G.`group_name`
              END) AS `group_name`,
                WHEN AA.`auth_view`+AA.`auth_read`+AA.`auth_post`+AA.`auth_reply`+AA.`auth_edit`+AA.`auth_delete`+AA.`auth_sticky`+AA.`auth_announce`+AA.`auth_vote`+AA.`auth_pollcreate`+AA.`auth_attachments`+AA.`auth_mod` = 0 THEN 'False'
                ELSE 'True'
              END) AS `Access`,
              (CASE WHEN AA.`auth_view`=1 THEN 'True' ELSE 'False' END) AS `Can View`,
              (CASE WHEN AA.`auth_read`=1 THEN 'True' ELSE 'False' END) AS `Can Read`,
              (CASE WHEN AA.`auth_post`=1 THEN 'True' ELSE 'False' END) AS `Can Post`,
              (CASE WHEN AA.`auth_reply`=1 THEN 'True' ELSE 'False' END) AS `Can Reply`,
              (CASE WHEN AA.`auth_edit`=1 THEN 'True' ELSE 'False' END) AS `Can Edit`,
              (CASE WHEN AA.`auth_delete`=1 THEN 'True' ELSE 'False' END) AS `Can Delete`,
              (CASE WHEN AA.`auth_sticky`=1 THEN 'True' ELSE 'False' END) AS `Can Sticky`,
              (CASE WHEN AA.`auth_announce`=1 THEN 'True' ELSE 'False' END) AS `Can Announce`,
              (CASE WHEN AA.`auth_vote`=1 THEN 'True' ELSE 'False' END) AS `Can Vote`,
              (CASE WHEN AA.`auth_pollcreate`=1 THEN 'True' ELSE 'False' END) AS `Can Create Poll`,
              (CASE WHEN AA.`auth_attachments`=1 THEN 'True' ELSE 'False' END) AS `Can Attachments`,
              (CASE WHEN AA.`auth_mod`=1 THEN 'True' ELSE 'False' END) AS `Can Moderate`
              `phpbb_auth_access` AA
              LEFT JOIN `phpbb_forums` F ON AA.`forum_id`=F.`forum_id`
              LEFT JOIN `phpbb_groups` G ON AA.`group_id`=G.`group_id`
            ORDER BY
              `Type`, AA.`forum_id`, AA.`group_id`

It does require database access, and is intended for MySQL databases. It's been tested again phpbb2 v2.0.22.


  • Internet


  • 5/29/2012 - Article published.