PeopleSoft Permission List Queries

1. Component Permission List Query:
This query identify the permission lists and its description associated with component.

SELECT   menu.menuname, compdfn.pnlgrpname, auth.classid permission_list,
CLASS.classdefndesc permission_desc
FROM psauthitem auth,
psmenudefn menu,
psmenuitem menuitm,
pspnlgroup comp,
pspnlgrpdefn compdfn,
psclassdefn CLASS
WHERE menu.menuname = menuitm.menuname
AND menuitm.pnlgrpname = comp.pnlgrpname
AND compdfn.pnlgrpname = comp.pnlgrpname
AND compdfn.pnlgrpname LIKE UPPER (:component_name)
AND auth.menuname = menu.menuname
AND auth.barname = menuitm.barname
AND auth.baritemname = menuitm.itemname
AND auth.pnlitemname = comp.itemname
AND auth.classid = CLASS.classid
GROUP BY menu.menuname, compdfn.pnlgrpname, auth.classid, CLASS.classdefndesc
ORDER BY menu.menuname, compdfn.pnlgrpname, permission_list;

2. Content Reference accessed by a permission list:

This query identifies Content references accessed by 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;

FRMT = Frame Template

HPGC = Pagelet

HPGT = Homepage Tab

HTMT = HTML template

LINK = Content Reference Link

3. Page Access By Permission List:

SELECT b.menuname, b.barname, b.baritemname, b.pnlitemname AS pagename,
       c.pageaccessdescr,
       DECODE (b.displayonly, 0, 'No', 1, 'Yes') AS displayonly
  FROM psclassdefn a, psauthitem b, pspgeaccessdesc c
WHERE a.classid = b.classid
   AND a.classid = :1
   AND b.baritemname > ' '
   AND b.authorizedactions = c.authorizedactions;

4. PeopleTools Accessed By a Permission List:

SELECT DISTINCT b.menuname
FROM psclassdefn a, psauthitem b
WHERE a.classid = b.classid
AND (   b.menuname = 'CLIENTPROCESS'
OR b.menuname = 'DATA_MOVER'
OR b.menuname = 'IMPORT_MANAGER'
OR b.menuname = 'APPLICATION_DESIGNER'
OR b.menuname = 'OBJECT_SECURITY'
OR b.menuname = 'QUERY'
)
AND a.classid = :PermissionList;

5. Roles Assigned to a Permission List:

SELECT b.rolename, b.classid AS permission_list
FROM psclassdefn a, psroleclass b
WHERE a.classid = b.classid AND a.classid = :permissionlist;

6. User IDs assigned to a Permission List:

SELECT   c.roleuser AS USER_IDs
FROM psclassdefn a, psroleclass b, psroleuser c
WHERE a.classid = b.classid
AND b.rolename = c.rolename
AND a.classid = :permissionlist
GROUP BY c.roleuser;

Department Security Tree and Query Security

Always create a department security tree with the name "DEPT_SECURITY" as it is hardcoded in various views and very important in implementing Row level security. If this naming is not followed then departments under newly created tree will not be available while setting up the security on "Security by Dept Tree" page.

Query Security:

 

• PeopleSoft Query uses query access group trees to control the access of the tables in the PeopleSoft database.
• You create and update query access group trees using Query Access Manager.
• You should create query access group trees based on your organization’s needs and on any customizations you’ve made.

 

Relationship between row-level security and Query security record definitions:

 

• PeopleSoft applications implement row-level security by using a SQL view that joins the data table with an authorization table.

 

• To apply row level security:
• Open the record on which you want to apply row-level security. Click the Properties button, and select the Use tab from the Record Properties dialog box.
• Select the security record definition (usually a view) in the Query Security Record list box.

 

• Row-Level (Data Permission) Security Views:

1. Using PeopleSoft row-level security views enables you to restrict users from seeing certain rows of data.
2. You can restrict data by:

1. User, by using the OPRID field.

2. Primary permission list, by using the OPRCLASS field.

3. Row security permission list, by using the ROWSECCLASS field

3.             3.   To implement row-level security through a security view:
1. In Application Designer, insert one of the three row-level security fields(OPRID, OPRCLASS, ROWSECCLASS) into the record definition.
2. Configure the field as a Key, but not a List Box Item.
3. Save the record and build the view.
4. Use the record as the search record or query security record.

• Steps for creating query security record:

          1. Create a view depending on your security need.

 

Example: create a view which has oprid, rowsecclass and the parent key field and in sql editor write the following sql:

 

SELECT DISTINCT opr.oprid,
                opr.rowsecclass,
                dtl.t_cust_id FROM   psoprdefn opr,
       ps_t_sjt_class cls,
       ps_t_sjt_class_dtl dtl WHERE  cls.rowsecclass = dtl.rowsecclass
       AND cls.t_cust_id = dtl.t_cust_id
       AND opr.rowsecclass = cls.rowsecclass 

 

2. Attach the above view to the record in record properties (query security record).

 

3.            3. Grant security to that record to which the query security view is attached.
4.            4. In PIA, traverse to query access manager (Oracle PS Tools ->people tools –> Query  Security -. Query Access Manager). Then CLICK ON CREATE NEW TREE.

 

 

5. 5. If access group is already present, then search for that access group in the prompt. 
6. 6. If you want create a new access group then enter the access group name and PRESS ENTER, it will be redirected to the below page where you can create your own access group.       
   
7. 7. Then insert the child records to the access group.

 

step 7.1

Step 7.2   

Step 7.3

 

8. 8. Go to permission list and traverse to query. (People Tools -> Security -> Permission & Roles -> Permission list (Select the permission list)).

 

9. 9. In access group permissions, assign the tree name and access group.

 

 

10. 10. In query manager, add the record to which the query security view is attached.

 

 

NOTE: You should consider adding record definitions to the query trees in a hierarchy that matches the parent/child relationship of records in your database.

PeopleSoft Permission List Queries

1. Component Permission List Query:
This query identify the permission lists and its description associated with component.

SELECT   menu.menuname, compdfn.pnlgrpname, auth.classid permission_list,
CLASS.classdefndesc permission_desc
FROM psauthitem auth,
psmenudefn menu,
psmenuitem menuitm,
pspnlgroup comp,
pspnlgrpdefn compdfn,
psclassdefn CLASS
WHERE menu.menuname = menuitm.menuname
AND menuitm.pnlgrpname = comp.pnlgrpname
AND compdfn.pnlgrpname = comp.pnlgrpname
AND compdfn.pnlgrpname LIKE UPPER (:component_name)
AND auth.menuname = menu.menuname
AND auth.barname = menuitm.barname
AND auth.baritemname = menuitm.itemname
AND auth.pnlitemname = comp.itemname
AND auth.classid = CLASS.classid
GROUP BY menu.menuname, compdfn.pnlgrpname, auth.classid, CLASS.classdefndesc
ORDER BY menu.menuname, compdfn.pnlgrpname, permission_list;

2. Content Reference accessed by a permission list:

This query identifies Content references accessed by 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;

FRMT = Frame Template

HPGC = Pagelet

HPGT = Homepage Tab

HTMT = HTML template

LINK = Content Reference Link

3. Page Access By Permission List:

SELECT b.menuname, b.barname, b.baritemname, b.pnlitemname AS pagename,
       c.pageaccessdescr,
       DECODE (b.displayonly, 0, 'No', 1, 'Yes') AS displayonly
  FROM psclassdefn a, psauthitem b, pspgeaccessdesc c
WHERE a.classid = b.classid
   AND a.classid = :1
   AND b.baritemname > ' '
   AND b.authorizedactions = c.authorizedactions;

4. PeopleTools Accessed By a Permission List:

SELECT DISTINCT b.menuname
FROM psclassdefn a, psauthitem b
WHERE a.classid = b.classid
AND (   b.menuname = 'CLIENTPROCESS'
OR b.menuname = 'DATA_MOVER'
OR b.menuname = 'IMPORT_MANAGER'
OR b.menuname = 'APPLICATION_DESIGNER'
OR b.menuname = 'OBJECT_SECURITY'
OR b.menuname = 'QUERY'
)
AND a.classid = :PermissionList;

5. Roles Assigned to a Permission List:

SELECT b.rolename, b.classid AS permission_list
FROM psclassdefn a, psroleclass b
WHERE a.classid = b.classid AND a.classid = :permissionlist;

6. User IDs assigned to a Permission List:

SELECT   c.roleuser AS USER_IDs
FROM psclassdefn a, psroleclass b, psroleuser c
WHERE a.classid = b.classid
AND b.rolename = c.rolename
AND a.classid = :permissionlist
GROUP BY c.roleuser;

Changing the PeopeleSoft Component Tab name (page naem)

We can do the change in two steps:

Step 1: Create HTML Object (html.TEST_ITEM_LABEL) with below script.

<script type="text/javascript"> 

var tabContainer = document.getElementById("PSTAB"); 

if (tabContainer) 

{ 

tabContainer.firstChild.firstChild.firstChild.childNodes[0].firstChild.firstChild.innerHTML = "<font color=black size=2 <b>E-Invoice - %bind(:1)</b></font>"; 

} 

</script>

Step2: Write the below peopelcode in page activate.

TEST_INVOICE_WRK.HTMLAREA.Value = GetHTMLText(HTML.TEST_ITEM_LABEL, VENDOR.NAME1);

Query for Tree Security

                                Query for  getting list of  records  from access group and tree 

SELECT R.TREE_NAME, G1.TREE_NODE,G.TREE_NODE,R.TREE_NODE  FROM pstreenode  R,  pstreenode  G      ,(SELECT TREE_NAME,TREE_NODE,TREE_NODE_TYPE FROM pstreenode WHERE TREE_NODE_TYPE = 'G' )

G1

WHERE R.TREE_NODE_TYPE = 'R'

AND G.TREE_NODE_TYPE = 'G'

AND R.PARENT_NODE_NAME = G.TREE_NODE

AND G.tree_name = 'QUERY_TREE_AM'

AND R.TREE_NAME = G.TREE_NAME

AND G.PARENT_NODE_NAME = G1.TREE_NODE (+)

AND G.TREE_NAME = G1.TREE_NAME            (+)

ORDER BY 1,2 ;

Query to find the list of users having access to component, action along with Navigation details

 

SELECT DISTINCT roleclass.rolename,
authitem.menuname,
authitem.menulabel, 
authitem.baritemname,
authitem.itemlabel,
DECODE(authitem.DISPLAYONLY, 0, 'N', 1, 'Y') AS "Display_Only",
CASE authitem.AUTHORIZEDACTIONS
    WHEN 1 THEN 'Add'
    WHEN 2 THEN 'Update/Display'
    WHEN 3 THEN 'Add, Update/Display'
    WHEN 4 THEN 'Update/Display All'
    WHEN 5 THEN 'Add, Update/Display All'
    WHEN 6 THEN 'Update/Display, Update/Display All'
    WHEN 7 THEN 'Add, Update/Display, Update/Display All'
    WHEN 8 THEN 'Correction'
    WHEN 9 THEN 'Add, Correction'
    WHEN 10 THEN 'Update/Display, Correction'
    WHEN 11 THEN 'Add, Update/Display, Correction'
    WHEN 12 THEN 'Update/Display All, Correction'
    WHEN 13 THEN 'Add, Update/Display All, Correction'
    WHEN 14 THEN 'Update/Display, Update/Display All, Correction'
    WHEN 15 THEN 'Add, Update/Display, Update/Display All, Correction'
    ELSE 'SPECIAL' END AS "Authorized_Actions",
authitem.PNLGRPNAME,
CASE InStr(nav.PATH_TO_COMPONENT, 'Root>', 1)
  WHEN 0 THEN nav.PATH_TO_COMPONENT
  ELSE SubStr(nav.PATH_TO_COMPONENT, InStr(nav.PATH_TO_COMPONENT, 'Root>', 1) + 5, length(nav.PATH_TO_COMPONENT) - InStr(nav.PATH_TO_COMPONENT, 'Root>', 1) + 5)
  END AS navigation
from
(SELECT
DISTINCT a.CLASSID, a.MENUNAME, b.MENULABEL , a.BARITEMNAME,c.itemlabel ,
a.DISPLAYONLY,                                                                               
a.AUTHORIZEDACTIONS,
c.PNLGRPNAME
FROM PSAUTHITEM a, PSMENUDEFN b, PS_ACLCOMPONENT_V2 c
where a.MENUNAME = b.MENUNAME
AND a.menuname =  c.menuname
AND a.barname = c.barname
AND a.baritemname = c.baritemname) authitem ,
psroleclass roleclass,
(SELECT a.PORTAL_URI_SEG2 as component
, E.PORTAL_LABEL || '>' || D.PORTAL_LABEL || '>' ||C.PORTAL_LABEL ||
'>' || B.PORTAL_LABEL || '>' || A.PORTAL_LABEL as PATH_TO_COMPONENT
, A.PORTAL_URLTEXT
, A.PORTAL_ISPUBLIC as Active
, A.LASTUPDOPRID
, A.LASTUPDDTTM
, A.PORTAL_URI_SEG2
FROM PSPRSMDEFN A, PSPRSMDEFN B, PSPRSMDEFN C, PSPRSMDEFN D, PSPRSMDEFN
E
WHERE 1=1
AND A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME(+)
AND B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME(+)
AND C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME(+)
AND D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME(+)
AND (E.PORTAL_NAME IS NULL OR E.PORTAL_NAME = 'EMPLOYEE')
AND (A.PORTAL_NAME = 'EMPLOYEE' OR A.PORTAL_NAME IS NULL )
AND (B.PORTAL_NAME = 'EMPLOYEE' OR B.PORTAL_NAME IS NULL )
AND (C.PORTAL_NAME = 'EMPLOYEE' OR C.PORTAL_NAME IS NULL )
AND (D.PORTAL_NAME = 'EMPLOYEE' OR D.PORTAL_NAME IS NULL )
) nav
WHERE  roleclass.rolename =
AND authitem.classid = roleclass.classid
AND  authitem.PNLGRPNAME =  nav.PORTAL_URI_SEG2(+)
ORDER BY rolename, menuname, baritemname

 

PeopleSoft Query to find the objects which are not included in the Project

Below are the SQLs to find the Peoplecode and SQL objects which are  created or modified by you but not included in any of your project.

 

 

--------------------------------------------------

-- Selects Peoplecode not inserted in a project

--------------------------------------------------

SELECT

CASE A.OBJECTID1

when 1 then 'Record Peoplecode'

when 3 then 'Menu Peoplecode'

when 9 then 'Page Peoplecode'

when 10 then 'Component level Peoplecode'

when 60 then 'Message Peoplecode'

when 74 then 'Component Interface Peoplecode'

when 87 then 'Subscription Peoplecode'

END AS  OBJect_Type

,

A.OBJECTVALUE1 || '.' || A.OBJECTVALUE2 || '.' || A.OBJECTVALUE3 || '.' || A.OBJECTVALUE4 || '.' || A.OBJECTVALUE5 || '.' || A.OBJECTVALUE6  || '.' || A.OBJECTVALUE7

AS EXTENDED_OBJ_NAME

FROM PSPCMPROG    A 

 , PSPCMTXT B -- join to pspcmtxt to filter out reverted changes (back to blank)

WHERE  LASTUPDOPRID =

AND  A.OBJECTID1 || A.OBJECTVALUE1 || A.OBJECTID2 ||A.OBJECTVALUE2 || A.OBJECTID3 || A.OBJECTVALUE3 || A.OBJECTID4 || A.OBJECTVALUE4

NOT IN

(

SELECT OBJECTID1 || OBJECTVALUE1 || OBJECTID2 || OBJECTVALUE2 || OBJECTID3 || OBJECTVALUE3 || OBJECTID4 || OBJECTVALUE4

FROM PSPROJECTITEM I, PSPROJECTDEFN   d

WHERE I.PROJECTNAME = D.PROJECTNAME

AND D.LASTUPDOPRID =

and i.objecttype in (8,9,39,40,42,43,44,46,47,48)

)

AND a.objectid1                               = b.objectid1

AND a.objectvalue1                        = b.objectvalue1

AND a.objectid2                               = b.objectid2

AND a.objectvalue2                        = b.objectvalue2            

AND a.objectid3                               = b.objectid3

AND a.objectvalue3                        = b.objectvalue3

AND a.objectid4                               = b.objectid4

AND a.objectvalue4                        = b.objectvalue4

AND a.objectid5                               = b.objectid5

AND a.objectvalue5                        = b.objectvalue5

AND a.objectid6                               = b.objectid6

AND a.objectvalue6                        = b.objectvalue6

AND a.objectid7                               = b.objectid7

AND a.objectvalue7                        = b.objectvalue7

AND  A.OBJECTID1 NOT IN   (66,104)

;    

  

 

 

--------------------------------------------------

-- Selects App Engine Peoplecode not inserted in a project

--------------------------------------------------

SELECT

DISTINCT

'Application Engine Peoplecode',

A.OBJECTVALUE1 || '.' || A.OBJECTVALUE2 || '.' || A.OBJECTVALUE3 || '.' || A.OBJECTVALUE4 || '.' || A.OBJECTVALUE5 || '.' || A.OBJECTVALUE6  || '.' || A.OBJECTVALUE7

FROM PSPCMPROG    A 

WHERE  LASTUPDOPRID =

AND   A.OBJECTID1|| A.OBJECTVALUE1|| A.OBJECTID2||A.OBJECTVALUE2||A.OBJECTVALUE3||A.OBJECTVALUE4||A.OBJECTVALUE5  

 NOT IN

(

SELECT I.OBJECTID1 ||I.OBJECTVALUE1 || I.OBJECTID2 ||REPLACE(I.OBJECTVALUE2,' ','')

 FROM PSPROJECTITEM I, PSPROJECTDEFN   d

WHERE I.PROJECTNAME = D.PROJECTNAME

AND D.LASTUPDOPRID =

and objecttype =43

)

and A.OBJECTID1 = 66

;    

 

--------------------------------------------------

-- Selects App Package Peoplecode not inserted in a project

--------------------------------------------------

SELECT

'Application Package Peoplecode'

, A.OBJECTVALUE1 || '.' || A.OBJECTVALUE2

 FROM PSPCMPROG    A 

WHERE  LASTUPDOPRID =

AND   A.OBJECTID1|| A.OBJECTVALUE1|| A.OBJECTID2||A.OBJECTVALUE2

NOT IN

(

SELECT I.OBJECTID1 ||I.OBJECTVALUE1 || I.OBJECTID2 || I.OBJECTVALUE2

 FROM PSPROJECTITEM I, PSPROJECTDEFN   d

WHERE I.PROJECTNAME = D.PROJECTNAME

AND D.LASTUPDOPRID =

and objecttype =58

)

and A.OBJECTID1 = 104

;    

 

 

 

--------------------------------------------------

-- Selects SQL Objects not inserted in a project

--------------------------------------------------

SELECT 

CASE WHEN SQLTYPE = 0 THEN 'SQL Object '

WHEN SQLTYPE = 1 THEN 'App Engine Step '

WHEN SQLTYPE = 2 THEN 'Record View'

WHEN SQLTYPE = 6 THEN 'Application Engine XSLT'

END AS OBJECTTYPE      ,

SQLID

FROM PSSQLDEFN

WHERE   LASTUPDOPRID =

AND  SQLID

NOT IN

(

SELECT OBJECTVALUE1

FROM PSPROJECTITEM I, PSPROJECTDEFN   d

WHERE I.PROJECTNAME = D.PROJECTNAME

AND D.LASTUPDOPRID =

AND OBJECTTYPE in (30)

);