INSERT INTO external_members (extentityid, groupid)

SELECT
	`external_entities`.`id`,
	`local_members`.`groupid` 
FROM
	`external_entities` `external_entities` 
		INNER JOIN `users` `users` 
		ON `external_entities`.`name` = `users`.`name` 
			INNER JOIN `local_members` `local_members` 
			ON `local_members`.`userid` = `users`.`id`

Result:
If a user exists in both the local table, and the external_entities table, this will dump all their local groups into the external_members table, which has the end result of giving the LDAP user all the groups the local user had. This has been vetted in a installation of Confluence 2.7 where a local user was created with the same login as one in LDAP. then an LDAP was integrated into this install. This SQL successfully granted this test user with the same permissions (confluence-users, confluence -admin).

Steps I took to produce this result: (be sure to backup the DB before doing this)
1) Shutdown Confluence
2) Run Query
3) Start Confluence
4) Login with an local admin user, and flush the caches.
5) Login with an LDAP user that was also a local user. This user should have all the same groups as the prior local user.

This fix does have a serious flaw. The pre-condition for this to work is the LDAP user must be in the external_entities table to begin with. If the LDAP user has never logged in, then they will not be in this table; this entry is created on first log in.

However, I have the same problem with atlassian-user.xml. Lost all local groups once LDAP is turned on with atlassian-user.

Hi Tim,
Is it possible for your to provide another script to eliminate the pre-condition?

I think I can grab users from users table and insert them into to external_entities table
insert into external_entities (id, name, type) values (1,'username', 'EXT');
however, I am not sure about external_entities.id column, how does it gets its value? A sequence?

And then I can run the script you provide above to migrate the groups?

I tried to eliminate the pre-condition to do the user first time login via SOAP API. Although the "first time" login was successful, no record was created for the user in EXTERNAL_ENTITIES table.

http://support.atlassian.com

Tim Wong
Atlassian Support- San Francisco

This is what I did...Make sure you test it out on a dev server and not production

Since I only wanted to use the LDAP authentication, not the groups, I figured out that this worked for me. I don't know if it is the "right" way...but it worked.
This is what I did:

1. To re-associate local confluence groups
a. Exported data from the dbo.users table into an excel file
b. Deleted all columns except 'name'
c. Added a new column named 'id' and 'type'
d. In the 'id' column, I started the number at 12681217 and
increased it by one (sequentially) on each row that had a
username.
e. In the 'type' column I inserted the text "EXT" in each row
f. Saved the excel file, and then ran the import data wizard in
MS SQL 2005. I imported the data to the dbo.external_entities
table.
g. Ran the following script from above:

INSERT INTO external_members (extentityid,
groupid)

SELECT
external_entities.id,
local_members.groupid
FROM
external_entities external_entities
INNER JOIN users users
ON external_entities.name =
users.name
INNER JOIN local_members
local_members
ON local_members.userid
= users.id

*Note, I only copied the user accounts to the excel file if it was an account that matched the network (ldap) user name. So, after doing these steps, only users that had wiki usernames that matched their LDAP username will be re-associated to their groups. The other accounts will have to be manually re-added to each group.

2. To eliminate the duplicate usernames I deleted all the users (except the local admin account) from the dbo.users table.

I hope that helps...

Your suggestion worked perfectly on both the dev and production hosts.

Manny

http://confluence.atlassian.com/display/DOC/Migrating+to+new+User+Management

Therefore, if migrating from os_user to LDAP, make sure to put in your ldap tag, then hibernate tag, then os_user tag in <confluence-home>/confluence/WEB-INF/classes/atlassian-user.xml. If you're already on hibernate user management, this issue pertains.

You can see if you're on os_user or hibernate user by looking at the os_user and users tables on the db, respectively.

1. Install Crowd
2. Setup Confluence with Crowd, and tell Crowd to import all the users from Confluence.
3. Map the LDAP directory (first) and the imported users directory (second) to Confluence.

Crowd will authenticate users against the LDAP directory, and combine memberships from both directories to present to Confluence.

http://confluence.atlassian.com/display/DOC/Migrate+to+LDAP+User+Management+From+OsUser

Watch this bug report for updates on migrating (and keeping group membership) from hibernate(atlassian user) => LDAP.