Improvement suggestion for IdentityOathEventListener user attribute cache clearing logic for doPreSetUserClaimValue(s)

[rksk]

In the IdentityOathEventListener [1], with doPreSetUserClaimValue(s) event, we are clearing AuthorizationGrantCache for all the active access tokens and the authorization codes issued for the user. This cache cleanup is very heavy because we are sending cluster messages for each entry and also adding a record to the session data store table to clean session store entry for tokenId.

Recently we have done some improvements to reduce the load by skipping all the tokens without openid scope and also which are expired but the state is still ACTIVE with [2]. The query used to get ACTIVE access tokens which have openid scope with query [3]. Then, we filter out expired tokens (which are still having ACTIVE state) in the Java level.

But the query [3] still seems to be heavily in somecases. For example, some deployements are having tens of thousands of tokens (with random scopes) for a single user where tokens have expired but refresh token is not expired. Hence our query [3] returns a huge dataset and we filter them out in the java level.

We can do the following improvements to the current query [3].

• Avoid nested query since it internally fetches all the tokens first and then only filters out tokens without openid scope
• Remove join with IDN_OAUTH_CONSUMER_APPS since we are not making use of client ID here
• Add validity time checking logic to the query itself. so the resultset will be reduced (network usage)

I've prepared a query [4] with these improvements. Hope we can add this to improve the performance of this flow.

[1] https://github.com/wso2-extensions/identity-inbound-auth-oauth/blob/b563645f8c8fb9ebf2aeed54e8477bcf318ecad5/components/org.wso2.carbon.identity.oauth/src/main/java/org/wso2/carbon/identity/oauth/listener/IdentityOathEventListener.java#L106 [2] https://github.com/wso2/product-is/issues/5126 [3]

SELECT DISTINCT
   ACCESS_TOKEN,
   REFRESH_TOKEN,
   ACCESS_TOKEN_TABLE.TOKEN_ID,
   TIME_CREATED,
   VALIDITY_PERIOD,
   REFRESH_TOKEN_TIME_CREATED,
   REFRESH_TOKEN_VALIDITY_PERIOD,
   CONSUMER_KEY,
   GRANT_TYPE
FROM
   (
      SELECT
         ACCESS_TOKEN,
         REFRESH_TOKEN,
         GRANT_TYPE,
         CONSUMER_KEY_ID,
         TOKEN_ID,
         TIME_CREATED,
         VALIDITY_PERIOD,
         REFRESH_TOKEN_TIME_CREATED,
         REFRESH_TOKEN_VALIDITY_PERIOD
      FROM
         IDN_OAUTH2_ACCESS_TOKEN
      WHERE
         AUTHZ_USER = 'sajith'
         AND TENANT_ID =- 1234
         AND TOKEN_STATE = 'ACTIVE'
         AND USER_DOMAIN = 'PRIMARY'
   )
   ACCESS_TOKEN_TABLE
   JOIN
      IDN_OAUTH_CONSUMER_APPS
      ON ID = CONSUMER_KEY_ID
   LEFT JOIN
      IDN_OAUTH2_ACCESS_TOKEN_SCOPE
      ON ACCESS_TOKEN_TABLE.TOKEN_ID = IDN_OAUTH2_ACCESS_TOKEN_SCOPE.TOKEN_ID
WHERE
   TOKEN_SCOPE = 'openid';

[4]

SELECT DISTINCT
   ACCESS_TOKEN,
   IDN_OAUTH2_ACCESS_TOKEN.TOKEN_ID,
FROM
   IDN_OAUTH2_ACCESS_TOKEN, IDN_OAUTH2_ACCESS_TOKEN_SCOPE
WHERE
IDN_OAUTH2_ACCESS_TOKEN.TOKEN_ID = IDN_OAUTH2_ACCESS_TOKEN_SCOPE.TOKEN_ID
   AND TOKEN_SCOPE = 'openid'
   AND IDN_OAUTH2_ACCESS_TOKEN.AUTHZ_USER = 'sajith'
   AND IDN_OAUTH2_ACCESS_TOKEN.TENANT_ID =- 1234
   AND IDN_OAUTH2_ACCESS_TOKEN.TOKEN_STATE = 'ACTIVE'
   AND IDN_OAUTH2_ACCESS_TOKEN.USER_DOMAIN = 'PRIMARY'
   AND VALIDITY_PERIOD > 0
   AND (TIME_CREATED + INTERVAL '1second' * ( VALIDITY_PERIOD / 1000)) > {UTC time NOW + timestamp skew}