PeopleSoft Security Script

 

The menu associated with each of the PeopleTools applications:

==============================================================

PeopleTools Application Menu 

Application designer APPLICATION_DESIGNER 

Data mover DATA_MOVER 

Definition Security OBJECT_SECURITY 

Query access QUERY 

Performance Monitor PPMI PERFMONPPMI 

To identify all permission lists (CLASSID) that provide access to the APPLICATION_DESIGNER menu using the following query:

========================================================================================================================

select distinct CLASSID from PSAUTHITEM where MENUNAME = 'APPLICATION_DESIGNER';

select CLASSID from PSAUTHITEM where MENUNAME='DATA_MOVER';

select CLASSID from PSAUTHITEM where MENUNAME='MAINTAIN_SECURITY';

To find the User associated with a particular Permission list

==============================================================

SQL> select OPRID,OPRCLASS from psoprcls where OPRCLASS='PTPT1200';

To find the Role associated with a permission list

====================================================

SQL> select * from PSROLECLASS where CLASSID='PTPT1200';

To find the User associated with a particular ROLE

======================================================

SQL> select * from PSROLEUSER where ROLENAME='PeopleTools';

To find the roll assigned to all the users

===========================================

select a.oprid User_ID,a.oprdefndesc User_Name,b.rolename,c.descr

from psoprdefn a,psroleuser b,PSROLEDEFN c

where a.oprid= b.roleuser

and b.rolename= c.rolename

and a.oprid not LIKE 'SP%'

finding permission lists with access, and identifying which users have those permission lists. 

=============================================================================================

select

    OC.OPRID,

    OC.OPRCLASS

from 

    PSOPRCLS OC

where 

    OPRCLASS = 'PSADMIN' or OPRCLASS in (

        select  CLASSID

        from    PSAUTHITEM        

        where   MENUNAME = 'APPLICATION_DESIGNER'    

        and     AUTHORIZEDACTIONS >= 1

    )

order by OC.OPRID, OPRCLASS

Note that this query takes into account the PeopleSoft Administrator role (permission list PSADMIN) as users with this role will also have access to PeopleTools.

Find out which users (and from what permission lists) have access to a particular page:

==========================================================================

select distinct OPRID, OPRCLASS

from PSOPRCLS

where OPRCLASS in (

    select distinct CLASSID

    from sysadm.PSAUTHITEM

    where PNLITEMNAME = '<PAGENAME>'

);