How To Create a Copy of a Group Membership to a New Group in Crowd
In a scenario that you need to create a group containing almost the same users of an existing group, making a copy of that group and then removing the users that should not be there would be much easier than manually adding everyone to the new group one by one, through the UI. In this case we can trick the CSV Importer to import only new memberships instead of users in Crowd.
To generate a CSV export from a specific group in Crowd, please follow the steps below:
Run the query below against your Crowd Database to identify the ID of the directory your group belongs to:
SELECT * FROM cwd_directory;
Then double check the directory_id for the group:
SELECT * FROM cwd_group WHERE group_name = 'group_name';
For example, the group I want to make a copy is called confluence-users and this group is from my LDAP directory. Take a note of the directory ID to use in the next query.
Now we'll need to generate the Group Memberships CSV file based on the chosen group:
SELECT u.user_name, g.group_name FROM cwd_user u JOIN cwd_directory d ON u.directory_id = d.id JOIN cwd_membership m ON u.id = m.child_id JOIN cwd_group g ON g.id = m.parent_id WHERE u.directory_id = <id> AND g.group_name = 'group_name' ORDER BY 2 ASC, 1 ASC INTO OUTFILE '/path/to/memberships.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
/path/to/memberships.csvwith the full path to the memberships csv file that you wish to generate; the
<id>with the ID of the Directory from the earlier SELECT query (first step); and the group_name you want to copy the membership from.
\COPY (SELECT u.user_name, g.group_name FROM cwd_user u JOIN cwd_directory d ON u.directory_id = d.id JOIN cwd_membership m ON u.id = m.child_id JOIN cwd_group g ON g.id = m.parent_id WHERE u.directory_id = <id> AND g.group_name = 'group_name' ORDER BY 2 ASC, 1 ASC) TO membership.csv CSV DELIMITER ','The membership.csv file will be saved on the current working directory. Replace the
<id>with the ID of the Directory from the earlier SELECT query (first step) and the group_name you want to copy the membership from.In case you are using PgAdmin, please run the query from the Other Databases section and save the result as a CSV using the same character as delimiter.
For Oracle and SQL Server:ここをクリックして展開...
SELECT u.user_name, g.group_name FROM cwd_user u JOIN cwd_directory d ON u.directory_id = d.id JOIN cwd_membership m ON u.id = m.child_id JOIN cwd_group g ON g.id = m.parent_id WHERE u.directory_id = <id> AND g.group_name = 'group_name' ORDER BY 2 ASC, 1 ASC
Use your native database tools to generate a CSV export out of the output from this query. Remember to use comma (,) as delimiter on the CSV.
Since we must set the path for a valid CSV file to import users on Crowd, we'll need to create an empty CSV containing only 4 commas as delimiters. Create a new users.csv file with the following content:
Now open your membership.csv file and add the following line at the beginning:
Then modify the group on the second column (after the comma) and change it to the new group name you want to create.
例:Here we need to make sure we have the proper permission to create the group through Crowd. If you are using LDAP, make sure you have Read & Write permissions or permission to manage groups locally. Otherwise you'll need to create the group in the LDAP before importing this CSV on Crowd.
- Finally we got both files created as expected, so let's begin the importing process. First go under Users > Import users and select CSV importer.
- Then select the directory your users and group belong to. If it's an LDAP, you'll need to select No for passwords encrypted. Then specify the whole path of the users.csv and for the membership.csv files and click on Continue.
- Now we'll need to map the column according to the delimiter. On our case, for the users.csv file you can just choose any column as long as you don't repeat them. Then for the membership.csv choose according to the CSV header row, which is likely to be Username and then Group name. After mapping the columns click on Continue.
- This screen just shows the chosen options, validate it once again and click on Continue.
- The results of the import will show now. On my case I had 8 rows of membership and the group named confluence-users4 did not exist, so Crowd imported it.
And you're done! You can now go to your group and check the direct users! Just make sure you're checking the group from the directory you've imported the membership.
You might want to check crowd-atlassian.log file in case the memberships were not imported. If a message saying that "the user was not added to the group" appears, you might need to double check your permissions or if the user is from the same directory as your group.