Enhanced Query Security
Limit Access to Queries by Role and User
Posted on 1/22/2007 (originally published in print on 5/1/2004)
by Shannon Whitley
In days of yore, PeopleSoft Query was a Windows-only tool that wrapped its security in a tightly compiled ball. Users were stuck with the delivered security model using records that were attached to access groups in a tree structure. My users have always hated the limitations of the security setup in the Query tool. They want to be able to specify exactly which roles or users can see a particular query. Today, with the browser-based version of Query, you can build additional security rules and give the users what they want.
If I could make this warning paragraph blink in bright red letters, you'd be seeing neon right now. You should only attempt the changes in this article if you are completely comfortable modifying PeopleTools. In my latest PeopleTools upgrade, I had to rewrite the code that I'll be showing you. Maintenance is definitely required, and you have the potential to break your Query tool completely, so test these changes thoroughly before rolling anything out to production.
If you're still reading this article, then I assume you have the same problems with Query security that I had. When you provide access to queries through access groups, you don't have very much control over who can see individual queries. For example, it's not easy to hide queries that contain compensation data from the people who should only see other job data. You can do it through the creation of additional views, but that is a pain. At my site, we've developed a process to limit access to queries by role and user. It's not perfect, and it only works in the browser-based version of PeopleSoft, but our users have been very happy with the solution.
The first step in this process is to create a new record, page and component to manage the query security. I created a record called, Z_AUTH_QUERY and inserted the following fields: QRYNAME, ROLENAME, and USER_OPRID. I built this record as a table. Next, I created a page and inserted a grid control. (See Figure 1) I placed edit boxes for ROLENAME and USER_OPRID in the grid. Then I added an edit box outside the scroll at scroll level zero and pointed the edit box to Record.PSQRYDEFN, Field.QRYNAME. I also named the page Z_AUTH_QUERY.
Figure 1
To continue reading this article you must have a current VP1 Subscription.
