Purpose

• There are cases when admins need to perform an audit on users who have administrative access to HipChat Server
• Since HipChat Server does not come together with a built in audit tool to provide this information to the users, we need to pull the information directly from the database

Solution

• Log in to the database with the following command through the HipChat Server terminal / SSH console:

  DBPASS=$(awk '/"pass"/ {gsub(/[",]/,"");print $2}' /hipchat/config/site.json) && mysql -uroot -p$DBPASS hipchat

• Run the query below to get the access control list (acl) from the groups table and verify the users that have admin rights:

  SELECT acl FROM groups;

• The acl column contains a list of users id with admin and moderator (delegated admin) rights. The founder is the user id designated as the group owner.  For example:

  MariaDB [hipchat]> SELECT acl FROM groups;
  +-----------------------------------------------------+
  | acl                                                 |
  +-----------------------------------------------------+
  | {"admin":[1,216,230],"moderator":[235],"founder":1} |
  +-----------------------------------------------------+

  
  

• What the result above shows is that user id's 1, 216, and 230 are all admins on Hipchat. User 235 is a delegated admin, and user id 1 is also the founder (which automatically has admin rights, too).
  
• From there, you can identify the users further by querying the users table with their ids. For example:

  MariaDB [hipchat]> SELECT name, email FROM users WHERE id IN (1, 216, 230, 235);
  +-------------+--------------------------+
  | name        | email                    |
  +-------------+--------------------------+
  | User A      | usera@example.com        |
  | User B      | userb@example.com        |
  | User C      | userc@example.com        |
  | User D      | userd@example.com        |
  +-------------+--------------------------+