Cleanup OpenID Connect Tokens not working

[hdagheda]

Cleanup OpenID Connect Tokens not deleting object from local_o365_objects table. Even deleting token doesn't allow user to login again.

[weilai-irl]

Hi @hdagheda,

The purpose of the "Cleanup OpenID Connect Tokens" feature is not to delete records from the mdl_local_o365_objects table. Rather, it looks for user OIDC tokens stored in the mdl_auth_oidc_token table that doesn't have a valid userid, and delete such tokens. It also shows records in mdl_auth_oidc_token table that contain tokens between potentially mismatched users, which helps site admins to troubleshoot SSO error.

If you have users who can't login, please look for the records of the user in the following tables:

• mdl_local_o365_objects
• mdl_auth_oidc_token
• mdl_local_o365_connections (in case the user is manually connected)

I hope this helps.

Regards,
Lai

[AntonT76]

Dear @weilai-irl

we have a similar problem like @hdagheda :
In some cases we have to change existing usernames (eg username changes from [email protected] to [email protected]). It is the same user, but due to some organisational processes the user gets a new username. The old username will be deactivated/deleted.

In most of the cases the user has already done some activities in Moodle (eg upload homework, quiz, etc.) with his old username. These activities would be lost if we create a new user (eg [email protected]) and deactivate/delete the old user (eg [email protected]) in Moodle.

So what we do is to change the username directly in Moodle. It works fine, but it ends up in a Token error. The user could not login. To fix the token error, we have to edit the database (table: auth_oidc_token) and delete the entry of this user. Unfortunately, it is not easy because we do not always have access to the databases.

It would be nice if there is a possibility directly in the Maintenance Tool-Set of Office 365-Plugin Set - maybe in addition to the feature "Cleanup OpenID Connection Tokens" - where we can delete invalid token entries in the database "auth_oidc_token".

thanks,
best regards
Anton

[AntonT76]

Based on some tests I would like to make an addition. It might be possible to avoid a token error after changing the existing username in Moodle:

1. login in Moodle as Moodle Admin
2. go to o365-plugin-settings - Advanced - Connections - Manage User Connections
3. disconnect the user
4. enter a pseudo-password (user will be changed from OIDC to manual login authentication, a stored token will be deleted from the table prefix_auth_oidc_token)
5. go to user settings, edit the user and change username - save entries
6. edit user settings again - change authentication method from "manual" to "OpenID Connect"
7. Finished.

Now, the user can login with the new login name. A new token will be stored in the table prefix_auth_oidc_token

I did some tests with one user, I hope, this method will work for all other users.

br, Anton

[kagmole]

Had the same issue than @AntonT76 on our Moodle instance.
Alas, we had 1'000+ wrong entries, so doing the described manual steps would have been a little tedious. 😁

Basically our users had an old Windows username prefix-theuser while we needed the new Windows style theuser@domain.
For our cases, the table prefix_auth_oidc_token (where our prefix = the default mdl) contained the correct value in oidcusername.

So what we did was basically check our cases with:

SELECT * 
FROM "mdl_auth_oidc_token"
WHERE
    LOWER("username") <> LOWER("oidcusername")
    AND LOWER("username") LIKE 'prefix-%'
    AND LOWER("oidcusername") LIKE '%@domain'
ORDER BY "username"

A little backup:

SELECT * INTO "backup_mdl_auth_oidc_token_20250214" FROM "mdl_auth_oidc_token"

And the fix:

UPDATE "mdl_auth_oidc_token" SET "username" = LOWER("oidcusername")
WHERE
    LOWER("username") <> LOWER("oidcusername")
    AND LOWER("username") LIKE 'prefix-%'
    AND LOWER("oidcusername") LIKE '%@domain'

After that, no more issue to reconnect.
In case it helps someone with the same problem.