Yair Zaslavsky has uploaded a new change for review. Change subject: aaa: Changing ids of users and groups ......................................................................
aaa: Changing ids of users and groups This is an upgrade script for changing IDs of groups and users to match uuid representation of external_id. external_id holds the id of users and groups from ldap providers. We would like to revert the uuid generation of ids of groups and users at engine, and change back to 3.3 behavior. This is the first step to towards that. Change-Id: I6a502e9972e76ebe1a2c809ace1e47f6db5c9fdb Topic: AAA Signed-off-by: Yair Zaslavsky <[email protected]> --- A packaging/dbscripts/upgrade/03_05_0150_change_group_ids.sql A packaging/dbscripts/upgrade/03_05_0160_update_user_ids.sql 2 files changed, 76 insertions(+), 0 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/66/25966/1 diff --git a/packaging/dbscripts/upgrade/03_05_0150_change_group_ids.sql b/packaging/dbscripts/upgrade/03_05_0150_change_group_ids.sql new file mode 100644 index 0000000..8d3aa96 --- /dev/null +++ b/packaging/dbscripts/upgrade/03_05_0150_change_group_ids.sql @@ -0,0 +1,52 @@ +--groups.external_id holds a hex representation of the id of groups at ldap directories. +-- This script sets the guid representation at ad_groups.id, and modifies all relevant references, +--using the following steps: + +--1. Adding temp column for mapping from old uuid to new uuid +SELECT fn_db_add_column('ad_groups', 'temp_id', 'uuid'); +--Filling the new column with guid repreentation of external_id +UPDATE ad_groups SET temp_id = CAST(substring(external_id::text FROM 3 FOR 8) || + substring(external_id::text FROM 11 FOR 4) || + substring(external_id::text FROM 15 FOR 4) || + substring(external_id::text FROM 19 FOR 4) || + substring(external_id::text FROM 23 FOR 12) AS uuid); + +--2. Changing relevant group_id appearances in other tables +ALTER TABLE tags_user_group_map DROP constraint "tags_user_map_user_group"; +UPDATE tags_user_group_map m set group_id = ( + SELECT temp_id FROM ad_groups WHERE id = m.group_id); +UPDATE permissions p SET ad_element_id = ( + SELECT temp_id FROM ad_groups WHERE id = p.ad_element_id); +--3. Fixing group_ids at users +CREATE temp TABLE tmp_users_groups AS + SELECT fnsplitteruuid(group_ids) as group_id, user_id from users; +UPDATE tmp_users_groups t SET group_id = ( + SELECT temp_id FROM ad_groups WHERE id = t.group_id +); +CREATE temp TABLE tmp_users_group_ids AS + SELECT user_id, array_to_string(array_agg(group_id), ',') group_ids FROM tmp_users_groups GROUP BY user_id; +UPDATE users u SET group_ids = ( + SELECT group_ids FROM tmp_users_group_ids WHERE user_id = u.user_id +); +UPDATE ad_groups SET id = temp_id; +--4. Cleanup +DROP TABLE tmp_users_group_ids; +DROP TABLE tmp_users_groups; +ALTER TABLE tags_user_group_map ADD CONSTRAINT tags_user_map_user_group FOREIGN KEY (group_id) REFERENCES ad_groups(id); +SELECT fn_db_drop_coumn('ad_groups','temp_id'); + + + + + + + + + + + + + + + + diff --git a/packaging/dbscripts/upgrade/03_05_0160_update_user_ids.sql b/packaging/dbscripts/upgrade/03_05_0160_update_user_ids.sql new file mode 100644 index 0000000..a1a9de8 --- /dev/null +++ b/packaging/dbscripts/upgrade/03_05_0160_update_user_ids.sql @@ -0,0 +1,24 @@ +--users.external_id holds a hex representation of the id of users at ldap directories. +-- This script sets the guid representation at users.id, and modifies all relevant references, +--using the following steps: + +--1. Adding temp column for mapping from old uuid to new uuid +SELECT fn_db_add_column('users', 'temp_id', 'uuid'); +--Filling the new column with guid repreentation of external_id +UPDATE users SET temp_id = CAST(substring(external_id::text FROM 3 FOR 8) || + substring(external_id::text FROM 11 FOR 4) || + substring(external_id::text FROM 15 FOR 4) || + substring(external_id::text FROM 19 FOR 4) || + substring(external_id::text FROM 23 FOR 12) AS uuid); + +--2. Changing relevant group_id appearances in other tables +ALTER TABLE tags_user_map DROP constraint "tags_user_map_user"; +UPDATE tags_user_map m set user_id = ( + SELECT temp_id FROM users WHERE user_id = m.user_id); +UPDATE permissions p SET ad_element_id = ( + SELECT temp_id FROM users WHERE user_id = p.ad_element_id); +UPDATE users SET user_id = temp_id; +--3. Cleanup +ALTER TABLE tags_user_map ADD CONSTRAINT tags_user_map_user FOREIGN KEY (user_id) REFERENCES users(user_id); +SELECT fn_db_drop_column('users','temp_id'); + -- To view, visit http://gerrit.ovirt.org/25966 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I6a502e9972e76ebe1a2c809ace1e47f6db5c9fdb Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Yair Zaslavsky <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
