Performance Impact of Adding Row Security on MCU

[meadek]

Hi Everyone!

We recently implemented row security for our HR roles using Security Business Unit (MCU). As you may expect, this change was not very popular - particularly by the people in those roles who used to be able to see everything.

There have recently been some complaints about JDE performance at one of our locations, and this security change has been suggested as a possible cause for the performance impact. However, many of the users experiencing issues are not in one of the secured roles. We are continuing to explore the other potential causes of the performance issue, but we'd like to be able to either eliminate this as a cause, or focus more resources on it.

This change required the addition of 38 new row security records for a total of 105 MCU row security records and 1132 total row security records.

My questions are:
- How would performance be impacted for the people in the HR Roles?
- How would performance be impacted for the people in other roles?

I've attached an excel document listing the row security we have in-place. I've included one tab with all row security, and another tab showing only the new HR security.

Thanks for your help!

Kelly

 

[klwong]

It's certainly possible, given the number of row security records you have in place.

Just a small suggestion, every row adds something new that the system checks for. I noticed your row security for MCU references *ALL for the object name. I know this is extra work, but you might want to limit that to the specific tables where you need the security applied.

I'd have to check a debug log to be sure, but perhaps the system looks at the object name first, before looking at the group name to limit where it is applied? This would be why other users are affected, even if they aren't in that group.

 

[cncjunior1]

I've learned the hard way that row security can have a significant impact on performance. The first thing I would do would be an in depth index analysis on your database, because now your SQL statements are going to add an "MCU BETWEEN ..." clause for each table you have defined with row security on MCU, as an example, which will make a big difference to the SQL optimizer on how it will fetch the data.

I can't say for sure how adding the row security for only HR users would affect users outside those groups though. It may either be the hierarchy JDE goes through to read the security records or the number of records you have in your security table.

 

[meadek]

Thanks to both of you for your thoughts. We'll do some monitoring and see what we need to adjust to improve the situation.

I appreciate your help!

Kelly