`OAuthUtil.removeAuthzGrantCacheForUser` performs O(N) unbounded database operations per login when user has many active `openid`-scoped tokens

[vfraga]

Description

When an adaptive authentication script updates any user claim during login, the IdentityOathEventListener unconditionally triggers OAuthUtil.removeAuthzGrantCacheForUser, which queries IDN_OAUTH2_ACCESS_TOKEN for all active tokens with the openid scope belonging to the authenticating user, then iterates over the entire result set to evict each token's cached entry from IDN_AUTH_SESSION_STORE individually. There is no upper bound on the result set and no batching of the eviction operations.

In environments where users accumulate a large number of active tokens (due to disabled refresh token renewal, long-lived refresh tokens, multiple OIDC applications, or tokens with infinite validity periods), this O(N) behavior causes the authentication thread to block for tens of seconds — potentially exceeding gateway or load balancer timeouts and effectively preventing the user from logging in.

The issue reproduces on every subsequent login because the eviction targets the cache table (IDN_AUTH_SESSION_STORE), not the token table (IDN_OAUTH2_ACCESS_TOKEN). The token count is unaffected by the eviction, so each login rediscovers the same full set of tokens and repeats the same work.

Root Cause:

The performance issue has three contributing factors:

1. Unbounded token retrieval. OAuthUtil.removeAuthzGrantCacheForUser calls getAccessTokensByUserForOpenidScope(authenticatedUser, true), which returns all active tokens with the openid scope — including expired access tokens whose refresh tokens are still valid. The true flag and the absence of a LIMIT are by design (all tokens with potentially cached claims must be evicted to guarantee correctness of subsequent ID tokens and userinfo responses), but there is no safeguard against pathologically large result sets.

2. Per-token eviction loop with individual DB operations. OAuthUtil.clearAuthzCodeGrantCachesForTokens iterates over every token and calls AuthorizationGrantCache.clearCacheEntryByTokenId individually, which performs an in-memory javax.cache.Cache.remove() and then a session store eviction via SessionDataStore.clearSessionData. The session store eviction inserts a DELETE marker row into IDN_AUTH_SESSION_STORE — one INSERT per token, each requiring a connection acquire/execute/commit/release cycle (or async queue submission).

3. Unconditional trigger on all claim updates. IdentityOathEventListener.doPreSetUserClaimValues fires the full cache eviction for every claim update, regardless of whether the claim being updated is an OIDC claim that could be cached in AuthorizationGrantCache. Updating non-OIDC claims (e.g., session attributes, custom flags, internal markers) triggers the same expensive eviction unnecessarily.

Secondary Issue — DELETE Marker Accumulation:

SessionDataStore.removeSessionData() does not execute an SQL DELETE. It inserts a DELETE marker row into IDN_AUTH_SESSION_STORE. The debug log message "Removed SessionContextData from DB" is misleading — it prints after the INSERT. Physical removal is deferred to the CLEANUP_SESSION_DATA stored procedure.

Because the table's primary key is (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION) and TIME_CREATED is a nanosecond-precision BIGINT, each INSERT of a DELETE marker is guaranteed to have a unique key. With checkExistingEntryForDeleteOperationInsert defaulting to false, no deduplication occurs. This means each login adds +N DELETE marker rows to IDN_AUTH_SESSION_STORE (where N = the user's active openid-scoped token count), causing significant table growth between CLEANUP_SESSION_DATA runs.

Detailed Call Chain

JsGraalClaims.putMember                         ← Adaptive auth script sets a claim
  └─ JsClaims.setMemberObject
      └─ JsClaims.setLocalClaim
          └─ JsClaims.setLocalUserClaim
              └─ AbstractUserStoreManager.setUserClaimValuesWithID
                  └─ IdentityUserNameResolverListener.doPreSetUserClaimValuesWithID
                      └─ IdentityOathEventListener.doPreSetUserClaimValues
                          └─ OAuthUtil.removeAuthzGrantCacheForUser
                              ├─ [DB SELECTs] getAccessTokensByUserForOpenidScope (×2)
                              ├─ [DB SELECTs] getAuthorizationCodesByUserForOpenidScope (×2)
                              └─ clearAuthzCodeGrantCachesForTokens     ★ Iterates N tokens
                                  └─ (per token) AuthorizationGrantCache.clearCacheEntryByTokenId
                                      ├─ BaseCache.clearCacheEntry      (in-memory javax.cache evict)
                                      └─ clearFromSessionStore
                                          └─ SessionDataStore.clearSessionData
                                              └─ queue.push / removeSessionData  ★ Per-token DB INSERT

Additional Trigger Points

The same removeAuthzGrantCacheForUser method is also called from:

Listener Method	Trigger
doPreSetUserClaimValue	Single claim update
doPreSetUserClaimValues	Batch claim update
doPreUpdateRoleListOfUser	Role assignment change
doPreUpdateUserListOfRole	Updating users in a role (per user)

Steps to Reproduce

1. Create an OIDC application with refresh token renewal disabled (the default) and a sufficiently long refresh token validity period.
2. Configure an adaptive authentication script that updates a user claim on successful login. For example, normalizing the username claim to lowercase:

var onLoginRequest = function(context) {
    executeStep(1, {
        onSuccess: function (context) {
            var user = context.steps[1].subject;
            if (!user) { return; }
            var username = user.claims["http://wso2.org/claims/username"];
            if (username) {
                user.localClaims["http://wso2.org/claims/username"] = username.toLowerCase();
            }
        }
    });
};

3. Simulate a large number of token grants for a single user against this application (e.g., thousands of login/token-grant cycles over the refresh token validity window). Since refresh token renewal is disabled, each grant creates a new row in IDN_OAUTH2_ACCESS_TOKEN without revoking the previous one.
4. Log in as the affected user and observe the response time of the /commonauth endpoint.

Expected Behavior

Login latency should remain constant regardless of how many active tokens a user has accumulated. Cache eviction operations should be batched and should not scale linearly with the token count.

Actual Behavior

Login latency scales linearly with the user's active openid-scoped token count. With ~10,000+ accumulated tokens, the /commonauth request takes over 60 seconds — long enough to exceed common gateway/proxy timeouts. Each subsequent login repeats the same work because the eviction does not reduce the token count.

Suggested Fix

Three complementary improvements could address this:

1. Batch session store eviction operations. Collect all token IDs that need eviction and issue them in a single batch operation against the session store (e.g., addBatch()/executeBatch() on a single prepared statement with a single connection and commit). This reduces N individual DB round-trips to a single batch operation.

2. Filter cache eviction by claim relevance. Before calling removeAuthzGrantCacheForUser, inspect the claim URIs being updated and skip eviction if none of them are OIDC claims that could be cached in AuthorizationGrantCache. The set of relevant claims can be derived from the OIDC scope-to-claim mappings configured for the tenant. This eliminates the trigger entirely for the common case of adaptive scripts updating non-OIDC claims. Note: this alone does not resolve cases where the updated claim IS an OIDC claim (e.g., username), but it prevents the issue from manifesting unnecessarily.

3. Asynchronous eviction at the OAuth layer. Move the entire removeAuthzGrantCacheForUser execution (including the SELECT queries and iteration) to an asynchronous task, freeing the authentication thread immediately. This introduces a brief window of stale cached claims, but in practice the window is very short and self-corrects on the next token refresh or issuance.

Version

Identity Server 7.0.0, 7.1.0, 7.2.0, 7.3.0

Environment Details (with versions)

• WSO2 Identity Server 7.1.0 (Update Level 49)
• MySQL 8.4 (InnoDB, default configuration)
• Session persistence enabled (default)
• IdentityOathEventListener enabled (default)
• Refresh token renewal disabled (default)
• persistence_pool_size = 100 (default)
• checkExistingEntryForDeleteOperationInsert = false (default)

Developer Checklist

• [Behavioural Change] Does this change introduce a behavioral change to the product?
•  ↳ Approved by team lead
•  ↳ Label impact/behavioral-change added
• [Migration Impact] Does this change have a migration impact?
•  ↳ Migration label added (e.g., 7.2.0-migration)
•  ↳ Migration issues created and linked
• [New Configuration] Does this change introduce a new configuration?
•  ↳ Label config added
•  ↳ Configuration is properly documented