Content References Accessed by a Permission List

Your rating: None Average: 3 (1 vote)

Another permission list query to list Content References accessed by a specific Permission List.

SELECT   a.portal_label AS PORTAL_LINK_NAME, a.portal_objname, a.portal_name, a.portal_reftype
    FROM psprsmdefn a, psprsmperm b, psclassdefn c
   WHERE a.portal_reftype = 'C'
     AND a.portal_cref_usgt = 'TARG'
     AND a.portal_name = b.portal_name
     AND a.portal_reftype = b.portal_reftype
     AND a.portal_objname = b.portal_objname
     AND c.classid = b.portal_permname
     AND a.portal_uri_seg1 <> ' '
     AND a.portal_uri_seg2 <> ' '
     AND a.portal_uri_seg3 <> ' '
     AND c.classid = :permissionlist
     AND a.portal_name = :portalname
ORDER BY portal_label;

We are only interested in translate value of 'TARG' (target) from field portal_cref_usgt on table psprsmdefn. Other available translates on that field are:
FRMT = Frame template
HPGC = Pagelet
HPGT = Homepage tab
HTMT = HTML template
LINK = Content Reference Link

It is important to note that knowing the content reference will make it easy to find the path (PIA navigation) for that content ref. Here is a post to help you with that.

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Sakthi's picture
Sakthi (not verified)
Re: Content References Accessed by a Permission List

Hello,

Sometimes in Peopletools->Portal->Structure and Content
We might hide some Portal links, So it is better include the following in the SQL

AND NOT EXISTS (SELECT '1' FROM PSPRSMSYSATTR d WHERE d.PORTAL_OBJNAME=a.PORTAL_OBJNAME
AND PORTAL_ATTR_NAM='PORTAL_HIDE_FROM_NAV')
-------------------------------------------------------------------------------
SELECT a.portal_label AS PORTAL_LINK_NAME, a.portal_objname, a.portal_name, a.portal_reftype
FROM psprsmdefn a, psprsmperm b, psclassdefn c
WHERE a.portal_reftype = 'C'
AND a.portal_cref_usgt = 'TARG'
AND a.portal_name = b.portal_name
AND a.portal_reftype = b.portal_reftype
AND a.portal_objname = b.portal_objname
AND c.classid = b.portal_permname
AND a.portal_uri_seg1 <> ' '
AND a.portal_uri_seg2 <> ' '
AND a.portal_uri_seg3 <> ' '
AND c.classid = :permissionlist
AND a.portal_name = :portalname
AND NOT EXISTS (SELECT '1' FROM PSPRSMSYSATTR d WHERE d.PORTAL_OBJNAME=a.PORTAL_OBJNAME
AND PORTAL_ATTR_NAM='PORTAL_HIDE_FROM_NAV')

Lepa's picture
User offline. Last seen 5 hours 52 min ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Content References Accessed by a Permission List

Thanks Sakthi, really appreciate your feedback.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
11 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.