By Chris Malek | 10 October 2012
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;