Find and Delete Empty Portal CREF Folder Permissions

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.