How to remove groups from Crowd via the database

Still need help?

The Atlassian Community is here for you.

Ask the community

This article only applies to Atlassian's server products. Learn more about the differences between cloud and server.

Purpose

The Crowd UI does not currently provide a way to bulk delete groups.  If a customer has mistakenly created 100s of groups due to a mistake in configuring their LDAP filter, there is no easy way to remove the extraneous groups. SQL commands can be used to remove the unwanted groups, provided you take proper care.

When making the following actions...

  • It's important to shut down Crowd while deleting the groups to prevent your users from encountering errors.
  • A restart is also necessary to reset in-memory caches of users and groups.
  • If you use LDAP for user management, groups will be re-created on the next sync unless you modify your LDAP filter to exclude the groups in question, or you remove those unwanted groups from your LDAP directory.

  • Our warning about testing SQL queries on a test installation is particularly important here. Once you delete groups or group memberships they are irrevocably gone.

Diagnosis

  • You have 50+ groups in Crowd that interfere with your ability to manage group membership. You are unable to remove the groups via the UI or by modifying the LDAP filter. 

Workaround

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

Before proceeding with the instructions below, check for usage of these groups in the Crowd UI (http://<baseurl>/crowd/console/secure/group/browse.action) to be sure that removing these groups will not break your instance, and then use the queries provided below to make additional checks. 

IMPORTANT:  The user and group names in the queries are case sensitive.  These queries have been tested in Postgres and MySQL and were tested in Crowd 2.8. You may need to modify the syntax to work for other supported databases. 

Section 1: Check to see if the group(s) is safe to delete: 

NOTE: A group that is safe to delete has no members and is not referenced in any permissions.

  1. Run the SQL script below to find any empty groups: 

    SELECT id, lower_group_name FROM cwd_group
    WHERE cwd_group.id NOT IN
    (SELECT cwd_membership.parent_id FROM cwd_membership);

    Note: The ids and group name will be used in subsequent scripts.

  2. Run the SQL script below to confirm that the groups to be deleted do not contain any members:

    SELECT * FROM cwd_membership
    WHERE parent_id IN (id1, id2);


  3. Run the SQL script below to check if groups are used in permission mapping:
    Note: Permission mapping is only used when "Allow all to authenticate" is set to "FALSE", in which case specific groups needs to be defined so that only those groups' members can authenticate.

    SELECT * FROM cwd_app_dir_group_mapping 
    WHERE group_name IN ('group-to-delete', 'another-group-to-delete');

    Note: Note the id to be used in the next script.
     

  4. Run the SQL script below to check if groups are used to grant permissions. Use the id from the script above:

    SELECT * FROM cwd_granted_perm
    WHERE group_mapping IN (id1, id2);

Section 2:  SQL scripts to remove groups

When you are certain it is safe to remove the groups run these scripts in order: 

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

  1.  Remove group members, if any users were returned in Step 2 above: 

    DELETE FROM cwd_membership 
    WHERE parent_id IN (id1, id2)
    
  2. Remove any permission mapping associated with the groups, if any results were returned in Step 3 above: 

    DELETE FROM cwd_app_dir_group_mapping 
    WHERE group_name IN ('group-to-delete', 'another-group-to-delete');
  3. Remove any granted permissions associated with the groups. 

    DELETE FROM cwd_granted_perm
    WHERE group_mapping IN (id1, id2);

    Note: Use the id obtained in Section 1, Step 3. 
     

  4. Remove the groups: 

    DELETE FROM cwd_group 
    WHERE group_name IN ('group-to-delete', 'another-group-to-delete');
Last modified on Feb 17, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.