We had an interesting situation with one of our customers recently where creative use of one of our products, the ERP Firewall for PeopleSoft, saved the customer from having to do an emergency PeopleTools upgrade. Needless to say, the drinks are on them at Collaborate.

For those that aren’t familiar with our ERP Firewall for PeopleSoft product, it is a Web Application Firewall that has deep knowledge of PeopleSoft applications. It doesn’t just requests coming in as URL strings that someone can write regular expressions to process, it sees the request in the context of PeopleSoft. It knows what a PeopleSoft component is; it knows what a Web Profile is, it understands PeopleSoft security, etc.

The problem that our customer hit was that when someone enters an invalid password logging in to PeopleSoft, PeopleTools would drop the portal and node name from the URL. Normally this wouldn’t be a problem because most people are accessing the default portal in PeopleSoft (generally the EMPLOYEE portal). When you login to PeopleSoft and don’t specify a portal, you get the default portal. Makes perfect sense.

However, when you also have a large number of customers accessing the CUSTOMER portal, then it gets more interesting. The customer end user attempts to login at https://some.host.com/psp/ps/CUSTOMER/CUST/h/?cmd=login . They enter a bad password by accident, and then they get redirected to https://some.host.com/psp/ps/?cmd=login along with the standard message saying that the username or password is incorrect.

So they type in the correct password and get logged in. Except now they are pointed to the EMPLOYEE portal (because the CUSTOMER portal reference got removed). And not being an EMPLOYEE, they don’t have access to anything. Oops. Their session is valid, but the URL is pointing to somewhere where they get nothing.

Turns out that this is fixed in a PeopleTools patch (8.48.13 for the 8.48 codeline, I’m not sure about other PeopleTools versions), but the customer was live with an earlier patch release in the 8.48 codeline and was concerned about dropping a new version of PeopleTools in.

Since they have the ERP Firewall product already (they use it for restricting employees from using the customer facing / internet accessible web server and force them to go through web servers that are just for employee use) we decided to treat accessing the EMPLOYEE portal as a security condition that we want to detect. However, instead of doing something like blocking access, we calculate the proper CUSTOMER portal URL and silently redirect the user there. So we’re actually using a security tool to solve a usability problem.

You might think that just replacing EMPLOYEE with CUSTOMER in the URL would be enough to solve the problem, but there were a few wrinkles which ended up making the ERP Firewall piece a really good fit.

Part of the challenge was making sure that we kept all of the users correct context when redirecting. Most users would be coming through the portal home page, but some might be coming in from deep links into order history or from bookmarks, etc. So we couldn’t just have a single URL to redirect people to.

The stickier problem was that the ERP Firewall needed to redirect differently based on whether the person was logged on or not. If the user was not logged in, and we redirected them to the CUSTOMER portal home page, PeopleTools viewed that as a login attempt, and gave the user the signon page. Normally PeopleTools handles this quite well; an attempt to hit a deep link in PeopleSoft when you’re not logged in gets you the signon page, and once you login, you go to that deep link that you originally requested.

However, due to this bug, the CUSTOMER portal was getting dropped again, so it was necessary to append the cmd=login parameter that PeopleTools recognizes as a request for the login page. Of course, if the user is logged in already and you redirect them with a cmd=login link, then you just killed their session.

The nice thing is that the ERP Firewall for PeopleSoft has the deep knowledge of PeopleSoft to make this trivial. It knows what a PeopleSoft portal is, it knows what PeopleSoft roles a user has, it knows whether they are logged in or not, and it knows how to properly generate and/or modify PeopleSoft URLs in a safe fashion.

If you are interested in learning more about this product, here’s a link to the product page. In addition to providing an overview, you can watch an in-depth demo that shows exactly how it works and how you would configure it.

Labels: 2008, Firewall, Security

Internet Explorer manages a whole host of security settings through the concept of security zones. Security zones have names such as Internet, Intranet, Trusted Sites. There’s also the concept of a Custom zone, which just means that you’ve gone in and set one of the lower level security settings specifically.

Each time you load a web page, you can see on the bottom right hand status bar in IE, which zone that it thinks that it is in. One thing that catches some people is that IE does not have any sort of automatic facility to detect what is your corporate domain. Even though you may have done Windows level authentication to “mycompany.com”, you’ll still notice that when you visit “psft.mycompany.com”, IE will flag that as an Internet site.

You can manually change these settings for particular sites/domains through Internet Explorer (Tools -> Internet Options -> Security), but if you have lots of machines to update, it’s probably better to use some automation.

Microsoft provides some tools for doing this, but in the case of just adding a single site or domain, you can also go through and just push out updates via .reg files. If you’ve never pushed out any registry changes via a .reg file, it’s not hard, but you definitely want to be careful because if you screw up the registry in Windows you can cause a lot of damage. There are probably people within your organization that can handle this though.

So, with that caveat out of the way, what does a .reg file look like for adding your PeopleSoft servers to the Intranet zone? Assuming that the server names are psfthr, psftfin, psftcrm and are all in the mycompany.com domain and just running http, then this is what you’d have.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionInternet SettingsZoneMapDomainsmycompany.com]

[HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionInternet SettingsZoneMapDomainsmycompany.compsfthr]
“http”=dword:00000001

[HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionInternet SettingsZoneMapDomainsmycompany.compsftfin]
“http”=dword:00000001

[HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionInternet SettingsZoneMapDomainsmycompany.compsftcrm]
“http”=dword:00000001

Not too bad. How about if we just want to flag the entire mycompany.com domain as the Intranet?

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionInternet SettingsZoneMapDomainsmycompany.com]
“*”=dword:00000001

Before making changes like this, you’ll want to check that you don’t already have any custom settings for your standard workstations. If so, then you’ll need to merge these changes in with however those settings get deployed in your organization.

If not, then you can save your own versions of these files with a .reg extension, and they’ll be ready for importing. You can use the /quiet flag for regedit to add this as part of your user’s Windows login scripts.

Labels: Microsoft Windows Browser

We had an interesting “Ask the Experts” question yesterday about how to disable the PeopleSoft page bar across the entire system. The page bar is what has the “New Window”, “Customize Page”, and “Copy URL to clipboard” links in it.

There is a personalization setting for the “Customize Page” link that can be defaulted completely off at the system level, but the other options can only be turned off by going into each component and changing these properties. Financials 8.9 has 6723 components in it. Not something that you want to do one at time, especially since changing these settings is technically a customization.

One potential workaround to this is to just use SQL and update the component properties manually, but you generally don’t want to muck around with making direct updates to the PeopleTools tables (and you’re still customizing a ton of objects – you’re just doing it faster).

The other potential workaround that can be used still involves a customization, but a less invasive one. It involves using a little CSS to hide the page bar. This one line of JavaScript will do the trick.

document.write("<style>#PAGEBAR { display: none; }</style>");

If you put this into one of the delivered JavaScript programs that is added to every page (which is a customization), then you’ll be set.

Note that you haven’t actually disabled this functionality, just hidden it. So if you’re really desperate to keep people from using the pagebar, then you will have to disable it on each component so that the backend knows that it’s disabled as well.

Labels: PeopleCode, Security, User

In my previous posting on passing parameters to nVision, I briefly mentioned something that was unknown to one of the other experts here at Grey Sparling (and if he didn’t know about it, then probably not many other people do as well). Therefore, it warrants its own posting.

Although you can use the query security record to perform data security, you can also accomplish this by adding OPRID, OPRCLASS, or ROWSECCLASS as a key field (but not list box field) in the record definition.

Here’s an example of how it works (both with and without security applied).

With Security
In a financials database, there is a view called SP_PROJ_OPRVIEW, which is intended to allow security at the project level for individual users. The delivered record definition has the following settings for the OPRID field:

Now, if you were to use this record in PS/Query, you would get the following SQL:

SELECT
FROM PS_SP_PROJ_OPRVW A
WHERE A.OPRID = 'VP1'

Without Security

Here’s the same record, with the OPRID key set as a list box item (which turns off row level security for it)

The SQL coming from query would then be as follows:


SELECT FROM PS_SP_PROJ_OPRVW A


Labels: Security

(Sept. 20 update: since writing this we have created a Desktop Single Signon snap-on product that works with PeopleSoft Enterprise. Here’s the announcement and here is the product page).

Single signon is widely desired, yet not widely understood. As usual with PeopleSoft, there isn’t one simple answer, but the good news is that it’s not that hard to get what you want. The biggest challenges are political rather than technical.

So, let’s start by listing a few of the different common definitions of single signon. What most people mean (and want) is that a user signs on once in the morning and is then granted access to all other applications based on that signon. No additional login screens, etc.

Another common definition is that there is only one place for a user to authenticate with. No need to remember 17 different passwords from systems that have different rules about how often to change the password and how long it has to be. The drawback here is that the user still has to authenticate for each system that they access. I’ll refer to this style as “single password”.

Note that I use the word “authenticate” rather than saying “fill in their username and password”. Although most environments are based on username and passwords, the best run environments go beyond just username/password in order to validate the user (think SecureID token).

One interesting wrinkle to all of this is somewhat PeopleSoft specific. PeopleSoft supports a notion of single signon between PeopleSoft instances.

If you have PeopleSoft HR, Financials, CRM, and EPM, then you actually have four different environments, not just four different product lines in one environment. There are some advantages to this loosely coupled model, but unified administration wasn’t one of them. We actually made some progress at this at PeopleSoft towards the end, but it still never got to be as simple as administering one large system.

Given those four separate environments, PeopleSoft supported single signon between them. If a user logged into, say, Financials and then followed a link to the HR system they would not have to signon again. You do need to configure each system to trust each other (you don’t want someone with access to a demo CRM system to be able to access your production Financials), but that is not difficult at all. PeopleBooks has good information on how to do this.

Note that the word LDAP has not yet been mentioned. LDAP is just a common place for storing user information (such as their password, their email address, etc.). By itself, it doesn’t provide single signon. It only simplifies getting single signon working by having a standards-based common location for storing user credentials.

We made some big bets on LDAP support in PeopleSoft 8. When that came out back in 2000, there weren’t really too many enterprise application vendors that supported LDAP. Of course, all of our customers in Higher Education had been telling us to do this for years (especially the University of Michigan). We even had fantasies about dropping our internal authentication support and using LDAP as the out of the box authentication mechanism for PeopleTools 9.

One problem that we had though was that when our field was trying to explain to other customers how this stuff worked, that the concept of single signon and LDAP got confused. Even to the point where the single signon section in PeopleBooks had to be changed to explain that they are not the same thing.

So, out of the box, you can get support for “single password” from the desktop level if your desktop signon uses a backend that supports LDAP (such as Microsoft Active Directory). The first time that the user accesses PeopleSoft they get prompted for their password again, but then (via the PeopleSoft single signon) the user can access all of your PeopleSoft systems.

If you want to go beyond this and have desktop level single signon, then you’ll need to do some customization. A common way of doing this is to have a Windows server running IIS that acts as a proxy server to PeopleSoft. You setup IIS to use NTLM authentication for the proxy link, which will cause Internet Explorer to send in the user’s desktop signon information. Then you create a little bit of signon PeopleCode that will check the custom HTTP header that IIS will attach to the request with the user’s domain and login ID.

If you do this make ABSOLUTELY sure that you validate requests with this header come through the IIS server. Otherwise you’ve just opened up access to your entire PeopleSoft system to anyone that knows how to create an HTTP request with a custom header (which is painfully easy). This is because the IIS server just passes back the domain and username, but does not cryptographically secure it.

The nice thing is that this is not just limited to Internet Explorer. Recent versions of Mozilla based browsers (Mozilla 1.6+, Netscape 7.2+, Firefox 0.8+) also have support for Microsoft’s NTLM protocol. If the user is on a different platform than Windows, then their desktop signon won’t be passed along, but at least they won’t be locked out.

If you want to do this type of Windows desktop single signon, but don’t want/can’t have an IIS proxy server, then you’ll want to look at using jCIFS for that.

How about if you don’t want to use the Windows login as the basis for desktop single signon. Is that even possible with PeopleSoft applications?

Sure. It takes a little bit more work, but it’s possible. You’d have to install something locally on the client machine that get the user’s credentials once, and then passes that along to somewhere where the PeopleSoft server can validate it. Either by passing it along in the browser headers or some other way. If you’re interested in this, take a look at Steve Friedl’s Illustrated Guide to SSH Forwarding. Using SSH as a mechanism for desktop single signon for PeopleSoft applications strikes me as an interesting idea.

Well, there’s more to be said on this topic, but this has been sitting in the queue for too long, so I’m publishing what I’ve got. Please comment if you’re interested in hearing more (as well as what you’d like to hear).

Labels: Products, Security

(update : see ERP Firewall for PeopleSoft)

A few years back I had the opportunity to help out with an interesting customer requirement for dynamic security based on where the user is logging in from. Kirk Chan (another ex-PSFT person) and I got to spend quite a bit of time working with the customer on this. In the end, it was quite successful. In fact, they even presented on this at one of the PeopleSoft Connect user conferences.

The customer was rolling out PeopleSoft eBenefits to tens of thousands employees, a large number of whom didn’t have access to the corporate network except over the public internet. The challenge was that they didn’t want to take any chance that, for example, the VP of HR logs in from a kiosk machine somewhere to do his or her benefits, gets their password swiped, and then someone has access to all of the HR system. So anyone logging in over the internet was to only get the needed privileges for accessing the eBenefits pages, and nothing else. If they logged in from the internal network, then they would receive their usual access level.

Making matters more complicated was that they were about 30 days from their code freeze cut-off date for what would be rolled out. The benefits open enrollment period wasn’t something that can be changed just for dealing with code changes. If this couldn’t be implemented, then they would have to go back to doing things the old way (where most employees would fill in their info on paper). That would be pretty expensive.

Another complication was that their IT group was definitely in the “show me” mood. Later on, we were able to speculate why they didn’t have much confidence in their software vendors at that moment 🙂

So, how’d it work?

The key to this was some custom signon PeopleCode, a couple of extra tables for storing some additional data and a couple of modifications to the one of the delivered security maintenance pages. We got lucky in that they had an ex-PSFT person onsite doing some consulting who helped with the actual implementation of the code for them (Hi Maverick!). What I’m about to describe is actually simpler than what they did because of some additional requirements of theirs (users being able to opt out completely from their account being exposed over the Internet) that I won’t go into in any detail here.

(update: see this blog entry for more things to consider in looking at this customization)

What the signon PeopleCode did was to check which servers they were coming through (this is accessible in the %Request object) and then populate the PSROLEUSER table for them based on that.

The PSROLEUSER table is the table that PeopleTools uses to know what roles a user has. All of the delivered dynamic role stuff (sourcing roles via Query, or LDAP or a custom PeopleCode program) all populate this table.

This is important to understand – if there isn’t an entry in this table for a given user/role combination, then the user does NOT have that role. At runtime this is the only place that is checked for roles. The Internet Architecture does not run any queries or access LDAP servers to determine the roles, they always come from the PSROLEUSER table. You can test this out by deleting rows from this table in a demo system while you are logged in. You will lose the role(s) immediately. Unfortunately the internal PeopleTools code for handling roles that change in mid-session is really bad from a usability perspective – it’s not graceful at all).

Once we know which server the user is coming through, the signon PeopleCode would flush the existing list of roles for that user, and then re-populate it from a shadow table that was created. The shadow table was essentially a clone of PSROLEUSER, but it served to act as the “master” list of the overall set of roles that a user had. The signon PeopleCode would filter this list based on what roles were deemed appropriate for the server that the user was logging in through.

In order to keep this shadow table up to date, the delivered page for maintaining user/role combinations had to be updated to store it’s changes into the shadow table instead of PSROLEUSER. The delivered programs that source role info from Query and/or LDAP also needed small changes to write into the shadow table and not PSROLEUSER.

In a nutshell this work splits out the notion of overall roles and current roles. With the delivered PeopleSoft configuration, these two are tightly linked together. Fixing this was one of the things that was likely for PeopleTools 9.

(note: there are some other ways to do this without changing the delivered objects, but they get a little tricky and the customer didn’t care about the slight bit of extra maintenance in exchange for this functionality).

This particular problem was very focused on the notion of dynamic security based upon whether someone was logging in from inside the firewall or not, but it would be easy to extend it to other scenarios, such as the time someone was logging in. An example might be a student intern in the HR department only gets HR system access during their assigned intern hours, but they’re given access to the Student Admin pages 24/7.

Another scenario would be how the user was authenticated. If someone logs in using a SecurID token, you might grant them a higher level of access than if they only used a username and password.

There’s another scenario where this technique could be applied, but it’s probably worth it’s own blog post because there’s a couple of different ways of solving it.

Labels: PeopleCode, Security

While consulting at the same customer in NYC, we were looking at performance issues related to implementing row level security.

As with many customers who implement row level security with PeopleSoft products, there is a relatively complex join between a set of tables that map users to the data values they have access to, and finally to the data table (i.e. Ledger table in this circumstance). Because the data table has a lot of values, and because the joins to it are complex, performance is often a significant issue.

Thinking back to PeopleTools 9

One of the features planned for PeopleTools 9 was definitional row level security. We planned to provide a centralized means of defining the rules that drove how sets of data values mapped to sets of users (including support for trees). Because we were trying to minimize the administrative effort required by organizations who were implementing this type of security, the logic required to evaluate a rule could be relatively time consuming if it were executed every time a user tried to access data.

Therefore, we decided that a good trade-off was to create a security staging table that was optimized for joining to the data table. This staging table would identify all the individual values a user or user group had access to, allowing an equality join to be done to the data table. The security staging table would be populated periodically by evaulating the rules that drove the mapping of the users to the data values.

Applying the design concept here This customer had already created a set of tables and UI for administering those rules. However, in order to identify which rows of data a user should see in the ledger table, a relatively complex set of joins and unions needed to occur. As we looked at the potential performance implications of this, we recognized that evaluating the rule at the same time data is selected from the table would cause serious issues.

Because this customer was running on Oracle, we were able to use the database to create the staged table using a materialized view. Depending on the complexity of the SQL needed to get the appropriate data, the view can either be updated incrementally, or not. In this circumstance, the SQL required two unions (the first select got the data for the first chartfield, the secon select got data for an alternative chartfield, and the third got data for blank values). This meant that the refresh of the materialized view needed to occur on a pre-defined schedule or be triggered. In this circumstance, the customer created a trigger on each of the tables used in the view (OPRCLASS, the Custom mapping table, and each of the chartfields referenced).

One important note was that the materialized view did NOT include the join to the data table (i.e. LEDGER table). The Ledger reporting view was used instead. This is important, because of the following factors:

• The LEDGER table changes frequently, which means that the refresh would have to be run each time data in the ledger table is modified.
• Materializing the join between the ledger table and the security table would physically store the combination of each user and each row of data that user has access to (in other words, much of the ledger table would be replicated for each user… although disk is cheap, it’s not that cheap.)

The Grey Sparling approach to Data Security

Although this is the method used in PeopleSoft to support row level security out of the box, it does have significant drawbacks. This is why we developed our own reporting security product.

Keep in mind that the PeopleSoft approach to row level security is to hide the security rule from the application and to filter on OPRID, OPRCLASS, or ROWSECCLASS when it finds it in the record definition. This means the following:

1. The criteria used to filter the data is hidden from the report. This is a big no-no from an auditing and compliance perspective, because you cannot easily figure out whether the data was missing in a report because security was applied, or whether the data simply didn’t exist.
2. The filtering of the data is convoluted. In other words, instead of filtering the data directly against the data table, multiple joins are required to do the filtering.
3. It is not trivial to apply the security rules to multiple data tables in the application. In each circumstance, either the record definition must be modified (for PS/Query), or a view joining the data table to the security rules must be created (for PS/nVision or PS/Query).

Our solution is to provide a place where the security rule can be defined in a manner optimal for the administrator, and then apply the rule directly to the report in the reporting tool. This means that the performance from row level security is better, it is easy to see how the data in the report had been filtered, and the security is applied consistently across different ledgers or tables without requiring app designer access or DBA access to create views. The secret sauce to this is how we apply the rule to the report without allowing the person running the it to be able to modify it. If you want to find out how we do that, feel free to contact me to get an NDA, I’ll add you to the Grey Sparling SPAM engine (just kidding), and we’ll talk…

Of course, I’m sure many of you are thinking “Well, if this is so great, why didn’t this customer buy the product instead of using this technique?” The answer comes down to timing. If our product existed before they did the analysis, design, development, and testing, they may have used it. Because we were looking primarily at the performance implications of using something that was about ready to be moved to production, it made more sense to look at performance as a stand-alone item (instead of revisiting the decisions already made). It’s entirely possible that there may be a future posting about how this customer purchased and implemented the Grey Sparling Report Compliance, Security and Distribution Extender.

Labels: nVision, Security

In the previous post about row level security, I mentioned that the two PeopleCode events SearchInit and SearchSave were not intended for providing row level security.

There’s a few different reasons. One key reason is that, by design, online security that is implemented via SQL is security that can be re-used for reporting. Query, Crystal, and nVision will all use the same search record/view when it is attached to tables being reported on. Crystal and nVision do this because they use Query as a data source (note that nVision is not limited to just Query as a data source though).

All you need to do is open the record properties in Application Designer. On the Use tab, you’ll see a prompt for Query Security field. Unfortunately, this prompt is not smart enough to limit your choices only to records that will make sense as security records (same keys, plus OPRID or OPRCLASS), but that won’t stop you from using the same search record/view that you used for the online component.

The next time that you run a Query, Crystal or nVision report that uses that data table, you’ll see that the data is limited appropriately. Even with SQR, where this doesn’t happen automatically, you can perform the join manually to implement this security.

If the data level security is written in PeopleCode, then you’ll have to do extra work for reporting security.

An even more important reason to avoid using SearchInit and SearchSave is to avoid potential security breaches.

Historically the behaviour of the component processor had been that if all of the key values for the search record were provided and the search record/view allowed access, then the SearchInit and SearchSave events did not fire. And why would they, given that there is no need to display a dialog to help someone browse for data that they have already supplied?

Of course, there were a number of people that did make this assumption, so the default behaviour of the component processor was changed awhile back to always force SearchInit and SearchSave to fire.

One consequence of this change in behaviour was that sending someone a URL directly into a page with the data loaded stopped working. Instead the person would see the search dialog with the key values loaded, and they would need to press the “Search” button. Since all of the keys needed to load the page were already present, the user wouldn’t need to select from multiple rows, but it was still a pretty jarring experience for the user.

So, another change was added. This time a new PeopleCode function called SetSearchDialogBehaviour() was added. The function could only be used in the SearchInit event and would allow the application developer control over whether the component processor used the historic behaviour and go straight into the page when all key values were supplied, or to keep the new default behaviour.

In case you’re wondering, there wasn’t an easy way to make the events fire, but still let the page load if all key values were provided (long explanation, but it was considered the most desirable outcome – just couldn’t happen in the releases that needed to be changed).

The problem comes because the reason for this behaviour is still not widely known in the Application Developer community. So you’ll see questions like this along with answers like this. Right answer, but not getting the overall story.

I once had to fly 3000 miles for a meeting with a large PeopleSoft customer that made this mistake in some custom development that they had a consulting company do for them. Only they didn’t figure this out themselves, a major newspaper wrote an article about the holes in the system that allowed anyone to look at anyone else’s data. Not good.

That’s not to say that it’s impossible to do row level security in PeopleCode, but in general you’re better off using the mechanisms that were intended for security.

Labels: PeopleCode, Security

One of the things that is often misunderstood about PeopleSoft applications is how row level security works.

Part of the reason for this is that a 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.

What is Row Level Security in PeopleSoft

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

Also, it’s worth mentioning that there is a way to override the delivered row level security without touching the delivered components. Take a look at this posting I wrote on the PeopleTools forum in ITToolbox for the details on that.

Update:

Since this posting was made, we at Grey Sparling Solutions solved row level security for reporting (which as several additional issues with respect to routing results in report manager). Information about this product, including a flash demo of the solution is available here. For customers who are interested, we can also apply this solution to secure online access.

Labels: Query, Security