List URLs Accessible to a Permission List

Here is some SQL that will list all the component URLs accessible for a given permission list. I was doing some security auditing recently and I found this useful. I needed a list of all URL that a permission list had access to even if they were not listed in the portal navigation.

You need to modify the a.classid = 'HCCPHP1000' where clause based on your needs. This was generated for a HRMS database so the second column returned may also need to be tweaked based on your installation.

select DISTINCT A.CLASSID,  TRIM(DBMS_LOB.SUBSTR(U.URI_TEXT)) || 'EMPLOYEE/HRMS/c/' 
|| trim(M.MENUNAME) || '.' 
|| trim(M.PNLGRPNAME) || '.GBL'
from psauthitem a, PSMENUITEM M,
PSMSGNODEDEFN N , PSNODEURITEXT U

WHERE A.MENUNAME = M.MENUNAME
AND A.BARNAME = M.BARNAME
AND A.BARITEMNAME = M.ITEMNAME
AND N.ACTIVE_NODE = 1
AND N.LOCALNODE = 1 
AND N.LOCALDEFAULTFLG = 'Y'
AND N.MSGNODENAME = U.MSGNODENAME
AND U.URI_TYPE = 'CN'  
and a.classid = 'HCCPHP1000'  -- replace me
ORDER BY 2;