Portal Security Sync

The portal registry (basically the left-hand menu navigation) is made up of folders that can contain subfolders and/or content references. Content references point to different types of content, but most of the time in your HCM, FSCM or CRM application they point to components.
The portal registry security table is called PSPRSMPERM. This table defines which permission lists and roles are assigned to each folder and content reference.

When you do edit permission lists on-line, PeopleCode keeps PSPRSMPERM in sync. But not so when you migrate permission lists, or when you build or update them via SQL (a technique I’ve grown to depend on).

So if you migrate a new permission list into an environment, how do you get the portal registry synchronized? Well, that’s the job of Portal Security Sync.
Portal security sync can be a confusing process. In this article I’ll try to make some sense of what it is, why it’s necessary, what kinds of issues you can expect, and some alternatives.
Portal Security Sync is an app engine that basically goes through all of your permission lists, grabs the components, and figures out which content references that permission list relates to. Then it updates the content reference with that permission list, and walks up the folder hierarchy updating each parent folder with that permission list as well. The table that gets updated is – you guessed it – PSPRSMPERM. Starting with PeopleTools 8.45 it even removes security from folders and content references that are not valid any more.

The problem is that Portal Security Sync can grant more access than you want. Keep in mind that the portal registry can provide multiple paths to get to the same component. Suppose you have AR, but not Order Management. You might end up with an Order Management folder on your top menu that takes you all the way down to the Dunn and Bradstreet Customer Information page in addition to the normal navigation through the Customers menu. Or you might find that managers and employees share a common component to update employee information, but it appears under both the Employee Self Service Navigation Collection and the Manager Self Service Navigation Collection. Now suppose you don’t want to let employees see a menu item called Manger Self Service. That’s too bad, because portal security sync will put it on everybody’s menu due to a common component four levels down.

I believe in keeping one set of permission lists across all environments. I depend on migrating permission lists to keep them in sync across all of my environments. So that means Portal Security Sync is essential. I have two techniques to keep folders from popping up where I don’t want them.

First, I use the “Hide from Portal Navigation” on folders that we don’t want (that aren’t licensed for instance). For example, in that Order Management example above I’d just pull up the unwanted folder in “Structure and Content” and click the Hide from Portal Navigation button.

 

 

 

Sometimes though you might not want to eliminate the entire navigation path. Consider the example I mentioned earlier where we have a manager self service and an employee self service navigation collection, and we only want people with a manager role to see the manager self service collection. In this case, we must manually remove the unwanted Employee Self Service permission lists from the Folder Security tab of the Manager Self Service folder, and leave the Manager Self Service ones. In reality, I have an SQL script that I run after Portal Security Sync for this purpose. I plan to create an SQR or app engine to do the deletes, and schedule it to run after Portal Security Sync within a PSJob.

One trick about portal security sync I wanted to mention: It needs to be run as a user with the Portal Administrator role. Portal Administrator is a special role which makes every entry in the portal registry show up, regardless of how your permission lists are defined.

Another problem I see from time to time is that after running portal security sync, the changes don’t necessarily show up for the users. If that happens to you, first check the PSPRSMPERM table or look on-line to make sure the content references actually got populated with the correct permission lists. If that looks OK, have the user clear their browser cache (temporary internet files). If that doesn’t work, purge app server cache. If that doesn’t work, try the web server cache.

Granted, Portal Security Sync is a shotgun approach. If you’re only wanting to set up security for a single component, You can run the Component Registration Wizard process in app designer to get ‘er done. If you like living on the edge, check out the following SQLPlus script I’m working on to show what rows should be in PSPRSMPERM, and inserts them without generating unique constraints. I uses Oracle’s Connect By feature to walk up the folder hierarchy, so my apologies to non-Oracle users. If you run it in SQLPlus, it’ll prompt you for a component.

-- Identify the rows that should be inserted into PSPRSMPERM
select DISTINCT AA.PORTAL_NAME, AA.PORTAL_REFTYPE, AA.PORTAL_OBJNAME, CC.CLASSID, 'P'
FROM
(SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, connect_by_root(A.PORTAL_URI_SEG2) PNLGRPNAME, 'ALLPAGES', 'P', '0'
from (SELECT distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE
FROM PSPRSMDEFN A
WHERE
A.portal_name = 'EMPLOYEE' and
A.portal_objname <> A.portal_prntobjname and
not exists (
select 'x'
from PSPRSMSYSATTRVL
where portal_name = A.PORTAL_NAME AND
portal_Reftype = A.PORTAL_REFTYPE and
portal_objname = A.PORTAL_OBJNAME and
PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and
A.PORTAL_OBJNAME not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
)
) A
start with PORTAL_URI_SEG2 = '&1'
connect by prior A.portal_prntobjname = A.portal_objname ) AA, PSMENUITEM BB, PSAUTHITEM CC
WHERE AA.PNLGRPNAME = BB.PNLGRPNAME AND
BB.MENUNAME = CC.MENUNAME AND
BB.BARNAME = CC.BARNAME AND
BB.ITEMNAME = CC.BARITEMNAME
/
-- Update version stuff so the user doesn’t have to clear browser cache after the update
UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'SYS'
/
UPDATE PSLOCK SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'PRSM'
/

-- Delete the rows that may already exist so we don’t get unique constraints
DELETE FROM PSPRSMPERM PPERM
WHERE (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_PERMNAME, PORTAL_PERMTYPE) IN
(select DISTINCT AA.PORTAL_NAME, AA.PORTAL_REFTYPE, AA.PORTAL_OBJNAME, CC.CLASSID, 'P'
FROM
(SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, connect_by_root(A.PORTAL_URI_SEG2) PNLGRPNAME, 'ALLPAGES', 'P', '0'
from (SELECT distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE
FROM PSPRSMDEFN A
WHERE
A.portal_name = 'EMPLOYEE' and
A.portal_objname <> A.portal_prntobjname and
not exists (
select 'x'
from PSPRSMSYSATTRVL
where portal_name = A.PORTAL_NAME AND
portal_Reftype = A.PORTAL_REFTYPE and
portal_objname = A.PORTAL_OBJNAME and
PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and
A.PORTAL_OBJNAME not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
)
) A
start with PORTAL_URI_SEG2 = '&1'
connect by prior A.portal_prntobjname = A.portal_objname ) AA, PSMENUITEM BB, PSAUTHITEM CC
WHERE AA.PNLGRPNAME = BB.PNLGRPNAME AND
BB.MENUNAME = CC.MENUNAME AND
BB.BARNAME = CC.BARNAME AND
BB.ITEMNAME = CC.BARITEMNAME
)
/
-- Now insert the new Portal Registry Rows
insert into PSPRSMPERM (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_PERMNAME, PORTAL_PERMTYPE, PORTAL_ISCASCADE)
(select DISTINCT AA.PORTAL_NAME, AA.PORTAL_REFTYPE, AA.PORTAL_OBJNAME, CC.CLASSID, 'P', '0'
FROM
(SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, connect_by_root(A.PORTAL_URI_SEG2) PNLGRPNAME, 'ALLPAGES', 'P', '0'
from (SELECT distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE
FROM PSPRSMDEFN A
WHERE
A.portal_name = 'EMPLOYEE' and
A.portal_objname <> A.portal_prntobjname and
not exists (
select 'x'
from PSPRSMSYSATTRVL
where portal_name = A.PORTAL_NAME AND
portal_Reftype = A.PORTAL_REFTYPE and
portal_objname = A.PORTAL_OBJNAME and
PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and
A.PORTAL_OBJNAME not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
)
) A
start with PORTAL_URI_SEG2 = '&1'
connect by prior A.portal_prntobjname = A.portal_objname ) AA, PSMENUITEM BB, PSAUTHITEM CC
WHERE AA.PNLGRPNAME = BB.PNLGRPNAME AND
BB.MENUNAME = CC.MENUNAME AND
BB.BARNAME = CC.BARNAME AND
BB.ITEMNAME = CC.BARITEMNAME
)
/
--Update the version stuff which should keep users from having to clear browser cache
UPDATE PSPRSMDEFN
SET VERSION = (SELECT VERSION FROM PSLOCK WHERE OBJECTTYPENAME = 'PRSM'),
LASTUPDDTTM=SYSDATE
WHERE (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME) IN
(select DISTINCT AA.PORTAL_NAME, AA.PORTAL_REFTYPE, AA.PORTAL_OBJNAME
FROM
(SELECT PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, connect_by_root(A.PORTAL_URI_SEG2) PNLGRPNAME, 'ALLPAGES', 'P', '0'
from (SELECT distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE
FROM PSPRSMDEFN A
WHERE
A.portal_name = 'EMPLOYEE' and
A.portal_objname <> A.portal_prntobjname and
not exists (
select 'x'
from PSPRSMSYSATTRVL
where portal_name = A.PORTAL_NAME AND
portal_Reftype = A.PORTAL_REFTYPE and
portal_objname = A.PORTAL_OBJNAME and
PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and
A.PORTAL_OBJNAME not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
)
) A
start with PORTAL_URI_SEG2 = '&1'
connect by prior A.portal_prntobjname = A.portal_objname ) AA
)
/
-- Un-define our &1 variable so it’ll prompt again the next time the script is run
UNDEF 1