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.

  1. SELECT
  2. (CASE
  3. WHEN G.`group_name` = '' THEN 'User'
  4. ELSE 'Group'
  5. END) AS `Type`,
  6. (CASE
  7. WHEN G.`group_name` = '' THEN
  8. (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)
  9. ELSE G.`group_name`
  10. END) AS `group_name`,
  11. F.`forum_name`,
  12. (CASE
  13. 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'
  14. ELSE 'True'
  15. END) AS `Access`,
  16. (CASE WHEN AA.`auth_view`=1 THEN 'True' ELSE 'False' END) AS `Can View`,
  17. (CASE WHEN AA.`auth_read`=1 THEN 'True' ELSE 'False' END) AS `Can Read`,
  18. (CASE WHEN AA.`auth_post`=1 THEN 'True' ELSE 'False' END) AS `Can Post`,
  19. (CASE WHEN AA.`auth_reply`=1 THEN 'True' ELSE 'False' END) AS `Can Reply`,
  20. (CASE WHEN AA.`auth_edit`=1 THEN 'True' ELSE 'False' END) AS `Can Edit`,
  21. (CASE WHEN AA.`auth_delete`=1 THEN 'True' ELSE 'False' END) AS `Can Delete`,
  22. (CASE WHEN AA.`auth_sticky`=1 THEN 'True' ELSE 'False' END) AS `Can Sticky`,
  23. (CASE WHEN AA.`auth_announce`=1 THEN 'True' ELSE 'False' END) AS `Can Announce`,
  24. (CASE WHEN AA.`auth_vote`=1 THEN 'True' ELSE 'False' END) AS `Can Vote`,
  25. (CASE WHEN AA.`auth_pollcreate`=1 THEN 'True' ELSE 'False' END) AS `Can Create Poll`,
  26. (CASE WHEN AA.`auth_attachments`=1 THEN 'True' ELSE 'False' END) AS `Can Attachments`,
  27. (CASE WHEN AA.`auth_mod`=1 THEN 'True' ELSE 'False' END) AS `Can Moderate`
  28. FROM
  29. `phpbb_auth_access` AA
  30. LEFT JOIN `phpbb_forums` F ON AA.`forum_id`=F.`forum_id`
  31. LEFT JOIN `phpbb_groups` G ON AA.`group_id`=G.`group_id`
  32. ORDER BY
  33. `Type`, AA.`forum_id`, AA.`group_id`
  34. ;

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


Tags

  • Internet

Revisions

  • 5/29/2012 - Article published.