PeopleSoft Signout Page Modifications
Jim Marion has a good writeup on his blog with some ideas for customizing the PeopleSoft signout page, but it doesn't directly cover a common use case that I get asked about a lot, which is redirecting the user somewhere else at signout time. A common reason for doing that is if you have a primary portal, such as the Oracle Portal, that provides access into your PeopleSoft applications. When the PeopleSoft session is done, you want the user to return to the main portal home page. Another reason is that you have some sort of single signon external to PeopleSoft, so you don't want the PeopleSoft users to see the PeopleSoft signon page. How to do itThe process for doing this is pretty straightforward. The HTML that gets displayed at signout time is defined in the "Look and Feel" page of the Web Profile. One common source of confusion with the values defined on this page is that they are not URLs, but they are references to files in the web server. So you can't set a different URL here, you need to have the redirection logic in the appropriate file on the web server. By default, the file that gets displayed at signout time is called signin.html. We'll want to make a copy of this since by default it gets used for the signon page as well as the signout page. You can give it a name like signout_redirect.html ; end users will never see this name though so just name it so that you remember it's purpose later. Whatever name that you call it, you'll want to reference that name for the logout page in your Web Profile. For example, if you wanted to send your users to the Grey Sparling home page with a 0 second delay when they log out from PeopleSoft, you would add the following line immediately after the <HEAD> tag at the top of your new file.
<meta equiv="REFRESH" content="0;url=http://www.greysparling.com/">
The contents of these files are read into memory when the web server starts up, so you'll need to bounce the web server in order to get that to take effect. Preventing Page Flash at Signout TimeWhat I've outlined above is the simplest way to solve the problem. One small usability issue exists though. In spite of the fact that we have specified 0 seconds for the delay, the browser will still render all of the HTML onscreen before sending the user to the new home page. The best course of action here is to remove everything between the <BODY> tags. That saves the browser from having to render the page so it can get to the redirect quicker. There is still a quick page flash, but it is quicker and it is just a blank screen so it doesn't look quite as bad. The HTTP gurus in the crowd are probably wondering why this isn't solved with an HTTP level redirect, which would make the whole page flash issue go away. The answer is that you don't have easy access to do this. You can use some of the options that Jim mentions in his blog (we do something similar in our ERP Firewall for PeopleSoft product) if it is really a big deal to you though. Upgrade IssuesEach time you install PIA (whether from an upgrade or just adding a new web server) you'll need to copy this newly created file from an existing web server (since you no longer care about what new features/layout changes that Oracle might add to these pages in new releases). If you install a lot of web server instances, you can put this file in the setup program so that you always end up with your custom one instead of the default. Just search for the file PTSitePsftdocs.jar in your PSHOME\setup directory. That has the source files that get put into the web server. Session ExpirationThankfully there is a separate page that gets displayed when a session expires vs someone signing out. That file is called expire.html. You may want to setup some separate handling for this one so that the user does see a message about their session having timed out instead of just being redirected to a different page. Where are the files?The actual files to edit in the web server are stored in WEB-INF\psftdocs\<sitename>. I'd list the whole path, but it's so incredibly deeply nested that you'd need a special wide monitor for the browser to be able to scroll that far :-) That's a subject for another blog post some day though. Can I change signout behaviour based on the user?By the time the signout page is displayed, the user's session has already been wiped out. You no longer even know who the user is/was. There are no PeopleCode events that run at signout time by default (although it can be done). Read Jim's blog post or talk to us about ideas for getting around this. Any other tips? If you want to change the label/text of the Signout link, it is defined in the Message Catalog. The message set number is 95, and the message id is 408. Don't forget to update any other languages that you may be using with your PeopleSoft applications. Labels: 2008, Security, Sysadmin
Strengthening Data Privacy in PeopleSoft
Session 2886 in the OAUG section. Monica Nelmes Elliott PeopleSoft Product Marketing Approva Dr Marilyn Prosch, Ph.D., CIPP Department of Accounting Arizona State University Monica is beginning the session by talking about when she was victimized by identity theft a few years back. A Fortune 100 company using PeopleSoft had someone access her account, open up several lines of credit. Big nightmare. So she's very passionate about this issue now. Prior to bringing Dr. Prosch up, she takes a few questions. One was about being able to monitor specific users in PeopleSoft (maybe some new call center employees that you're worried are trying to pull up too many accounts or something). She said that Approva announced a partnership at the conference with a company, Lumigent, that does database monitoring (here is the press release ) Now Dr. Prosch is up. She's been in this area for about 7 years, came in from systems background. Has several slides showing all of the different organizations that have had privacy breaches in 2007. She mentioned that Arizona (where she is from) is now ranked first in the U.S. for identity theft, and that the governor there has just appointed 2 new positions for this. Dr. Prosch says that PeopleSoft is used in many the organizations involved in these breaches. Most are not system hacks, but data downloads where the data/laptops get stolen or from backups that get lost/stolen. 39 states now have identity breach laws, but she does not believe that the federal government is going to do anything soon, so you're essentially required to know about the rules for all of the places that you do business (ed: of course, this is true globally as well). Talking about FTC being more likely to be lenient if you are at least showing that you are taking action The Federal Trade Commission is going after some big cases now. These can have a pretty significant financial impact on an organization. However, she believes that the FTC is more likely to show some leniency if you can show that you were taking action towards preventing breaches before the breach occurred. The discussion then went into the concept of GAPP; Generally Accepted Privacy Principles. Much like GAAP (Generally Accepted Accounting Principles), the idea is to codify best practices for privacy. These are available to download for free and can be applied in your organization today. If you want someone to verify/audit your compliance with GAPP (maybe a business partner mandates this), then you can pay an auditor. The GAPP framework should address most major privacy legislation (domestic and international). It has 66 principles across 10 categories. Dr. Prosch is now talking about the concept of Continuous Privacy Monitoring. She's showing a 5 stage "privacy lifecycle" chart. Stage 1 is ad-hoc efforts around privacy, stage 4 is being ready for a GAPP audit, and stage 5 is continuously monitoring privacy within your organization (ed: to continue the accounting analogy; being able to close the books at any time, instead of just at month's end). Monica is back now talking about defining security rules for roles and permission lists in spreadsheets. How many people can answer who has access to a given piece of data after PeopleSoft has been running for awhile? She's giving a list of example fields to monitor in different PeopleSoft products (the actual field names in PeopleSoft, not just what the fields are). Approva can monitor all uses of sensitive fields in PeopleSoft. Joel Hutchison is an ex-PeopleSoft person who is the main developer for this. He's sitting in the audience, but can take questions. It would have been nice to see a bit more detail about this or maybe a demo, but overall it was a very good session. Labels: 2008, Collaborate, Security
Firewall Product as Savior
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. Of course, it knows lots of other things as well. Let us know if you'd like to learn more about it. Labels: 2008, Firewall, Security
Client/Server Single Signon for PeopleSoft
Larry recently mentioned that we had finally recorded a Flash demo for the client/server portion of our Desktop Single Signon for PeopleSoft product. In a nutshell, what it does is let your developers and reporting power users (Query, nVision) access PeopleSoft without getting prompted for their login. Instead, their Windows/Active Directory credentials that they used to login to the network are used to establish their session. One interesting aspect of implementing our Desktop Single Signon with the client/server support is that you can now run a PeopleSoft system without anyone(3) having a password inside PeopleSoft. You'd still have the accounts needed for booting the appservers, process schedulers, etc., but no passwords that a human (1) would ever use would need to be stored inside PeopleSoft. Even developers or people promoting changes from one PeopleSoft instance to another (such as from test to production) would not have passwords within PeopleSoft. What's really cool about this is that it leverages something that has existed since PeopleTools 1, which is the psuser.dll user exits. PeopleSoft delivers psuser.dll without any delivered functionality in it, except for a couple of C functions that PeopleTools will call at login time. The delivered psuser.dll does nothing, but PeopleBooks documents how you can supply your own implementation to override signon logic for 2 tier and 3 tier connections. Back in the client/server heyday this functionality was used by a few different vendors for doing client/server single signon. The only one that I could find from a few web searches was Novell's Single Signon for PeopleSoft, but they appear to have given up on that as of PeopleSoft 7.x. In fact, they don't even list PeopleSoft at all in their list of applications that they support (2). So, Grey Sparling is the only vendor selling a client/server single signon product for PeopleSoft 8 applications today. We're first and last to market! All kidding aside, it is interesting that no one does anything with the client/server aspects of PeopleSoft, since you quite literally can not run a PeopleSoft shop without at least some people using the client/server tools. When your developers make a change to PeopleSoft, it is typically done through a 2 tier Application Designer session. Same thing for promoting changes between different environments. Same thing for applying maintenance. There are also still a number of places that use the client/server reporting tools. I know of a few places where the 3 tier Query users number in the hundreds (including one customer with somewhere north of 800 Query users). And there are no password controls for 2 tier connections. If someone's password has expired and they try to login via 2 tier, guess what happens? They'll get logged right in. Why? Because there is no signon PeopleCode for 2 tier connections and password controls are enforced with signon PeopleCode. Another interesting wrinkle with giving 2 tier access is that you may be inadvertently circumventing other security measures that you have in place for PeopleSoft web access. How? Suppose that you use the delivered PeopleSoft support for validating your user logins against an LDAP directory. Your users type in their username and password in the standard PeopleSoft signon screen in their web browser and the PeopleSoft application server tries validating those against the LDAP server, where there are more stringent security measures in place than PeopleSoft supports. Except that PeopleTools will always try the username/password against the PeopleSoft database first, checking in the PSOPRDEFN table. You can't disable this behavior (which is documented in PeopleBooks). Your 2 tier users can login to a PeopleSoft web session by just using the same user/password that they use for 2 tier. The LDAP server will never be consulted and neither will any PeopleSoft password controls. Most PeopleSoft shops deal with this by having anyone that has 2 tier access use a different user account for client/server sessions vs web sessions. That is better than bypassing security controls, but most auditors are not too happy about people having multiple accounts for the same system. So if your PeopleSoft auditors haven't hit you up on this issue, it's probably because they don't realize that it is an issue. One interesting technical implementation detail is that although we plug in to the client/server tools through the delivered PeopleSoft user exit, we actually utilize the web single signon to establish the client/server session (including mapping from your Windows user name to your PeopleSoft user account name. This means that we could potentially port the client/server single signon part of Desktop Single Signon to work with other web single signon products that support PeopleSoft (e.g. Oracle, Sun, IBM/Tivoli, home grown, etc.). No promises, but let us know your thoughts if you're interested. (1) You would still have the database level accounts that are independent of the application being used (i.e. SYSADM or sa). I wasn't implying that DBAs aren't human though. Some of my friends are DBAs :-) (2) Novell does still list SAP's Windows client's (SAP R/3 front.exe and saplogin.exe) in their list of supported Windows applications. (3) See the comments. Brent Martin thought of a use case that I had forgotten about (but shouldn't have!). Labels: 2008, Security
Grey Sparling 2006 In Review
Out with the OldWe watched Dick Clark, drank Champagne, and sang Auld Lang Syne. We also began work on our year-end close, just like so many of the PeopleSoft customers who read our blog. This must mean that 2006 is over and 2007 is beginning. It was such a great year for us that we felt we would be remiss not to blog the year. In with the New2006 marked the first full year of operations for Grey Sparling Solutions. It was a great year to be part of the Oracle/PeopleSoft world. 2005 was a year of uncertainty for PeopleSoft customers ( what is Oracle going to do? what is Fusion all about? who planned the PeopleSoft content at the 2005 OpenWorld?), but 2006 was a huge improvement. Oracle announced Applications Unlimited at the Collaborate 2006 conference, and that has been hugely popular. Oracle also announced that the PeopleSoft products would have their own General Manager structure, with longtime PeopleSofter Doris Wong heading things up. Another interesting metric is that all of the system integrator companies that we work with seem to have more business with PeopleSoft projects than ever before The Grey Sparling EffectWe saw a significant increase in the number of customers using Grey Sparling products. That wasn't too hard since we shipped our first product in November 2005, but we have more customers than employees now, which I always remember as being a key statistic for enterprise software. It also helped that we have several new products that we shipped this year: All of that helped us turn a profit for 2006. Woo hoo! Well, it wasn't a very large profit, but it still feels pretty good to hit profitability in our first 18 months of operation. We also became an official Oracle partner this year. PeopleSoft Experts on the RoadAside from folks that became Grey Sparling customers this year, we talked with a large number of people at various conferences. We were extremely flattered to be asked as subject matter experts by conference organizers to give Oracle (versus vendor) presentations. This allowed us to connect with over 2,000 PeopleSoft customers in person in 2006. PeopleSoft Experts OnlineIn addition to physical meetings with folks, we also met lots of people via our weblog. I'm always surprised that there aren't more PeopleSoft bloggers out there - we certainly know plenty of people with lots to say about PeopleSoft! We were also proud to be credited with inspiring some new bloggers out there. 2006 Blog Entry awards.We figured that if Time Magazine can have its set of top 10's, we should too.: Top Grey Sparling Conference Stories of 2006While we're putting together our top blog entries, we might as well list out our top conference stories of the year. - Best Sales story. At Oracle Open World, we literally had a PeopleSoft customer come up to our booth on the first day and ask us how quickly we could generate an invoice for our Desktop Single Signon product. We did a remote install using the Open World wireless, and they were using the product live within 2 weeks for their 2007 Open Enrollment. Initial contact to Production in less than 2 weeks!
- Runner-Up Sales story. At the FSIUG in New York after demonstrating the nVision Drilling Snap-on, we did a remote installation of a trial version at a customer. This customer was able to use WebEx to show it to end-users who were not at the conference, and decide to purchase it right then and there.
- Best Cheapskate story. This had to be at the Alliance Conference, where we discovered how expensive it would be to rent an additional table for our booth for the 3 days the exhibition hall was open. Bert Laws of BearingPoint (a local of Nashville) was kind enough to make a run to Costco and buy a table for us (yes, it was significantly less expensive to buy a table than to rent it). We ended up giving the table to the folks at the Dell booth next to ours at the end of the conference (it would have been too difficult to check with our baggage).
- Best Clueless travel story. As good interenet users, we used Google Maps to print driving directions for all the places we were going in the UK while we were there for the UKOUG (knowing that Chris's mobile account for his Blackberry wouldn't work there). I don't remember the last time I had to look at a map to get where I needed to go in the states. This didn't work too well in the UK, and we ended up getting hopelessly lost many times on the trip. We ended up buying a map to help us make it through the trip.
2007 - The Year AheadThe coming year looks to be an exciting one as well. With the success of the conferences we attended last year, we're signed up for lots of conferences this year as well. Although the sessions haven't been formally approved for these conference, expect to see us at: - 2007 Northern California Training Day
- 2007 Alliance Conference
- 2007 EMEA Conference
- 2007 Collaborate
- 2007 Open World
From an engineering perspective, we've got two new products that we're partnering with customers to build in the first half of the year: - nVision Bolt-on. This will address most of the outstanding nVision issues that we had hoped to address while we were still at PeopleSoft.
- Workflow Notifications. This is a product requested by a long-time Higher Education customer who saw our email notification functionality in our report distribution product, and are currently grappling with the best way to deploy workflow with eProcurement and HCM applications.
We also have plans to add some new features to our existing products, so if you've already licensed something from us, you'll probably hear from us soon. Labels: 2006, Events, nVision, PeopleCode, PeopleSoft, Reporting, Security, User
Automating Password Protected Screensavers for Windows Desktops
One thing that comes up every so often when we're talking with people about our Desktop Single Signon for PeopleSoft product is the issue of having unsecured workstations. If someone walks up to my workstation while I'm at lunch and can just launch a browser to access PeopleSoft as me, then isn't that a security problem? Of course it is. The same as people writing down passwords on post-it notes or increasing your PeopleSoft session timeouts to some large value. The good news is that there is an easy way of dealing with this without compromising usability. This article from Microsoft steps through how to use a Windows Group Policy Object (GPO) in order to enforce users having password protected screensavers. The article (which is part of a larger series) implements this for just the domain Administrators, but it can easily be applied to everyone in the domain if you want. Once the GPO has been implemented for the domain, it takes effect at the time of the next login by each user. Also, this only works for users with Windows 2000 and above. If you have people still running Windows 98 or something, then this doesn't apply to them. You have bigger security problems if you're still running Windows 98 in your organization though. Note that although this discussion has been in the context of implementing our Desktop Single Signon product, it's good advice for any organization. Having password protected screensavers kick in after a short period of inactivity will help protect more than just your PeopleSoft applications. Labels: Microsoft, Security
Oracle Open World Session 281435 Powerpoint - Advanced PeopleTools Tips and Techniques
For those who were interested in downloading the powerpoint to the presentation Chris gave, here it is.It contains the tips he gave in the following areas: - Drilling Into Application Designer
- Security Drilling / Portal Administration Drilling
- Dynamic Tracing or Row Level Security
- Enhancing PeopleSoft User Experience
- Automating "Save As" Processing
Update: here are the code snippets from my demo. As mentioned in the presentation, these are PeopleTools 8.4 projects that you can import into your environment. Labels: 2006, Events, Security
Desktop Single Signon for PeopleSoft
We're pleased to announce a new product, Desktop Single Signon, that we started shipping last week. Desktop Single Signon allows your PeopleSoft users to connect in to PeopleSoft via their Windows login. Once someone logs in to Windows, then they won't see a PeopleSoft signon screen again. We had heard from several folks out there that had read our blog entry about PeopleSoft and single signon from awhile ago found it interesting, but they were looking for something productized that they could put into their environment. This was also something that has come up from talking with our current customers about some of the reporting challenges that they face. We've productized solutions to a number of PeopleSoft reporting challenges, but ease of access for a casual user base came up on a regular basis. Check out the Flash demo of the Desktop Single Signon product in action if you're interested. Also, just a quick note on product scope. The first version supports browser based desktop single signon, but we're already working on single signon for the reporting design tools (PS/Query, PS/nVision, Crystal Report Designer, Tree Manager) as well as the development tools (Application Designer, Data Mover, even Definition Security!). Labels: Security
Hiding the PeopleSoft Pagebar in all component
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
Drag and Drop stopped working in Tree Manager?!?!
One of the cool features added in PeopleTools 8.4 was drag and drop in tree manager. Yesterday, as we were installing Report Explorer at another customer, they mentioned that recently drag and drop stopped working in their environment. Interestingly, they had been dealing with that issue for quite some time although the solution was relatively simple. Drag and Drop in Tree Manager... What's that?Drag and drop allows you to move tree nodes around within a tree without requiring you to cut and paste them. This makes the product much more usable, when you are resequencing nodes or moving subtrees from one location to another. For companies that perform reorganizations on a regular basis, this feature is a life saver.  Okay, so why did it break all of a sudden?Good question. The drag and drop features in Tree Manager are enabled through some javascript embedded in the Tree Manager page. In almost all the circumstances where drag and drop stops working, it has to do with the security settings in Internet Explorer. Mike Friedman put together a very good posting on the IEBlog that explains how Internet Explorer applies and manages security related to JavaScript and other embedded objects that could be used to perform malicious activities on a machine. Great... How do I fix it?Fortunately, the fix is relatively simple. You merely need to make your PeopleSoft URI a trusted site in Internet explorer. In the default IE configuration, JavaScript will execute on a trusted site. Here's how you do it. First, you go into Internet Explorer and use the Tools menu and select Internet Options.  You will then be displayed the Internet Options dialog. Click on the Security tab to modify the security settings.  Finally, click on the Trusted Sites button and enter your PeopleSoft site into it. You can use wildcards in the URL, if you like.  Drag and Drop should start working if you put in a valid address. Labels: Security, Tree_Manager, User
Little known Row Level Security hook
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 SecurityIn 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
App Designer 3 tier login from the command line
It was just pointed out to me that PeopleBooks does not document how to connect to an application server (3 tier connection) from the command line instead of just directly to the database (2 tier connection) for Application Designer. So instead of a command line like this which connects you directly to the Oracle instance pside -CT ORACLE -CD FSDMO -CO VP1 -CP VP1 you'd have something like pside -CX APPSRV -CX hostname:portnumber -CO VP1 -CP VP1 The -CX parameter is for the host where the application server is running, and the portnumber is the port where the Tuxedo Workstation Listener (not the Jolt listener!) is. This defaults to port 7000 in a demo installation of PeopleSoft. Connecting in 3 tier with Application Designer is mainly useful for debugging. If you only connect 2 tier, then you can't debug anything that is happening on the application server. Labels: Security
PeopleSoft Single Signon
(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
Dynamic PeopleSoft Security Based on Login
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. 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
Materialized Views and Row Level 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 9One 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 hereThis 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: - 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.
- 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.
- 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
Why You Should Avoid PeopleCode For Row Level 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
Understanding Row Level 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. 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. 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
Field Level Security in PS/Query
This came up in ITTOOLBOX and is a follow-up on the previous posting I put together on this topic. The person asking it wanted to know how to add field level security to PS/Query, even though it was never part of the product. Here is my response. Probably the most elegant solution would be to modify PS/Query to do what you want it to do. Before Oracle acquired PeopleSoft, this would not have been something you would have wanted to do (because development could make a bunch of changes in Query in a future release and hose you). However, it's probably pretty unlikely that (a) you'll be upgrading any time soon, or (b) Oracle will make dramatic changes to PS/Query. If you want to have a general-purpose way of doing field level security, you can create a table for maintaining the fields and the users who should not see those fields. In PeopleCode, you can check to see if that field is on the query and whether the user has access to the field and delete it from the Query if they don't. Obviously, you will have to figure out how you want to handle public queries and design access. But, if all the person is doing is running a query, you can delete the field and not save it (assuming you're running on PeopleTools 8.4 or greater). Labels: Database, Query, Security
|
|
Subscribe Now!
