17 views

Description

A node can run out of memory when we read all user roles, including duplicates, into memory. This becomes a major issue for memory when users have a large number of duplicate user role records.
 
To check the potential risk for this issue, navigate to the sys_user_has_role table and group by user.  If there are any users with duplicate role records, then see the workaround to prevent risk of this PRB.
 

Steps to Reproduce

1. Insert a huge amount of duplicate rows for a particular user in the sys_user_has_role table, see example below.
 
mysql> select user, role, count(*) from sys_user_has_role group by user, role order by count(*) desc limit 20; 
+----------------------------------+----------------------------------+----------+ 
| user | role | count(*) | 
+----------------------------------+----------------------------------+----------+ 
| 1f8687fc681d700058633b4c08df2d9c | 29571498ff111000dadaebcfebffadd3 | 270312 | 
| 1f8687fc681d700058633b4c08df2d9c | 3c4d2aaa7f00000128e69a55aef088af | 225268 | 
| 1f8687fc681d700058633b4c08df2d9c | e32593417c3030005863f3683413844a | 180212 | 
| 1f8687fc681d700058633b4c08df2d9c | a82c497cc0a80a647174c407eafc37a9 | 180212 | 
| 1f8687fc681d700058633b4c08df2d9c | 0e38f60b0a0a2c395bf4d4b2a29ddf9b | 157687 | 
| 1f8687fc681d700058633b4c08df2d9c | 702553417c3030005863f36834138450 | 135162 | 
| 1f8687fc681d700058633b4c08df2d9c | 1fdee564406f51007f54ff649f0b170c | 130760 | 
| 1f8687fc681d700058633b4c08df2d9c | 338b3b8b9dcde1007f54cb768cb16061 | 90108 | 
| 1f8687fc681d700058633b4c08df2d9c | 8a454be00a0a0b8c00de7dae26869165 | 90108 | 
| 1f8687fc681d700058633b4c08df2d9c | 32eb39389dd525007f54cb768cb1607f | 90108 | 
| 1f8687fc681d700058633b4c08df2d9c | f9f8d5022b083100795f8a8317da157e | 90108 | 
| 1f8687fc681d700058633b4c08df2d9c | 5b3aea12bf92010032a0854b3f07393f | 90107 | 
| 1f8687fc681d700058633b4c08df2d9c | 3c4d6e837f000001531e632dcdc8a7e9 | 90107 | 
| 1f8687fc681d700058633b4c08df2d9c | 425d00059d1925007f54cb768cb1604e | 90106 | 
| 1f8687fc681d700058633b4c08df2d9c | 528b93c57c3030005863f36834138457 | 67579 | 
| 1f8687fc681d700058633b4c08df2d9c | 6aab2741efa31000a7450fa3f82256be | 67579 | 
| 1f8687fc681d700058633b4c08df2d9c | 01695301efa31000a7450fa3f82256ef | 67579 | 
| 1f8687fc681d700058633b4c08df2d9c | 3dfd67229f22110041a496fcc67fcf6c | 67504 | 
| 1f8687fc681d700058633b4c08df2d9c | 02fbdbf2640d0200084b56f4a9923804 | 45052 | 
| c07bf44d9d1d700026070a68961c9feb | 29571498ff111000dadaebcfebffadd3 | 617 | 
+----------------------------------+----------------------------------+----------+ 
20 rows in set (14.71 sec)
 
2. Generating a user session when logging in for the impacted user on the instance consumes a larger than expected amount of memory

Workaround

The following workarounds for this problem can be applied:

Workaround #1 (preferred workaround)

Switch to use the new Role Management plugin Contextual Security: Role Management V2 (com.glide.role_management.inh_count)

  • Navigate to the plugin table and activate it
  • This has been available since Geneva and all zbooted and new instances since use this new Role Management

Workaround #2 (requires Customer Support, maint role)

If preferring to stay with the older Role Management plugin:

  • eliminate the duplicates in the sys_user_grmember, sys_group_has_role, and sys_user_role_contains tables
  • eliminate the duplicates in sys_user_has_role for the directly-granted roles (i.e. where inherited=false)
  • re-calculate the inherited roles

Attached is the RecalcRoleManagementV1.txt script that is doing most of those steps. However, the duplicates are removed only from sys_group_has_role: there were not many in the other two tables. Once this is complete, add the following unique indexes, which are in Jakarta OOB:

  • sys_user_role_contains (role, contains)
  • sys_group_has_role (group, role)
  • sys_user_grmember (group, user)
 
Note: Do not add unique indexes on fields where there are duplicates.

Related Problem: PRB1263659

Seen In

There is no data to report.

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2018-04-17 09:21:11
Published:2018-04-17