Add a special group to all frontend users in TYPO3

I recently had the task to clean up a TYPO3 instance with round about 25000 frontend users. The goal was to reduce the 40 TYPO3 frontend groups to a minimum as the security policy changed. So the most of these groups have been deleted. As the user has to be part of at least one group to be able to login to the frontend, I had to assure that each user is member of a basic group. You can imagine I did not want to do that by hand in the TYPO3 backend for 25000 TYPO3 frontend users. So I thought about a possibility to do that with an SQL query.

Get started with the TYPO3 group juggling job

Let’s assume the basic TYPO3 group is number 2. We want to add this group to everybody who is not a member. We have to pay attention to two constraints:

  1. The group should not be added to accounts which are already members
  2. If there exist accounts without any group membership we have to add these first

This results in two queries. Number one adding the group to all accunts with an empty “usergroup” column. Number two adds “2,” upfront all the other groups for all accounts not in group number 2.

  1. UPDATE fe_users SET usergroup=”2″ WHERE usergroup = “”;
  2. UPDATE fe_users SET usergroup=CONCAT(‘2,’ , usergroup) WHERE FIND_IN_SET(‘2’, usergroup) = 0;

As always: Try it on a testsystem upfront and make a backup of your database before you use this in real life. Beside that: Have fun…

If you had problems following this TYPO3 tutorial, please let me know in the comments. If it helped you or you think it can help others feel free to spread the word by sharing and liking the article. It also would help me improving the upcoming posts.

2 Comments

  1. Why is the second constraint important? (And therefore, why the first query?)
    Additionally, if you didn’t use an existing group but created a new one just for the purpose of assigning everyone to at least one group, you could simplify the all of this to just
    UPDATE fe_users SET usergroup=CONCAT(’2,’ , usergroup) WHERE 1
    Or am I missing something?

    Reply
    • Ok, there are two cases:
      1) No group attached: If I would add “2,” to this fe_user you would have a list of two groups “2” and “NULL” as this is splitted by the “,”. Therefore I have to first add it without the comma.
      2) Group 2 already attched: If there is already group 2 attached I do not want to add it again. Therefore I only add it to the users who are not already a member of group 2.
      Does that make sense?

      Reply

Leave a Comment.