Understanding Row Level Security

I went through the post below from Grey Sparling Solutions Blog. This post explains the concept of row level security in a very understandable way. Go through the same.

Lot of the application groups within PeopleSoft provided their own row level security setup - HR with the security based on the department tree being a great example. The Financials group went beyond just one type of row level security "out of the box" - I forget how many choices they offered - there were several different common chart of account fields (business unit, department, account, etc.). With the application teams providing row level security in the vanilla install, many customers didn't realize that it is possible to handle row level security differently.

One thing that I always found helpful in customer meetings when this topic came up was to go through exactly how the PeopleTools component processor used search records to handle security. Once you understand that, then you can best decide if changing the row level security that the application groups provide makes sense from a cost/benefit perspective. And there definitely is a cost in the current PeopleTools - fixing row level security so that it was brain dead simple for customers to pick and choose how they wanted to implement it was one of the big ticket items planned for PeopleTools 9.

First, a quick side journey into the component processor (catch me at a conference or user group meeting sometime and I'll tell you the inside story on where the "component" name came from - it'll make you laugh). The component processor is truly the guts of PeopleTools - it is what handles the business logic, database commits, etc. for just about every page that you see in a PeopleSoft application. PeopleSoft components are parent/child hierarchies of database records that automatically get pulled together as one unit of work from the application developer's perspective.

There is one row of data at what is called Level 0. There can be up to 3 levels beyond that. Each level is required to have the same unique keys as the level above it, plus at least one additional unique key field to identify what makes the rows in that level unique.

For example, if a customer master record is at level 0 and has CUSTOMER_ID as it's unique key, you might have customer contacts at level 1 keyed by CUSTOMER_ID and CONTACT_ID. Customer contact phone numbers might be at level 2, keyed by CUSTOMER_ID, CONTACT_ID and PHONE_ID.

The data at each level comes from the key values above it. If the CUSTOMER_ID at level 0 is 1234, then the component processor would automatically use 1234 for CUSTOMER_ID for selecting data into level 1, and so on, down to level 3 (there are ways to override this, but that's how the default behaviour works).

The important thing to keep in mind here from the perspective of row level security is that populating the key fields in the level 0 record drives everything else. This is what the seach dialogs are responsible for.

When you first enter a component via the browser you get a page asking you for whatever the key fields are. In this case it would just be CUSTOMER_ID, but if the level 0 record has multiple key fields, then you'd get prompted for those as well. If you don't know the values, you click Search and get presented with a list of valid values.

But where does that list come from? Each component has what is known as a search record. The component processor uses the search record to supply the values for the key fields of the level 0 record, which then flow down to the child levels.

Any rows of data that the search record returns to the user gives the user access to that data.

So if you wanted to limit someone to only look at active customers, then you could create a view that had the same key fields as the customer master record, but had a WHERE clause like " WHERE CUSTOMER_STATUS='A' ". The view would only return active customers, which means that no one could get into that component to look at an inactive customer.

The component processor recognizes a couple of "special" fields on search records - OPRID and OPRCLASS. If the field OPRID exists on the search record, it automatically gets filled in with the current logged in user's ID. If the field OPRCLASS exists on the search record, it gets filled in with the Row Level Security Class (Permission List) that is specified on the user's security profile.

And that's essentially it for how the component processor handles things for row level security. There are a couple of PeopleCode events that fire when a search dialog is initialized (SearchInit) and when the user clicks "Search" (SearchSave), but those are not really intended for row level security. I'll write more about that in the future.

So how does the department tree security in HR work then? The HR group created a setup page that lets you pick a security class (permission list) and a tree node from the department security tree. For each one of these combos that you select, you decide whether access is granted or denied. All of this data is stored in a table. The HR team then created a view that links this table, the tree manager tables, and the employee department data together. The component processor automatically plugs in the row level security class for the user when selecting from the view, which limits what the user can see.

Financials works in a similar fashion, although the performance of the trees in the view didn't work well enough for them so they ended up with flatter security structures. The HR team actually delivered a couple of de-normalizing utilities for larger customers to deal with this issue as well.

So, how do you change that delivered security?

You could clone the delivered security views (and the tables/pages that they use to maintain the data). I helped a customer once do this where they wanted to use the position tree instead of the department tree.

You can also create your own security structures that do not mimic the delivered ones. All that matters is that you have a table or view that will return the key values that a user has access to. All that matters is that the search record has the same key fields as the level 0 record in the component that you're securing and that you either have the OPRID or OPRCLASS there to provide security.

There are no restrictions about what data the search record looks at or how that data gets maintained. For example, you might have some batch job that updates the table based on security defined in some totally different system. Or maybe someone has to request access to look at some data and security administrator will update the table that the search record looks at. I know of one customer that wanted to have their call center employees only be able to look at customer data when a call came in. So right before the server would send the screen pop for the customer data down to the agent's desktop, the security tables would be updated for that user to have access. As soon as the call was over, then access was taken away.

So the development of a new search record/view is one cost. This includes not just the technical development, but the auditing to make sure that it works properly as well.

Another cost is that the search record is attached to the component definition. So if you update a whole bunch of components to point to different search records, then you have to keep track of that at upgrade time. This is less costly than doing things like changing delivered code since you just need to add one extra step in your upgrade - update any changed components again. Not really a lot of thinking/analysis involved. But, a lot of customers like to run as close to vanilla as they can, so it is worth keeping in mind.

Of course, you don't want to be so afraid of a small cost at upgrade time if your business requirements need different data level security. I talked with a customer once that had turned off row level security in their Purchasing implementation because the delivered methods didn't work for them. Their auditors were complaining (and this was even before all of the current SarbOx push) and they wanted to know when Purchasing would support what they wanted. Changing the row level security is not so hard/costly that you'd want to run without security.