Properly document and improve the possible performance degradations due to CaseInsensitiveUsernames property

[mevan-karu]

Describe the issue:

CaseInsensitiveUernames property is set to true by default from 5.9.0 onwards. This is not properly documented in the IS 5.9.0 documentation [1]. We need to add information about the behavioral change this introduces and what factors to consider when deciding the required behavior the customer's deployment. With this config enabled, set of SQL filter queries are executed with the LOWER() SQL function, which could affect the performance of the system due to not having LOWER() indexes. We've identified the following two cases where performance of the system is degraded.

1. When user DB is case insensitive, but the CaseInsesitiveUsernames is set to true. In this case, since the case insensitive usernames in the product is enabled and LOWER() function is added to the SQL filter queries.
2. When user DB is case sensitive, but the CaseInsensitiveUsernames is set to true. In this case, DB is case sensitive and customer needs case insensitivity support from the product level. Hence in this case we need to execute the filter queries with the LOWER() function.

Suggested solution

Properly document the required guidelines for configuring the CaseInsensitiveUsername . For above identified cases;

1. Add a guideline to migration docs [2] and deployment docs [3] to set CaseInsesitiveUsernames property to false in case DB is case insensitive.
2. Document a set of LOWER() indexes to add to the product in case system DB is case sensitive and case insensitivity support is enabled from the product level.

Following are LOWER() indexes given for a customer, but these indexes needs to be analyzed on whether there are any not required indexes, or missing required indexes and finalize the set of indexes needed. The required guidelines and set of indexes needs to be added to our public documentation.

CREATE INDEX IDX_AT_CK_AU_LO ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, LOWER(AUTHZ_USER), TOKEN_STATE, USER_TYPE);
CREATE INDEX IDX_AT_TI_UD_LO ON IDN_OAUTH2_ACCESS_TOKEN(LOWER(AUTHZ_USER), TENANT_ID, TOKEN_STATE, USER_DOMAIN);
CREATE INDEX IDX_AT_AU_TID_UD_TS_CKID_LO ON IDN_OAUTH2_ACCESS_TOKEN(LOWER(AUTHZ_USER), TENANT_ID, USER_DOMAIN, TOKEN_STATE, CONSUMER_KEY_ID);
CREATE INDEX IDX_AT_AU_CKID_TS_UT_LO ON IDN_OAUTH2_ACCESS_TOKEN(LOWER(AUTHZ_USER), CONSUMER_KEY_ID, TOKEN_STATE, USER_TYPE);
CREATE INDEX IDX_AT_CIDAUTID_UD_TSH_TS_LO ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, LOWER(AUTHZ_USER), TENANT_ID, USER_DOMAIN, TOKEN_SCOPE_HASH, TOKEN_STATE);
CREATE INDEX IDX_AUTH_CODE_AU_TI_LO ON IDN_OAUTH2_AUTHORIZATION_CODE (LOWER(AUTHZ_USER), TENANT_ID, USER_DOMAIN, STATE);
CREATE INDEX IDX_AUTH_USER_UN_TID_DN_LO ON IDN_AUTH_USER (LOWER(USER_NAME), TENANT_ID, DOMAIN_NAME);
CREATE INDEX IDX_OCA_UM_TID_UD_APN_LO ON IDN_OAUTH_CONSUMER_APPS(LOWER(USERNAME),TENANT_ID,USER_DOMAIN, APP_NAME);
CREATE INDEX INDEX_IDN_USER_DK_LO_UNIQUE ON IDN_IDENTITY_USER_DATA (TENANT_ID, LOWER(USER_NAME), DATA_KEY);
CREATE INDEX INDEX_IDN_USER_LO_UNIQUE ON IDN_IDENTITY_USER_DATA (TENANT_ID, LOWER(USER_NAME));
CREATE INDEX IDX_UU_LO_UI_UUN_TI ON UM_USER(UM_ID,LOWER(UM_USER_NAME),UM_TENANT_ID);
CREATE INDEX INDEX_UM_USER_LO_UNIQUE ON UM_USER (LOWER(UM_USER_NAME), UM_TENANT_ID);
CREATE INDEX INDEX_UM_SYSTEM_USER_LO_UNIQUE ON UM_SYSTEM_USER (LOWER(UM_USER_NAME), UM_TENANT_ID);
CREATE INDEX INDEX_UM_ACC_MAPPING_LO_UNIQUE ON UM_ACCOUNT_MAPPING (LOWER(UM_USER_NAME), UM_TENANT_ID, UM_USER_STORE_DOMAIN, UM_ACC_LINK_ID);
CREATE INDEX INDEX_UM_HYBRID_UR_LO_UNIQUE ON UM_HYBRID_USER_ROLE (LOWER(UM_USER_NAME), UM_ROLE_ID, UM_TENANT_ID);
CREATE INDEX INDEX_UM_SYSTEM_UR_LO_UNIQUE ON UM_SYSTEM_USER_ROLE (LOWER(UM_USER_NAME), UM_ROLE_ID, UM_TENANT_ID);

Affected product versions

• IS 5.9.0, IS 5.10.0, IS 5.11.0, IS 6.0.0

[1] https://is.docs.wso2.com/en/5.9.0/setup/migrating-what-has-changed/#storing-the-username-in-consent-management [2] https://is.docs.wso2.com/en/latest/setup/migration-process/ [3] https://is.docs.wso2.com/en/latest/setup/deployment-guide/#configuring-databases

[ashendes]

Related issues: https://github.com/wso2/product-is/issues/13055

[Thumimku]

After this improvement, We are going to build a dynamic query to search for users for given attributes (user-name recovery scenario), we are going to use isCaseSensitiveUsername method to differentiate case-sensitive and insensitive attributes and user names.

Hence, We need to set the CaseInsesitiveUsernames property to false in case DB is case insensitive. We need to use LOWER() indexes to add to the product in case system DB is case-sensitive and case insensitivity support is enabled from the product level.

[rksk]

Moreover, if someone is setting CaseInsensitiveUsername to false with a case insensitive database like MySQL or MSSQL, the UseCaseSensitiveUsernameForCacheKeys property also has to be set to false to avoid product keeping case sensitive caches while DB acts case insensitive way.