Find and Delete Empty Portal CREF Folder Permissions

By Chris Malek | Wed, Jan 23, 2013

This SQL will find Portal CREF (Structure and Content) permissions for folders that have no child grants. These would show up as a folder to the user in the navigation with no links below the folder. This can be caused after an upgrade or when migrating permission lists between databases.

First, Here is some SQL to find the Portal Folder CREF permissions that have no child grants.

  SELECT * FROM PSPRSMPERM P
where P.PORTAL_REFTYPE = 'F'
-- find where there are no grants for child objects of folder
AND NOT EXISTS (
  SELECT 1 FROM PSPRSMPERM PC, PSPRSMDEFN C
      WHERE C.PORTAL_NAME = PC.PORTAL_NAME AND
      PC.PORTAL_REFTYPE = C.PORTAL_REFTYPE AND
      PC.PORTAL_OBJNAME = C.PORTAL_OBJNAME AND
      C.PORTAL_PRNTOBJNAME = P.PORTAL_OBJNAME AND
      C.PORTAL_NAME = P.PORTAL_NAME AND
      PC.PORTAL_PERMTYPE = P.PORTAL_PERMTYPE AND
      PC.PORTAL_PERMNAME = P.PORTAL_PERMNAME)
      AND P.PORTAL_NAME = 'EMPLOYEE';
  • PSPRSMPERM - Is the table that holds Portal CREF (Structure and Content) permissions for the local node.
  • PSPRSMPERM_RMT - Is the table that holds Portal CREF (Structure and Content) permissions for the remote nodes. This table is not included in this query but I wanted to mention it.
  • PSPRSMDEFN - Is the Portal CREF table.

Here is an Oracle friendly statement that will create delete statements based on what is reported above.

Warning: You should fully test this in a test database. I have not fully tested this yet.

    SELECT  Q'[DELETE PSPRSMPERM WHERE PORTAL_NAME = ']' || P.PORTAL_NAME ||
          Q'[' AND PORTAL_REFTYPE = ']' || P.PORTAL_REFTYPE ||
          Q'[' AND PORTAL_OBJNAME = ']' || P.PORTAL_OBJNAME ||
          Q'[' AND PORTAL_PERMNAME = ']'  || P.PORTAL_PERMNAME ||
          Q'[' AND PORTAL_PERMTYPE = ']'  || P.PORTAL_PERMTYPE || Q'[';]'

   FROM PSPRSMPERM P
where P.PORTAL_REFTYPE = 'F'
-- find where there are no grants for child objects of folder
AND NOT EXISTS (
  SELECT 1 FROM PSPRSMPERM PC, PSPRSMDEFN C
      WHERE C.PORTAL_NAME = PC.PORTAL_NAME AND
      PC.PORTAL_REFTYPE = C.PORTAL_REFTYPE AND
      PC.PORTAL_OBJNAME = C.PORTAL_OBJNAME AND
      C.PORTAL_PRNTOBJNAME = P.PORTAL_OBJNAME AND
      C.PORTAL_NAME = P.PORTAL_NAME AND
      PC.PORTAL_PERMTYPE = P.PORTAL_PERMTYPE AND
      PC.PORTAL_PERMNAME = P.PORTAL_PERMNAME)
      AND P.PORTAL_NAME = 'EMPLOYEE';

You may have to generate these deletes several times if you have nested folders that are empty. This script will start from the bottom of the “tree” and go up with each run.

You should run the portal security sync process and delete your web cache after running these deletes.

Article Categories
Author Info
Chris Malek

Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.

About Chris Work with Chris
Looking for pain-free PeopleSoft web services? 😀
PeopleSoft Simple Web Services (SWS)

Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.

Book
Integration Broker - The Missing Manual

I am in the process of writing a book called "Integration Broker - The Missing Manual" that you can read online.