Content References Accessed by a Permission List

Wed, 10/01/2008 - 2:21pm — Lepa

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.

Bookmark/Search this post with:

Lepa's blog

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.

Fri, 01/16/2009 - 7:54am

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')

Sun, 02/01/2009 - 9:49am

Lepa

Offline

Joined: 06/23/2008

Posts: 457

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

Your name: *

E-mail: *

The content of this field is kept private and will not be shown publicly.

Homepage:

Subject:

Comment: *

Input format

Filtered HTML

Allowed HTML tags: <div> <pre> <br> <code> <a> <em> <blockquote> <strong> <ul> <ol> <li> <dl> <dt> <b> <p> <h1> <h2> <u> <img>
Web page addresses and e-mail addresses turn into links automatically.
Lines and paragraphs break automatically.
You can enable syntax highlighting of source code with the following tags: <code>.

BBcode

You can use BBCode tags in the text.
Lines and paragraphs break automatically.
Web page addresses and e-mail addresses turn into links automatically.

More information about formatting options

Notify me when new comments are posted

CAPTCHA

The question below is to prevent automated spam submissions.

Math question: * 4 + 8 =

Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.

Unanswered Forum Posts

Title	Under	Posted on
Abs Mgmt: How can Abs rule pro-rate contractor's last month's entitlement (need help urgently)	PeopleSoft Functional	03/10/2010 - 9:21pm
Can we user LONG datatype as a search option in PeopleSoft	PeopleSoft Technical	03/10/2010 - 11:22am
special caracters are not translated correctly when ftp from windows to unix	Programming	03/09/2010 - 3:23pm
SQR Code for converting the data from .CSV into Peoplesoft Tables	PeopleSoft Technical	03/08/2010 - 9:25pm

Main Menu

User login

Quick links

Sponsored links

Recent blog posts

Who's new