October 31, 2005

Magic Search Record Fields

Today being Halloween, it seems appropriate to talk about the spooky, mysterious behavior of certain search records. Have you ever noticed this? You create a search view with carefully constructed SQL behind it. You hook it up to your component and navigate to the page, and it somehow displays only your own data. Nothing in your view SQL says anything about that. What's going on?

There are certain "magic" fields that, if specified just right, will cause the component processor to attach SQL WHERE clauses automatically behind the scenes. This can be a little confusing if you don't know about it and you're trying to find where it's coming from. The fields I know about are OPRID, ROWSECCLASS, OPRCLASS and (I think) EMPLID.

A brief search on these didn't turn up much, but I did find this: In Customer Connection, search on OPRID MAGIC and select the row-level security entry. As part of a discussion on how to set up row-level security, it says that "there are a few minor details that are not well documented." It then names three of the magic fields. (The Customer Connection entry is Financials-related. I also include EMPLID from the HRMS side because I'm pretty sure I've seen the magic behavior attached to that field name also. Could be wrong, though, and I'd appreciate any information you may have.)

The Customer Connection entry then goes through what it calls the "somewhat complicated" rules used by PeopleTools when these fields are used. The important thing to remember is that if you have one or more of these fields defined as keys (but not search keys or list box items) on your search record, Tools will attach WHERE clauses behind the scenes. This is particularly useful in self service applications because it automatically restricts the selection to data the user is permitted to see.

This might remind you of the way that PeopleSoft Query works. You try to report from a table, only to find that some data is automatically filtered out by row-level security. However, it's not really the same thing. In the case of Query, there is no magic. Open the record definition for the table and you'll see that one of the properties is the Query security record. This record (generally a view) is automatically joined to the main table whenever the main table is selected for a query. If you click on the SQL tab in Query Manager, you will see the join. For example, if you are reporting from JOB, then JOB might be aliased as table A, and it will be joined to a view A1. The particular view used as A1 will be the one specified in the JOB record definition as the Query security record. (This, by the way, is often the main reason for slow-running queries...but that's another topic!) Until next time...