Compare WebLib.Iscript grants between 2 Users

Sometimes the easiest way to determine why one PeopleSoft user can't access a particular application feature is to compare that user's authorizations to another user who can access that same feature. We can look for differences in Role Memberships, Permission Lists inherited through roles, etc. Sometimes you have to dig deeper, into areas that most people find inexplicable altogether, like WebLibraries and their constituent IScript functions. To that end, the following SQL will bare fruit.
Step1. Create a SQL View (PS_AUTHWEBLIB_USR), which will return a list of authorized WebLib.IScripts for each user:
SELECT DISTINCT A.ROLEUSER
, C.MENUNAME
, C.BARNAME
, C.BARITEMNAME
, C.PNLITEMNAME
FROM PSROLEUSER A
, PSROLECLASS B
, PSAUTHITEM C
WHERE A.ROLENAME = B.ROLENAME
AND B.CLASSID = C.CLASSID
AND C.MENUNAME LIKE 'WEBLIB%'

Please note that each of the fields herein is a key field.

Step2. Build the following query, preferably using PeopleSoft Query Manager:

SELECT A.MENUNAME
, A.BARNAME
, A.PNLITEMNAME
, 'Y'
, 'Y'
, A.OPRID
, ':2'
FROM PS_AUTHWEBLIB_USR A
WHERE A.OPRID = :1
AND EXISTS (SELECT 'X'
FROM PS_AUTHWEBLIB_USR B
WHERE B.MENUNAME = A.MENUNAME
AND B.BARNAME = A.BARNAME
AND B.BARITEMNAME = A.BARITEMNAME
AND B.PNLITEMNAME = A.PNLITEMNAME
AND B.OPRID = :2)
UNION
SELECT C.MENUNAME
, C.BARNAME
, C.PNLITEMNAME
, 'Y'
, 'N'
, C.OPRID
, ':2'
FROM PS_AUTHWEBLIB_USR C
WHERE C.OPRID = :1
AND NOT EXISTS (SELECT 'X'
FROM PS_AUTHWEBLIB_USR D
WHERE D.OPRID = :2
AND D.MENUNAME = C.MENUNAME
AND D.BARNAME = C.BARNAME
AND D.BARITEMNAME = C.BARITEMNAME
AND D.PNLITEMNAME = C.PNLITEMNAME)
UNION
SELECT E.MENUNAME
, E.BARNAME
, E.PNLITEMNAME
, 'N'
, 'Y'
, ':1'
, E.OPRID
FROM PS_AUTHWEBLIB_USR E
WHERE E.OPRID = :2
AND NOT EXISTS (SELECT 'X'
FROM PS_AUTHWEBLIB_USR F
WHERE F.OPRID = :1
AND F.MENUNAME = E.MENUNAME
AND F.BARNAME = E.BARNAME
AND F.BARITEMNAME = E.BARITEMNAME
AND F.PNLITEMNAME = E.PNLITEMNAME)
ORDER BY 1, 2, 3

Please note, that the story does not end here. The listing produced by this query will give you a leg up (maybe), on determining why one user can do something inside a PeopleSoft application while another user cannot. If the feature enabled user does indeed have WebLib.Iscript grants missing from the other user, you will need to determine whether or not these additional grants are wholly or partially responsible for one user's success vs. the other's failure. If you are lucky enough to have something familiar jog your memory (Ex. User 1 is granted access to WEBLIB_SOAPTOCI and User 2 is not), therefore User 1 can use the ExcelToCi tool and User 2 cannot, congrats. On the other hand, the functionality granted by a specific WebLib.IScript may not be easily intuited by its name. In addition, User 1 and/or User 2 may have grants to WebLib.IScripts that don't exist.
I plan to build an application engine program, which will facilitate identifying invalid authorizations of this type in the not too distant future.

How much security is too much security?

I picked up on a post in LinkedIn today titled "Impact of Security on Productivity"

The writer postulates as follows

"I'm researching how too much or inappropriate security measures can have a negative impact on productivity".

My reply follows:

I would love to know what "too much security" means.

I mean no insult to anybody here, but this thread (absent some excellent information) reminds me of Goldilocks and the 3 bears.

Goldilocks logged on with one user account and obtained access to too many things, above and beyond what she required to complete her assigned tasks.

She logged off then back on with a different user account and found that she did not have access to all of the tools she needed to complete her assigned tasks.

She then logged off and on one last time and this time her access was just right.

She could get to all of the tools she needed to complete her assigned tasks, nothing more, nothing less.

Ok, first of all, there is one problem here:

Goldilocks either has too many accounts or is logging on as somebody else.

Ladies and gentlemen, it's all about governance and compliance.

As long as management documents what tools are to be deployed, to whom and under what circumstances, the powers tasked with provisioning users with access to their applications should always grant Goldilocks what's "just right".

If Goldie doesn't have enough (correct) access, that doesn't mean that there is too much security going around.

It means that those parties responsible for access control are having some problems getting it right.

It may not be their fault. Perhaps there was something missing from whatever means were used to communicate Goldilocks requirements to them.

Properly managing access to applications assures Confidentiality, Integrity and Availability (CIA).

Read that as productivity.

If you can provide me herein with real-world examples of a loss of productivity brought about by managed access (not mis-managed access), I would appreciate the information.

PeopleSoft Audit

Monitoring changes to important data and collecting facts about what changed, who changed it and when did it change should not be looked at from the perspective of a list of application data tables.

You should approach your PMO (Project Management Office) and or stakeholders (the managers held accountable for data integrity of the application (Ex: HR Manager and/or Payroll Manager)) for assistance with identifying specific categories of data and thus specific data elements to be monitored for change.

Typically, you will find that they want to see who changed compensation data, or who changed authorizations (permissions, etc.).

Don't rely on making educated guesses.

These managers decide what data to monitor for changes based upon industry standards, corporate policies and government regulations.

Once you have identified what they want "Audited" and where that data is stored, you have a few options available to you.

PeopleSoft audit features monitor changes that take place through the on-line (OLTP) portion of the application via the Component Processor (the heart and soul of PeopleTools).

Data Changes that take place through batch processing (SQR, Application Engine Programs using PeopleCode to update tables directly rather than component interface), will not be recognized by the component processor audit tools.

Having said this, you have two approaches available within the application (the component processor), both of which require working within the Application Designer tools.

1. Field Level Audits.
When a user changes the value of a field (data element) within a web page, information about the change (who changed it, when did it change, what was the original value, what is the new value) get stored in a common table (PSAUDIT). You can turn on the feature for specific record fields within application designer.

2. Record Level Audits
When a user changes any on of a number of fields on a given table, a snapshot of the prior values of the fields may be stored in an "Audit Record". Rather than storing information about who, what and when in a common table (PSAUDIT), it is incumbent upon a developer to construct a new record definition (table) whose name begins with AUDIT (Ex: PS_AUDIT_JOB). This table will store "audit records" containing the values of a collection of fields originating in the PS_JOB table.

3. External to PeopleSoft Audit tools, are data base level auditing features, such as database triggers and database audits.

One advantage of database level auditing tools is that they are not limited to monitoring only those changes that take place via web pages.

They recognize the fact that a field value has changed, even when the change takes place outside of the application (Ex. SQR, COBOL, SQL Scripts, etc.).

I often recommend to my customers that they turn on both record level as well as some for of data base level auditing features concurrently.

If the PeopleSoft Audit Record and Data Base Collection Record mirror each other (have the same record format), this provides you with the opportunity to subtract the PeopleSoft Audit Records (on-line activity) from the Data Base Collection table. Thus the only remaining records/rows stored in the collection table detail data changes that took place outside of the access controls of the application.

Hiding Content From Portal Navigation Does Not Secure It Against Access. By Scott Birnbaum

Hiding portal content references and/or folders from Portal Navigation does not secure them. That is to say, it does not deny user access. Many folders and components are hidden from portal navigation by design, since the user is expected to access these portal menu items via links in web pages.

If you want to prevent anybody/everybody from obtaining authorized access to particular content, then you need to remove authorizations to said content.

I recently visited a customer who wanted to deny access to web pages used to Manage Table Spaces on the database.

We walked through some simple steps in order to identify which permission lists, roles and users were currently granted access to this particular CREF (Content Reference).

1. We copied the CREF Portal Label (the character string of the link in the Portal Navigation) into the Windows Clip Board.
2. We navigated to PeopleTools> Portal> View Menu Item Details.
3. We changed the Search By from "Portal Object Name" to "Portal Label"
4. We pasted the character string from the clip board into the Search Dialogue Box and clicked OK.

The View Menu Item Details page displayed 3 lists:
Authorized Permission Lists
Authorized Roles
Authorized Users

5. We downloaded the list of Authorized Permission Lists, using the download icon on the header bar of the grid.
6. We noted the folder navigation to the Manage Tablespace CREF
7. We navigated to PeopleTools> Portal> Structure and Content and drilled down the folders hierarchy to the Manage Table Space CREF and clicked the Edit link, which took us to the Portal Registry Structure definition for the CREF.
8. We copied the PIA menu name and component name into NOTEPAD.
9. We clicked the "Security" tab, which took us to a list of authorized permission lists.
10. We clicked on the "View Definition" link for the permission list, which took us to the permission list definition in Update mode.
11. We went to the "Pages" tab, which lists the menus containing authorized components (UTILITIES for this item) and clicked the edit components link.
12. We then click "Edit" for the selected component (Manage Table Space)
13. Clicked "De-select All", then OK, then OK, then Save.
14. We repeated these steps for each of the permission lists, which granted access to the component.
15. We then returned to "View Menu Item Details" in order to verify that the Portal Registry permissions had been updated, in this case they were removed.

A Small Tweak To Your Application Development Standards Can Have A Positive Effect On Your Bottom Line

Whenever you create a new component definition, create a new menu definition with the same name.

Locate the new component definition in the new menu definition.

If you need to have the component available from multiple Portal Folder Hierarchies, create a new menu definition for each new location with the same name as the component with a numeric suffix (Ex. _1;_2, etc.).

Background Information:

What problems are we solving here?

Change Management: Ever since PeopleSoft applications arrived, customers have been adding new components and web pages (previously knows as panel groups and panels respectively) to their applications.

Prior to the advent of PeopleSoft Portal Navigation, it was not uncommon for customers to add new components (panel groups) to delivered PeopleSoft menu definitions.

It didn't take very long to determine that this practice would increase the Total Cost of Ownership (TCO), when customers found it necessary to re-integrate the new customer built components into the new versions of the same delivered PeopleSoft menus during an upgrade.

The recommended approach came to be "Whenever possible, customers should add new components to custom(er) built menus. Prior to the introduction of PeopleTools 8.4x this was not considered an optimal solution since menu definitions and navigation within a PeopleSoft application were essentially the same thing.

With the general availability of PeopleTools 8.4x, the linkage between a component definition and a menu definition was decoupled from Portal Navigation.

Thus the recommendation became "Whenever you create a new component definition, place it in a new custom(er) built menu definition." Unfortunately, many customers interpreted this in a very single-threaded manner. Thus they created one or two new menu definitions and added hundreds of components to them.

So what? What's wrong with that?

Just this: When migrating a component from source to target, it should be migrated with its menu definition, portal registry structure, constituent pages and even (under some circumstances) permission lists.

If you have more than one new component under development and both are connected to the same menu, but for whatever reason, not all of the new components are supposed to be migrated from source to target, you would be faced with migrating a menu definition that points to one or more components that are not being migrated.

Prior to PeopleTools 8.4x, this could cause problems. This is no longer the case.

Old habits die hard. Developers refuse to migrate a menu definition that points to a component that isn't there. I can't argue against this if for no other reason than SYSAUDIT will flag this condition as an issue.

If they don't migrate the menu containing the new component, that means that they will have to add the new component to the existing menu definition in the target database, using Application Designer. This is wrong on so many levels. If you want to read more details about why this is a problem, read the first post on this blog.

When faced with the choice of adding 400+ new components to 1 menu definition or 400+ new components to 400+ menu definitions, go with the second choice. I promise you that you will find life so much more enjoyable.

Users Are From Venus. Programmers Are From Mars

Although programmers and end-users may have grown up on the same planet or in the same country or on the same block, you wouldn't know for sure just by listening to them trying to communicate with each other at work. Although they may be speaking the same language, their frame of reference is very different.

My wife read the previous post "Mantra for the day: A Folder Is Not a Menu."

She couldn't understand all of the details therein.

She did get the gist of the article however, which is that depending on the role you play in the workplace, the same word can have different meanings for different people.

If you want to know why you need to know when a folder is not a menu, read the preceding post.

Mantra for Today: A Folder Is Not a Menu.

There is only one menu in PeopleSoft Portal Navigation. At the top of the left hand side, right above the (VERITY) Search box, you will even see its name "Menu".

All of those links below the Search Box, with a small triangle (Carot) to the left are folders.

Any links with a hyphen [ - ] to the left are Content References (CREF for short).

Clicking on a CREF usually results in launching a component.

Users typically navigate to a particular CREF by clicking on a series of folders in a hierarchy until they find it, then launch a component.

Sometimes a customer wants the CREF to be located/re-located to a different folder hierarchy.

This is where the aforementioned mantra comes in handy, as well as knowing who to go to in order to move the CREF to a new folder hierarchy or new sequence in the current folder.

Portal administration is typically performed by somebody designated as the "Portal Administrator". The Portal Administrator will use Portal Administration tools to re-locate the CREF to its new location correctly, even if you say you want it moved to a new menu.

The Portal Administrator's work will essentially update the Portal Registry.

Components (CREF) initially get registered by developers/programmers in a development database. That is to say they (the components, not the programmers) are placed in a folder hierarchy when the programmer registers the component in the portal registry, using the Portal Registry Wizard.

If you ask a Programmer to move a component to a new menu, there is a high probability that he/she will interpret your request in a very different manner from the Portal Administrator, which will have some very unfortunate downstream consequences including Portal Registry corruption as well as Permission List corruption.

This is because unlike Portal Navigation, which only has one menu, the application development environment has hundreds of menus.

The first time the component was registered in the Portal Registry using the Portal Registry Wizard, its original application development environment menu relationship not only was recorded as part of the Portal Registry, it was recorded as part of Permission List configuration.

If a programmer removes the component from its original menu and adds it to a different menu, the original permission list will still be pointing at the old component/menu relationship. The original Portal Registry will still be pointing at the old component/menu relationship.

Running the Portal Security Synch (PORTAL_CSS) program, with "Delete invalid security" selected will remove the permissions associated with the old menu/component relationship from PSAUTHITEM. The Permission List will still be there. It just won't have the component/page permissions pointing to the old menu/component combination that no longer exists.

If nobody took the time to re-register the component at its new menu location (the application development environment menu object), or go on-line to the original permission list definition and authorize access to the component with its new menu object relationship, then testing will be disrupted, since the required permissions (authorizations) don't yet exist.

The best way to avoid getting bogged down in the first place is never to say "Menu" when you really mean "Folder". If you want to get a CREF moved to another folder hierarchy in the Portal Navigation Menu, you need to request that the CREF be moved to a new Folder.

Critical Success Factors for Managing and Migrating PeopleSoft Projects.

The following is a list of 9 habits of highly effective PeopleSoft developers, which promote successful migrations.

1.Always set your PeopleSoft Application Designer Tools Options to:

a.Insert object into project when modified and saved.

b.Prompt for related objects

The benefit of setting these Tools parameters is that the work the developer just did stands a much better chance of actually being stored in the project. Absent these settings, it is incumbent upon the developer to manually insert objects into the project. When taking the manual approach, it is not uncommon for record definitions, component definitions and Application Engine Program definitions to be successfully migrated to the target data base, absent new or modified PeopleCode.

2.Use the Save All feature early and often in order to reap the benefit of the above recommendation.

3.Use the Registry Wizard to register your component in the Portal Registry of your source (Development) data base. Don’t use the Portal Structure and Content component on-line.

The benefit of this approach is that you will be able to insert the Portal Registry Structure for the component you just registered into your project, as well as the menu and component definitions. Permission Lists may be inserted into the project during portal registration as well, though not always desired.

If you don’t use the registry wizard, you will have to register the component again in the target data base, which is wrong on so many levels. Portal registration is a development task, which is supposed to take place in the development data base (source), not System Integration, User Acceptance Testing or Production. If you register a component in any target data base, you have invalidated any testing performed in any source data base.

4.Whenever migrating Permission Lists containing menu/component permissions from source to target, make sure that either:

a.The menu definition, component definition and page definition(s) already exist in the target database.

or

b.The menu definition, component definition and page definition(s) are in the same project as the Permission List.

The benefit of this is that it will ensure a successful migration of the menu/component authorizations stored in PSAUTHITEM for the Permission List you are trying to migrate.

If you don’t satisfy requirement a. or b. above, the Permission List will only be partially migrated. The PSAUTHITEM rows will not be present in the target data base. There will not be any indication of this problem within Application Upgrader in the source data base. The Permission List will appear to have been successfully copied.

5.Always annotate your Application Designer Object Definitions (Object Properties descriptions, both long and short) thoroughly. It’s just plain wrong to leave them blank, and insufficient to simply place your name and a date in the description.

If an object was created or modified based upon a formal change request, copy/paste an appropriate amount of the requirement documentation into the long description, as well as what portion of that requirement is satisfied by the addition or modification of the object.

This might not mean much to the developer, but will mean a great deal to project managers downstream during the next upgrade cycle when they need to decide whether or not to carry forward new objects or modifications.

6.Developers should purge workstation cache files early and often, especially before and after a migration or the occasional execution of the VERSION Application Engine program, which is used to reset all VERSION numbers to 1.

Workstation cache should be purged as a precaution before running Upgrade/Compare Reports between source and target data bases, in order to ensure data integrity during the compare operation.

Workstation (2Tier) cache must be cleared after running the VERSION Application Engine Program, and before logging onto Application Designer (in 2Tier mode).

The VERSION Application Engine Program is used to repair Application Server Caching, which can become problematic due to VERSION corruption, which occurs when Application Designer objects are migrated from multiple source data bases into the same target data base.

If a developer does not purge workstation cache before logging onto Application Designer after the execution of the VERSION Application Engine Program, there is a very good chance that this action will result in VERSION corruption once again.

7.Always run Compare Reports between source and target data bases before migrating (copying) projects.

Review the compare reports carefully and make sure that the flags have been set according to your wishes. You have nobody to blame but yourself if you unintentionally delete objects during a “Copy” process.

8.Always read the Copy Reports carefully in order to verify that the copy ran as planned.

Just because the copy process “successfully” ran to completion does not mean it ran successfully.

It only ran successfully if your project and its contents were copied from source to target.

9.Immediately following a project migration, run the SYSAUDIT report. Be certain to check only those flags that correspond to the objects that were just migrated. For example, if you just migrated menus, components and page definitions, you don’t need (nor do you want) to search for problems with query definitions.

The SYSAUDIT program detects inconsistencies between tools tables, which contain the definitions of the objects you just migrated from source to target. It is most useful for identifying what’s missing from your project.

If objects like PeopleCode programs, Application Engine Program sections, etc. are missing from the project after it has been migrated from source to target, the SYSAUDIT program will probably detect this issue before your customer does.