When Row Security Attacks!
We frequently run Bootcamps covering Row Security in JD Edwards EnterpriseOne, and they are always extremely popular, with plenty of follow up questions on how to get the best out of it.
Whereas Application, Action and other types of Security rely on a simple Yes or No setting, Row Security is more of an art. With the ability to mix Alpha and Numeric Characters, Exclusive versus Inclusive and Ranges of Values that can seem to go on forever, careful design of the model is crucial. Many customers find it so overwhelming that they have tried all sorts of ways to work around this issue, but if you don’t have an army of developers and, just as importantly, time – let’s stick with Row Security!
Through this blog we will address some of the key issues experienced with Row Security, starting with this one, where we look at how Row Security is executed. Having an understanding of how it works behind the scenes is the first step towards designing efficient and effective Row Security.
Inclusive vs Exclusive Row Security
The example below shows Inclusive Row Security setup for the F0101 and the F0411, where we are trying to secure a range (typically a Business Unit, Company or Location) and an Address Type for the AP CLERK Role.
At this point it is important to explain that this has been setup for Inclusive Row Security where we set the values to be secured to ‘Yes’, in this case 10 through to 50.
Exclusive Row Security works in the opposite way; we secure all the other ranges for the AP Clerk to be ‘No’ and thus ranges 10 to 50 would be ‘Yes’. Exclusive Row Security creates more work, more uncertainty and ultimately more load on JD Edwards (we’ll look at this impact below).
This Inclusive Security example looks simple enough, but often customers have many, many lines of Row Security set against their Roles and Users, which makes it very difficult to manage the security and can significantly degrade system performance.
Focusing on the performance issues, let’s take a look at how part of the Row Security above is executed in SQL. Why is this important? Well, it shows how JD Edwards interprets those lines of Security in the screenshot above and works with the database to bring back the data we want this Role/User to see and work with:
The complexity of this statement gives us an indication of the impact on system performance of having tens, hundreds or (gasp) thousands of lines of Row Security for Users and Roles. We have seen such logic-heavy statements cause serious delays in Users getting to the data which they need to perform their Job. For businesses that use Lean, Just In Time and other methodologies which depend on speed, precision and efficiency, anything which slows Users down is very bad news!
In the next post we’ll look at what some Companies are doing to keep things simple and efficient. If you need help in the meantime, please get in touch and we’ll see if we can make that Row Security behave better…