Is there any limitation of number of ranges assigned for BU security in P00950 ?

Jaise James

Reputable Poster

We have been using BU Security for a while. We are thinking of increasing the ranges of BU security from 38 to over 150 per roles. Has any one done this and encountered any system limitations of using large ranges for securing Bu's

I think the impact to the web server memory should be there but the range is not large. However every SQL statements for the user will include 158 additional "where clauses" everytime.

Just wanted to check if there is any system limitation and/or performance consideration I should keep in mind

Any row security changes are a performance consideration, which is highly dependent of the way you have E1 set up and how you use it. You will have to test thoroughly with particular attention to those areas most important to you or that already have a perceived performance problem, before and after the change. Tuning may be able to help, such as building indexes over the MCU on relevant tables.
Depending on overlap, it may even be better to split into separate environments.

There was an issue many years ago with the total length of the SQL statement generated, but that shouldn't be an issue with anymore.
Thanks for your reply. I know there is no official limit as per Oracle.

Problem is one of the busines unit needs to be spilt into two. There ranges are all over the places overlappeing each other. Hence, the issue of such a large number of ranges. We really can't reduce the range.
However in my testing I found something that confirms my theory that this could be slow.I ran the query with smaller set of where clause. It was a simple select from f0101. It return the results in 2 seconds and return the x number of rows.
when I ran the query with large number where clause, it return the same result, But the second query return the results in 3 seconds. I know it's not much. But that 50% degradation. I have made sure to clear the cache on the db before each of them run. I have run the query multiple times with the same result everytime. I think its the SQL server taking more time to process/compile my query thats adding additional lag.

I am thinking if I run more complex join this might have even more degradation. Such degradation for almost all the queries for a user on web server migth add up and that too for decent amount of users might create an overall performance issue.
I am trying to avoid putting this to produciton to gauge the performance hit.
HI Jaise,

This sounds like a lot to me. I have seen examples of this working however. You need to make sure you are inclusive. If you are not I have a tool that will automatically convert the ranges from exclusive (Ns) to inclusive (Ys).
Well if you ever check out the logs the SQL generates, it becomes a real monster, we have a few users in our organization that have over 400 different business unit records and this has created an issue for us, I believe it starts to break at about 300, to circumvent it I believe we had to set up multiple user IDs. Maybe we may need to investing in another tool come upgrade time.
Hi Luke,

Yes we are inclusive only. and this is the least number we could go to have correct BU security.
Hello Michael,

We looked at the two different scenarios

1) Multiple user id with each user id providing different ranges. Unfortunatley, not a very elegant solution. I am sure Users as well as Auditors with balk at this
2) Allowing user to choose roles that has BU ranges while signing in. Unfortunaltey Our security model is designed that we have to disable rolechooser and users have to log in with "*all". Changing this model is a huge task
I might recommend finding something else common about those transactions or records.
Perhaps the companies cover a wide range of business units.
Also, question the assumption about exclusions of being able to see a business unit or range of business units.

Consider also specifying only specific files where possible. This would reduce general overhead.

And like you, the added criteria is a frustration point to me.