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...