Adding OLab users in bulk

User management for large groups of users is a bit of a pain in OpenLabyrinth v3. You can do this using the regular OLab3 interface but that means doing them one at a time, which is time consuming, boring and prone to error. In OLab4, using the Entrada platform, it will be much easier to tie into industrial strength user management options such as OpenLDAP, OAuth, IMS-LTI, etc.

In the meantime, it is sometimes better to do using direct manipulation of the underlying SQL tables. For internal purposes, we have documented the steps here. If you have access to the underlying SQL tables on your own OLab server, and are comfortable messing with SQL queries, you might find this helpful.

To add a group of users, with a predefined semi-anonymous username, and a common password:

  1. Create a single user account in OLab, using the standard method. We have found it convenient to have a common user name, such as ‘tester1‘, which will increment by 1 for each additional user (tester2..tester19 etc) in the set of users. Make sure that the username does not already exist. We also tend to use a common password for all members in the group for easy setup. We recommend that you tell users to change their password the first time that they login. We also tend to use a common firstname and email address for the group — again, users can edit these after they have logged in. For setup purposes, we often make the lastname the same as the number in the username. The Excel sheet will automatically increment these two values. Save the user details in OLab, as usual.
  2. Login to the MySQL database on your OLab server, using your favorite SQL editor. We like Sequel Pro but it should not matter what you use.
  3. Open the table named ‘users‘ and find the last row, which you just created in step #1, and note the max value of users.id — add 1 to this for your starting id number for the new batch of users. Note that you should do this when your OLab server is otherwise quiet, to avoid generating index key conflicts with duplicate id numbers.
  4. Copy the hashed password from users.password into your clipboard.
  5. Download a copy of this Excel spreadsheet — OLab3usersBulkAdd. This adds 50 users at a time by default but it can be easily edited to increase or decrease this amount.
  6. Use the Sheet named ‘Variable‘ to enter your starting points. You should only need to edit the cells highlighted in yellow. The remaining values should set themselves automatically.
  7. In cell A2, insert the starting id number. The rest of column A should adjust accordingly.
  8. In cell C2, insert the username prefix (eg. ‘tester‘). The rest of column C should adjust accordingly.
  9. In cell D2, insert the username number suffix (eg. ‘2‘). This should be one greater than the number used in the username in step #1. The rest of column D should adjust accordingly.
  10. In cell I2, insert the same username number suffix (eg. ‘2‘). This should be one greater than the number used in the username in step #1. The rest of column I should adjust accordingly.
  11. In cell E2, insert the hashed password value. The rest of column E should adjust accordingly.
  12. In cell F2, insert the email address. The rest of column F should adjust accordingly.
  13. In cell H2, insert the firstname. The rest of column H should adjust accordingly.
  14. You can edit the worksheet to have more or less rows if you do not need 50 users.
  15. Visually check the values in the worksheet are correct before you go further. It is easy to make changes at this stage. It is harder once the new batch of users has been created in OLab.
  16. Save the Excel sheet as a CSV. Excel may complain about not being to save more than one Sheet. Save the active Sheet. Excel has the odd feature that it creates a separate CSV file but also creates a new sheet within the original spreadsheet, with the same name.
  17. You can now close the XLS spreadsheet. Open the new standalone CSV file that you just created, also using Excel.
  18. For a clean Import, you should now delete the following columns (not just their data):
    1. col H = firstname
    2. col I = lastname
    3. col C = usernamePrefix
    4. col D = userCount
  19. Using your SQL editor, eg. Sequel Pro, ensure you are still connected to the table ‘users‘.
  20. Use File | Import CSV, with the option checked for ‘first row contains field names’. This should take only a couple of seconds.
  21. If all has worked correctly, you should now have a new batch of 50 users.