Migrating Crowd Directories

April 18, 2011 Leave a comment

When I originally started using Crowd, I set it up with an internal directory… A type of directory that is proprietary to the Crowd application. Ideally, I would have set it up with an AD/LDAP directory, but at the time I did not have one setup. Now, of course, I have one setup and want to migrate away from the old (proprietary) directory to the new LDAP directory. Confluence and JIRA are both using Crowd for authentication/SSO and of course migrating from one directory to another is not as easy as I had originally thought it would be.

Atlassian Support recommended that I use the Alias feature in Crowd.. The problem with that though, is that it only provides a synchronization of passwords. The users would still have to continue using their old username to login to Confluence and JIRA. I believe the reason Atlassian chose to do this is because there are many “behind-the-scenes” references to a username. This must be so that users can freely be added and removed from Confluence/JIRA without directly affecting the integrity of Confluence pages and JIRA issues. However, this provides a problem for me, because in my scenario I no longer can afford to manage users that have two separate usernames.

I ended up creating a set of SQL scripts to migrate my users from the old (proprietary) directory, to the new (LDAP) directory. It is a very manual process and not one that I take lightly. Ultimately, I have to migrate one user at a time and it involves notifying the user that they cannot use Confluence or JIRA, restarting Confluence and JIRA a number of times and updating a number of tables in the Confluence and JIRA databases. Here I will outline the process in which I have followed to accomplish this and will also provide the SQL scripts for download. I make no guarantee that this migration process will work for anyone else, but thought I would at least share it just in case.

Notes

  • Even though you update the JIRA tables, many fields are stored in  memory in JIRA’s index. Even restarting JIRA doesn’t always do the trick. For example, I had to manually perform a re-index in order to get assignee’s to update.
  • In JIRA, I have not been able to determine where the favorite filters are stored… So, the scripts do not update the favorite filters. That does not mean that the filters don’t exist, I just mean that they aren’t shown on the favorite filters.
  • These scripts were built for JIRA 4.3.2 and Confluence 3.5.2.
  • THIS IS NOT A GUARANTEED PROCESS. If you screw up your Confluence and JIRA instances, it’s not my fault.

Process

  1. Backup the database! Whether you are doing a migration for Confluence, or JIRA or both, backup the databases that you are working with.
  2. Install the procedure that I created for the appropriate application(s).
  3. Work on one user at a time.
  4. Remove all groups from the internal directory for the given user.
  5. Disable the user in the internal directory so that the use doesn’t count against the license count. You should only have to be worried about this if you have a limited number of users on your license. If you have an unlimited user license, you are in a better position then I.
  6. Activate the user in the LDAP directory and perform a Crowd authentication test for the application in question.
  7. Run the following MySQL command on the application’s database: 
    CALL MigrateUser('myOldUsername', 'mynewUsername', 1234);
  8. After running the MySQL command, review the returned results to make sure there are no remaining entries that did not get updated
  9. Re-synchronize the Crowd User Directory in the Confluence or JIRA application.
  10. Flush cache or re-index.
    1. When running the script on JIRA, re-index the application (or restart it).
    2. When running the script in Confluence, flush the cache (or restart it).

JIRA Script

-- JIRA
DELIMITER $$

DROP PROCEDURE IF EXISTS `MigrateUser`$$
CREATE PROCEDURE `MigrateUser` (
    inOldUsername VARCHAR(255),
    inNewUsername VARCHAR(255),
    inDirectoryId INT
)
BEGIN
    -- Update the crowd user entry
    update cwd_user set
      user_name = inNewUsername,
      lower_user_name = inNewUsername
    where
      user_name = inOldUsername
      and directory_id = inDirectoryId;

    -- Remove crowd's old membership entries for groups (assumes groups have changed)
    delete from cwd_membership where child_name = inOldUsername and directory_id = inDirectoryId;

    -- Update the project roles for the new user
    update projectroleactor set ROLETYPEPARAMETER = inNewUsername where ROLETYPEPARAMETER = inOldUsername;

    -- Update jira issues to reflect new assigne and reporter
    update jiraissue set assignee = inNewUsername where assignee = inOldUsername;
    update jiraissue set reporter = inNewUsername where reporter = inOldUsername;

    -- Miscellaneous updates
    update columnlayout set username = inNewUsername where username = inOldUsername;
    update favouriteassociations set username = inNewUsername where USERNAME = inOldUsername;
    update filtersubscription set USERNAME = inNewUsername where USERNAME = inOldUsername;
    update portalpage set USERNAME = inNewUsername where USERNAME = inOldUsername;
    update searchrequest set authorname = inNewUsername, username = inNewUsername where username = inOldUsername;
    update userhistoryitem set USERNAME = inNewUsername where USERNAME = inOldUsername;
    update component set LEAD = inNewUsername where lead = inOldUsername;
    update userassociation set SOURCE_NAME = inNewUsername where SOURCE_NAME = inOldUsername;
    update worklog set AUTHOR = inNewUsername where AUTHOR = inOldUsername;
    update worklog set UPDATEAUTHOR = inNewUsername where UPDATEAUTHOR = inOldUsername;
    update project set LEAD = inNewUsername where LEAD = inOldUsername;

    select * from columnlayout where username = inOldUsername;
    select * from favouriteassociations where username = inOldUsername;
    select * from filtersubscription where username = inOldUsername;
    select * from portalpage where username = inOldUsername;
    select * from searchrequest where username = inOldUsername;
    select * from userhistoryitem where username = inOldUsername;
    select * from userbase where username = inOldUsername;
    select * from columnlayout where username = inOldUsername;
    select * from projectroleactor where ROLETYPEPARAMETER = inOldUsername;
    select * from cwd_user where user_name = inOldUsername and directory_id = inDirectoryId;
END

Confluence Script

-- CONFLUENCE
DELIMITER $$

DROP PROCEDURE IF EXISTS `MigrateUser`$$
CREATE PROCEDURE `MigrateUser` (
    inOldUsername VARCHAR(255),
    inNewUsername VARCHAR(255),
    inDirectoryId INT
)
BEGIN
    update ATTACHMENTS a set a.creator = inNewUsername where a.creator = inOldUsername;
    update ATTACHMENTS a set a.lastmodifier = inNewUsername where a.lastmodifier = inOldUsername;

    update CONTENT a set a.creator = inNewUsername where a.creator = inOldUsername;
    update CONTENT a set a.lastmodifier = inNewUsername where a.lastmodifier = inOldUsername;
    update CONTENT a set a.username = inNewUsername where a.username = inOldUsername;

    update CONTENTLOCK a set a.creator = inNewUsername where a.creator = inOldUsername;
    update CONTENTLOCK a set a.lastmodifier = inNewUsername where a.lastmodifier = inOldUsername;

    update CONTENT_LABEL a set a.owner = inNewUsername where a.owner = inOldUsername;

    update CONTENT_PERM a set a.creator = inNewUsername where a.creator = inOldUsername;
    update CONTENT_PERM a set a.lastmodifier = inNewUsername where a.lastmodifier = inOldUsername;
    update CONTENT_PERM a set a.username = inNewUsername where a.username = inOldUsername;

    update CWD_USER a set a.lower_user_name = LOWER(inNewUsername) where a.lower_user_name = LOWER(inOldUsername) and a.directory_id = inDirectoryId;
    update CWD_USER a set a.user_name = inNewUsername where a.user_name = inOldUsername and a.directory_id = inDirectoryId;

    update EXTRNLNKS a set a.creator = inNewUsername where a.creator = inOldUsername;
    update EXTRNLNKS a set a.lastmodifier = inNewUsername where a.lastmodifier = inOldUsername;

    update FOLLOW_CONNECTIONS a set a.followee = inNewUsername where a.followee = inOldUsername;
    update FOLLOW_CONNECTIONS a set a.follower = inNewUsername where a.follower = inOldUsername;

    update LABEL a set a.owner = inNewUsername where a.owner = inOldUsername;

    update LINKS a set a.creator = inNewUsername where a.creator = inOldUsername;
    update LINKS a set a.lastmodifier = inNewUsername where a.lastmodifier = inOldUsername;

    update NOTIFICATIONS a set a.creator = inNewUsername where a.creator = inOldUsername;
    update NOTIFICATIONS a set a.lastmodifier = inNewUsername where a.lastmodifier = inOldUsername;
    update NOTIFICATIONS a set a.username = inNewUsername where a.username = inOldUsername;

    update PAGETEMPLATES a set a.creator = inNewUsername where a.creator = inOldUsername;
    update PAGETEMPLATES a set a.lastmodifier = inNewUsername where a.lastmodifier = inOldUsername;

    update REMEMBERMETOKEN a set a.username = inNewUsername where a.username = inOldUsername;

    update SPACEGROUPS a set a.creator = inNewUsername where a.creator = inOldUsername;
    update SPACEGROUPS a set a.lastmodifier = inNewUsername where a.lastmodifier = inOldUsername;

    update SPACEPERMISSIONS a set a.creator = inNewUsername where a.creator = inOldUsername;
    update SPACEPERMISSIONS a set a.lastmodifier = inNewUsername where a.lastmodifier = inOldUsername;
    update SPACEPERMISSIONS a set a.permusername = inNewUsername where a.permusername = inOldUsername;

    update SPACES a set a.creator = inNewUsername where a.creator = inOldUsername;
    update SPACES a set a.lastmodifier = inNewUsername where a.lastmodifier = inOldUsername;

    update TRACKBACKLINKS a set a.creator = inNewUsername where a.creator = inOldUsername;
    update TRACKBACKLINKS a set a.lastmodifier = inNewUsername where a.lastmodifier = inOldUsername;

    -- Reassign user preferences
    update OS_PROPERTYENTRY a
        set a.entity_name = concat('CWD_', inNewUsername)
        where a.entity_name = concat('CWD_', inOldUsername);

    -- Change space name
    update SPACES a
        set a.spacekey = concat('~', inNewUsername)
        where a.spacekey = concat('~', inOldUsername);

    update BANDANA a
        set a.bandanacontext = concat('~', inNewUsername)
        where a.bandanacontext = concat('~', inOldUsername);

    select * from cwd_user where user_name = inOldUsername;
    select * from NOTIFICATIONS where username = inOldUsername;
    select * from SPACEPERMISSIONS where PERMUSERNAME = inOldUsername;
    select * from CONTENT where username = inOldUsername;
END
Posted in: IT

Leave a Reply

Your email address will not be published.

Humanity Verification *Captcha loading...