Header Ads

  • Recent Posts

    Finding Permission lists and Security details for PeopleSoft components by using SQL Queries

    How do we retrieve all the menus, components and pages used for a particular PeopleSoft module? For example, if one had to get the list of all such components related to Payroll or ePerformance or any other module retrieving the list of pages, components and menus is pain some either doing via online or by any other non-SQL means. Using the translate values of OBJECTOWNERID field, we can get the list of related objects for a particular module and then query the PeopleTools meta tables to retrieve the rest of the information. How do we do this?

    First find the list of translate values from OBJECTOWNERID field related to the module one wants to retrieve the list for. Then using the retrieved values substitute them in the below SQL query to retrieve the Menu name, Menu Bar Name, Bar Item Name and Page(Panel) Item Name.

    SELECT C.MENUNAME, C.BARNAME, C.ITEMNAME, D.ITEMNAME
    FROM
    PSPNLGRPDEFN A,
    PSPNLGROUP B,
    PSMENUITEM C,
    PSPNLGROUP D
    WHERE A.PNLGRPNAME = B.PNLGRPNAME
    AND A.MARKET = B.MARKET
    AND A.OBJECTOWNERID IN (--Provide List of values --)
    AND A.PNLGRPNAME = C.PNLGRPNAME
    AND A.MARKET = C.MARKET
    AND B.ITEMNAME = C.ITEMNAME
    AND A.PNLGRPNAME = D.PNLGRPNAME
    AND A.MARKET = D.MARKET
    AND B.PNLNAME = D.PNLNAME
    ORDER BY 1, 2, 3, 4;

    But what do we do with the menu name, bar name and item names? We would like to have the security related values displayed for the components. So, we have to query PSAUTHITEM table. Only to find out the required values to be substituted in PSAUTHITEM table we wrote the above query. By passing the above retrieved values to PSAUTHITEM table, one can retrieve the required permission lists and relevant values. Here is a single SQL query which does this:

    SELECT DISTINCT E.CLASSID, E.MENUNAME, E.BARNAME, E.BARITEMNAME, E.PNLITEMNAME, E.DISPLAYONLY, E.AUTHORIZEDACTIONS
    FROM
    PSPNLGRPDEFN A,
    PSPNLGROUP B,
    PSMENUITEM C,
    PSPNLGROUP D,
    PSAUTHITEM E
    WHERE A.PNLGRPNAME = B.PNLGRPNAME --Component Name
    AND A.MARKET = B.MARKET
    AND A.OBJECTOWNERID IN (--Provide List of values --) /*List of values and this condition can be removed if run against a particular page or component or menu */
    AND A.PNLGRPNAME = C.PNLGRPNAME
    AND A.MARKET = C.MARKET
    AND B.ITEMNAME = C.ITEMNAME -- Bar Item Name
    AND A.PNLGRPNAME = D.PNLGRPNAME
    AND A.MARKET = D.MARKET
    AND B.PNLNAME = D.PNLNAME -- Page Name
    AND E.MENUNAME = C.MENUNAME
    AND E.BARNAME = C.BARNAME  --Menu Bar Name
    AND E.BARITEMNAME = C.ITEMNAME -- Bar Item Name
    AND E.PNLITEMNAME = D.ITEMNAME -- Page(Panel) Item Name
    ORDER BY 1, 2, 3, 4, 5, 6, 7;

    If the details are required for a particular page or component name then add conditions for PNLNAME with the required page name, PNLGRPNAME with the required Component Name and supply the required Menu related values accordingly.

    For those who are not familiar with the bar and item names, a quick reference is provided below:
    • MENUBARNAME - Menu bar name
    • BARITEMNAME - Itemname used in the menu definition for the panel group(component).
    • PNLITEMNAME - The Itemname used for the panel(page) in the component definition. 
    To further take the list of roles and role users add the appropriate Security tables with the Permission lists retrieved from PSAUTHITEM.

    The above SQL retrieves all the permission lists along with each of its content reference details, visibility and the actions allowed. If you would like to add permissions to a component, here is one of my previous posts -  Providing Permission to PeopleSoft components by updating PeopleTools tables

    No comments

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad