Permission tree update process will take an abnormal time when the result set is vey high from the DB side

[selakap]

Description:
Permission tree update process will take an abnormal time when the resultSet is very high from the DB side (when there are a large number of rows as a result for query [1]). This issue is noticeable for DB types which have cursor data fetching mechanism (fetch the defined number of rows and start the resultset looping without waiting for the completion of the data extract process. eg: oracle) with network latency.
In oracle, we can control the fetching row size using defaultRowPrefetch property. If the result set of [1] is very high there can be an unavoidable delay even we tune the resultset looping time using the relevant property.
Therefore we need to re-consider the logic/query[1] we are using to retrieve the permission data from the DB side.

Please take this issue as a future reference

Affected Product Version:
All kernel versions

Steps to reproduce:
This is a sample test using product apim

• Setup wso2 apim + oracle setup
• Set a minimum value for DefaultCacheTimeout in /repository/conf/carbon.xml (for Key manager node) to observe the issue with a small-time interval (eg: 1s)
• The below query [1] should return a large number of rows (>50000). (To do that you can create multiple APIs with a large number of API documents or import an existing DB dump which contains a large amount of data)
• Execute permission update process (you can invoke an API, start the server, invoke /services/APIKeyValidationService directly, etc)
• Observe an abnormal time to complete the result set loop [2].

1. SELECT UM_ROLE_NAME, UM_RESOURCE_ID, UM_IS_ALLOWED, UM_ACTION, UM_DOMAIN_NAME FROM UM_PERMISSION, UM_ROLE_PERMISSION, UM_DOMAIN WHERE UM_ROLE_PERMISSION.UM_PERMISSION_ID=UM_PERMISSION.UM_ID AND UM_ROLE_PERMISSION.UM_DOMAIN_ID=UM_DOMAIN.UM_DOMAIN_ID AND UM_PERMISSION.UM_TENANT_ID=-1234 AND UM_ROLE_PERMISSION.UM_TENANT_ID=-1234

2. carbon-kernel/core/org.wso2.carbon.user.core/src/main/java/org/wso2/carbon/user/core/authorization/PermissionTree.java

   Line 1133 in 6843f35

   while (rs.next()) {

[ruwanta]

We need to re-look at the need of loading total permission tree.
We can make the permission tree branch loading lazily, when a permission of a particular resource is queried.
This will make a limited set of records loaded at a time and cached. Not the whole set of permissions which most of them are not used.