Instantly share code, notes, and snippets.

Embed
What would you like to do?
This query will create LDAP mappings for all accounts that don't have them. Useful for migrating from Phacility to self-hosted using LDAP as the only auth method.
USE phabricator_user;
-- create LDAP mappings for unmapped users
-- this query uses a subselect due to the way MySQL reacts to UUID() being
-- wrapped in REPLACE() (returns the same UUID for all rows)
-- WARNING: make sure user.userName maps to LDAP usernames, otherwise
-- user_externalaccount.accountID needs to be modified for those users
-- where it doesn't. alternatively, use Phabricator's change username
-- utility in People app to change those users' usernames before running
INSERT INTO user_externalaccount
(phid, userPHID, accountType, accountDomain, accountSecret, accountID, dateCreated, dateModified, emailVerified, properties)
SELECT
CONCAT("PHID-XUSR-", LEFT(REPLACE(tmp.phid, '-', ''), 20)) as phid,
tmp.userPHID,
tmp.accountType,
tmp.accountDomain,
LEFT(REPLACE(tmp.accountSecret, '-', ''), 32) as accountSecret,
tmp.accountID,
0 as dateCreated,
0 as dateModified,
0 as emailVerified,
"" as properties
FROM (
SELECT
UUID() as phid,
u.phid as userPHID,
'ldap' as accountType,
'self' as accountDomain,
UUID() as accountSecret,
u.userName as accountID
FROM user u
LEFT JOIN user_externalaccount x
ON u.phid = x.userPHID AND x.accountType = 'ldap'
WHERE x.phid IS NULL
) as tmp;
@allixsenos

This comment has been minimized.

Copy link
Owner Author

allixsenos commented May 12, 2016

based on comments from Evan Priestley at Phabricator/Phacility https://secure.phabricator.com/T4279#47285

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment