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