Sakai sakai_user_id_map table query

[Jaco Gillman]

Hi Sakai Community

We are investigating a Sakai issue and would like to know if someone else has been through a similar scenario before and how they went about fixing the issue.

The table sakai_user_id_map only has two columns {USER_ID, EID}.

• USER_ID – PK, used in other tables to map the user
  
• EID – unique value, username used when user logs in
  

For  example, let’s say the sakai_user_id_map table has the following record entry:

USER_ID              EID

abcd                      jacog

Then somehow the USER_ID got changed to:

USER_ID              EID

wxyz                      jacog

This user ‘jacog’ will then log in and won’t see any previously submitted Assignments, Test and Quizzes etc. that has the mapping it had (USER_ID = abcd), which also was used in many other Sakai table’s & columns. Then this user might perform Assignment submissions, Forum posts, etc against the USER_ID = wxyz user mapping. How does one go about fixing/roll back this issue? According to my knowledge, it is impossible for the USER_ID to change and Sakai won’t allow that to happen, but strange things do happenJ. 

I briefly scanned some tables and these are some of the queries I believe will used to determine everywhere changes must happen:

SELECT * FROM announcement_channel where channel_id like '%<USER_ID>%';

SELECT * FROM calendar_calendar where calendar_id like '%<USER_ID>%';

SELECT * FROM SAM_ASSESSMENTGRADING_T where AGENTID like '%<USER_ID>%'; 

Changing all column values in all tables for user ‘jacog’ where USER_ID = wxyz to USER_ID = abcd? I have started to look at some of the Sakai tables and realized that so many tables and columns make use of the USER_ID and wondered if someone else has a similar daunting task. Otherwise we’ll just need to go look at each table to see where the change must be applied.

 

Kind Regards,

Jaco Gillman

Lead Java Developer

Mobile: +27 83 596 2375

Skype:  gillmanjc

---

See OpenCollab email disclaimer at http://www.opencollab.co.za/email-disclaimer

[Sam Ottenhoff]

Changing all column values in all tables for user ‘jacog’ where USER_ID = wxyz to USER_ID = abcd? I have started to look at some of the Sakai tables and realized that so many tables and columns make use of the USER_ID and wondered if someone else has a similar daunting task. Otherwise we’ll just need to go look at each table to see where the change must be applied.

Why not just reverse the change in SAKAI_USER_ID_MAP? Changing the USER_ID in 20+ tables will be difficult. You could take a database dump file and then grep for the files that have the string you are looking for before writing individual queries for each of those tables. 

[Hendrik Steller]

On Wednesday 30 November 2016 16:32:43 Jaco Gillman wrote:

> Changing all column values in all tables for user ‘*jacog*’ where *USER_ID
> *= *wxyz *to *USER_ID *= *abcd*?

Why don't you just change the mapping in USER_ID_MAP back(?) from wxyz to
abcd?

As you have noticed, the USER_ID from SAKAI_USER is the one that usually(*)
counts in other places. Which makes sense because you don't have to use an EID
or the USER_ID_MAP at all if you only use dedicated sakai users/accounts which
log in directly into sakai.

> I have started to look at some of the
> Sakai tables and realized that so many tables and columns make use of the

> *USER_ID *and wondered if someone else has a similar daunting task.

Yes.
Back when some Sakai tools couldn't cope with special characters like dots or
@ in the internal USER_ID, we had someone write a tool and pseudo API to
change internal USER_IDs (pseudo API so our tools with "id-change-support"
could be notified upon ID changes and update their internal tables).

Basically, that tool takes a list of tables and table columns and changes them
either by swapping the value outright or based on a regex you could supply
through a GUI. (**); regex became necessary instead of "just change the value"
because of stuff like resource URLs or the calendar API which uses XML
containing the ID in attributes. (***)
Then you could press a button and change back-and-forth between the old and
the new value.

The tests we did with selected USER_IDs were successful, but we never used it
in production with hundreds of affected users after the character issues with
the internal IDs got fixed.

Regards
Hendrik



(*)
I think sometimes sakai is "helpful" and if it can't find an E_ID it falls
back to the USER_ID or vice versa. Which can have funny effects if you have an
user with USER_ID "foo" and a different user with a different USER_ID, but the
E_ID "foo". Then you can get a workspace where the URLs in the menu on the
left belong to one user while the URLs in the drawer at the top belong to the
other (one uses the EID to build URLs, the other uses the USER_ID)

(***)
..which we noticed first when, at the very beginning, someone wrote a thingee
to anonymize a copy of the production DB for student developers, but had
overlooked the XML in the calendar data.


(**)
If you're interested where that developer found the USER_ID, here's a config
file of that tool from one of our test servers back when (for sakai 10.0 or
10.5)

CM_MEMBERSHIP_T = USER_ID
CM_MEMBERSHIP_T = MEMBER_ID
CM_ENROLLMENT_T = USER_ID,CREATED_BY,LAST_MODIFIED_BY
CMN_TYPE_T = UUID
GB_PERMISSION_T = USER_ID
MFR_AREA_T = UUID,CREATED_BY,MODIFIED_BY
MFR_ATTACHMENT_T = UUID,CREATED_BY,MODIFIED_BY
MFR_EMAIL_NOTIFICATION_T = USER_ID
MFR_LABEL_T = UUID,CREATED_BY,MODIFIED_BY
MFR_MEMBERSHIP_ITEM_T = UUID,CREATED_BY,MODIFIED_BY
MFR_MESSAGE_T = UUID,CREATED_BY,MODIFIED_BY,AUTHOR
MFR_MOVE_HISTORY_T = UUID,CREATED_BY,MODIFIED_BY
MFR_OPEN_FORUM_T = UUID,CREATED_BY,MODIFIED_BY
MFR_PERMISSION_LEVEL_T = UUID,CREATED_BY,MODIFIED_BY
MFR_PRIVATE_FORUM_T = UUID,OWNER
MFR_PVT_MSG_USR_T = USER_ID
MFR_RANK_T = UUID
MFR_RANK_T = CREATED_BY
MFR_RANK_T = MODIFIED_BY
MFR_RANK_T = ASSIGNTO
MFR_RANK_T = CREATED_BY
MFR_RANK_T = MODIFIED_BY
MFR_SYNOPTIC_ITEM = USER_ID
MFR_TOPIC_T = UUID,CREATED_BY,MODIFIED_BY,USER_ID
POLL_VOTE = USER_ID,VOTE_SUBMISSION_ID
SAKAI_PERSON_T = UUID
SAKAI_PERSON_T = CREATED_BY
SAKAI_PERSON_T = LAST_MODIFIED_BY
SAKAI_PERSON_T = UID_C
SAKAI_PERSON_T = AGENT_UUID
SAKAI_REALM_RL_GR = USER_ID
SAKAI_USER = USER_ID,CREATEDBY,MODIFIEDBY
SAKAI_USER_ID_MAP = USER_ID
SAKAI_USER_PROPERTY = USER_ID
SAKAI_SITE = CREATEDBY, MODIFIEDBY, #SITE_ID#~(\\S+)
SAKAI_REALM = #REALM_ID#/site/~(\\S+)
SAKAI_SITE_TOOL = #SITE_ID#~(\\S+)
SAKAI_SITE_TOOL_PROPERTY = #SITE_ID#~(\\S+)
SAKAI_SITE_PAGE = #SITE_ID#~(\\S+)
SAKAI_SITE_PAGE_PROPERTY = #SITE_ID#~(\\S+)
SSQ_USER_ANSWER = USER_ID
SST_USERSTATS = USER_ID
SST_EVENTS = USER_ID
SST_PRESENCES = USER_ID
SST_RESOURCES = USER_ID
VALIDATIONACCOUNT_ITEM = USER_ID
dw_assignment_status = USER_ID,ADVISOR,INSTRUCTOR
dw_site_users = user_id
dw_users = user_id
lesson_builder_comments = author,UUID
sakai_event_delay = USER_ID
sakai_site_user = USER_ID
sakvv_user = USER_ID,CREATED_BY
scheduler_trigger_events = uuid
user_audits_log = user_id
user_audits_log = action_user_id
user_audits_log = action_user_id
ANNOUNCEMENT_MESSAGE = OWNER, ~XML~/message/header~from
CHAT2_MESSAGE = OWNER
CM_ACADEMIC_SESSION_T = CREATED_BY,LAST_MODIFIED_BY
CM_CROSS_LISTING_T = CREATED_BY,LAST_MODIFIED_BY
CM_ENROLLMENT_SET_T = CREATED_BY,LAST_MODIFIED_BY
CM_MEMBER_CONTAINER_T = CREATED_BY,LAST_MODIFIED_BY
EMAIL_TEMPLATE_ITEM = OWNER
MFR_RANKIMAGE_T = CREATED_BY,MODIFIED_BY
SST_REPORTS = CREATED_BY,MODIFIED_BY
dw_matrix = owner
dw_metaobj_form_def = owner
dw_resource = CREATED_BY,MODIFIED_BY
dw_resource_collection = CREATED_BY,MODIFIED_BY
dw_wizard_style = owner
lesson_builder_pages = owner
lesson_builder_student_pages = owner
mailarchive_message = OWNER
metaobj_form_def = owner
osp_matrix = owner
osp_wizard_page = owner
rwikihistory = userid
rwikihistory = owner
rwikihistory = userid
rwikiobject = userid
rwikiobject = owner
rwikiobject = userid
signup_attachments = created_by,last_modified_by
HIERARCHY_PERMS = userId
HIERARCHY_PERMS = userId
SAKAI_PRIVACY_RECORD = userId
SAKAI_PRIVACY_RECORD = userId
SAKAI_SYLLABUS_ITEM = userId
SAKAI_PREFERENCES=~XML~/preferences~id
dw_session = userId
dw_session = userId
lesson_builder_log = userId
lesson_builder_q_responses = userId
lesson_builder_q_responses = userId
rwikipagemessage = userid
rwikipagemessage = userid
rwikipagepresence = userid
rwikipagepresence = userid
rwikipagetrigger = userid
rwikipagetrigger = userid
rwikipreference = userid
kvv_examsignup_membership = USER_ID
GB_GRADE_RECORD_T = STUDENT_ID,GRADER_ID
GB_COMMENT_T = STUDENT_ID,GRADER_ID
GB_GRADING_EVENT_T = STUDENT_ID,GRADER_ID
PROFILE_FRIENDS_T = FRIEND_UUID
PROFILE_FRIENDS_T = USER_UUID
PROFILE_FRIENDS_T = FRIEND_UUID
PROFILE_COMPANY_PROFILES_T = USER_UUID
PROFILE_EXTERNAL_INTEGRATION_T = USER_UUID
PROFILE_GALLERY_IMAGES_T = USER_UUID
PROFILE_IMAGES_EXTERNAL_T = USER_UUID
PROFILE_IMAGES_OFFICIAL_T = USER_UUID
PROFILE_IMAGES_T = USER_UUID
PROFILE_KUDOS_T = USER_UUID
PROFILE_MESSAGES_T = FROM_UUID
PROFILE_MESSAGE_PARTICIPANTS_T = PARTICIPANT_UUID
PROFILE_PRIVACY_T = USER_UUID
PROFILE_SOCIAL_INFO_T = USER_UUID
PROFILE_STATUS_T = USER_UUID
PROFILE_WALL_ITEMS_T = USER_UUID
PROFILE_WALL_ITEMS_T = CREATOR_UUID
PROFILE_WALL_ITEM_COMMENTS_T = CREATOR_UUID
ASN_NOTE_ITEM_T = CREATOR_ID
ASN_PEER_ASSESSMENT_ITEM_T = ASSESSOR_USER_ID
ASSIGNMENT_SUBMISSION = SUBMITTER_ID, ~XML~/submission~submitterid,
~XML~/submission~submitter0,#CONTEXT#~(\\S+)
ASSIGNMENT_CONTENT =
~XML~/assignment/properties/property[@name="CHEF:creator"]~value~base64,
~XML~/assignment/properties/property[@name="CHEF:modifiedby"]~value~base64
ASSIGNMENT_ASSIGNMENT =
~XML~/assignment/properties/property[@name="CHEF:creator"]~value~base64,
~XML~/assignment/properties/property[@name="CHEF:modifiedby"]~value~base64
CALENDAR_CALENDAR =
~XML~/calendar/properties/property[@name="CHEF:modifiedby"]~value~base64
CALENDAR_EVENT =
~XML~/event/properties/property[@name="CHEF:modifiedby"]~value~base64,
~XML~/event/properties/property[@name="CHEF:creator"]~value~base64
HIERARCHY_NODE_META = ownerId
SAKAI_POSTEM_STUDENT_GRADES = student_id
SAKAI_REALM_PROVIDER = PROVIDER_ID
SAM_ASSESSMENTGRADING_T = AGENTID
SAM_AUTHZDATA_T = AGENTID
SAM_FAVORITECOLCHOICES_T = OWNERID
SAM_GRADINGSUMMARY_T = AGENTID
SAM_ITEMGRADING_T = AGENTID
SAM_QUESTIONPOOL_T = OWNERID
SAM_STUDENTGRADINGSUMMARY_T = AGENTID
dw_presentation_comment = creator_id
dw_presentation_layout = owner_id
dw_presentation_log = viewer_id
dw_scaffolding = ownerId
dw_wizard = owner_id
dw_wizard_completed = owner_id
lesson_builder_items = sakaiId
osp_authz_simple = agent_id
osp_completed_wizard = owner_id
osp_list_config = owner_id
osp_presentation_comment = creator_id
osp_presentation_layout = owner_id
osp_presentation_log = viewer_id
osp_presentation_template = owner_id
osp_style = owner_id
osp_wizard = owner_id
signup_meetings = creator_user_id
signup_ts_attendees = attendee_user_id
CONTENT_RESOURCE =
#CONTEXT#~(\\S+),#RESOURCE_ID#/user/(\\S+)/.*,#IN_COLLECTION#/user/(\\S+)/.*
CONTENT_COLLECTION =
#COLLECTION_ID#/user/(\\S+)/.*,#IN_COLLECTION#/user/(\\S+)/.*

[Jaco Gillman]

Hi Hendrik, Sam

Thanks for your responses. I agree completely with what you are saying... Both of you suggested I just change the mapping in USER_ID_MAP back from wxyz to abcd... That will be step 1. What I then need to do is to find everywhere wxyz was used and replace that also with abcd. So if the user 'jacog' with mapping wxyz submitted an Assignment, and I change the mapping in USER_ID_MAP back to abcd, that Assignment submission wont show anymore. 

Thanks again for your comprehensive reply Hendrik!

Kind Regards,

Jaco Gillman

Lead Java Developer

Mobile: +27 83 596 2375

Skype:  gillmanjc