User Security Queries for PeopleSoft

Post Level         : Advanced/Good to Know

Target Readers : Beginners to Experts

1. Users for a particular component.

Select distinct UR.ROLEUSER, PC.MENUNAME, PC.BARNAME,
PC.BARITEMNAME, MI.PNLGRPNAME, PC.PNLITEMNAME
from PSROLEUSER UR, PSROLECLASS RP, PSAUTHITEM PC,
PSMENUITEM MI
where UR.ROLENAME = RP.ROLENAME
and RP.CLASSID = PC.CLASSID
and PC.MENUNAME = MI.MENUNAME
and PC.BARNAME = MI.BARNAME
and PC.BARITEMNAME = MI.ITEMNAME 
and MI.PNLGRPNAME = '<Component Name>'
order by UR.ROLEUSER, PC.MENUNAME, PC.BARNAME,
PC.BARITEMNAME, PC.PNLITEMNAME;

Sample Output:

2. Roles for a particular component.

Select distinct RP.ROLENAME, PC.MENUNAME, PC.BARNAME,
PC.BARITEMNAME, MI.PNLGRPNAME, PC.PNLITEMNAME
from PSROLECLASS RP, PSAUTHITEM PC, PSMENUITEM MI
where RP.CLASSID = PC.CLASSID
and PC.MENUNAME = MI.MENUNAME
and PC.BARNAME = MI.BARNAME
and PC.BARITEMNAME = MI.ITEMNAME
and MI.PNLGRPNAME = '<Component Name>'
order by RP.ROLENAME, PC.MENUNAME, PC.BARNAME,
PC.BARITEMNAME, PC.PNLITEMNAME;

Sample Output:

3. Permission Lists for a particular component.

Select distinct PC.CLASSID, PC.MENUNAME, PC.BARNAME,
PC.BARITEMNAME, MI.PNLGRPNAME, PC.PNLITEMNAME
from PSAUTHITEM PC, PSMENUITEM MI
where PC.MENUNAME = MI.MENUNAME
and PC.BARNAME = MI.BARNAME
and PC.BARITEMNAME = MI.ITEMNAME
and MI.PNLGRPNAME = '<Component Name>'
order by PC.CLASSID, PC.MENUNAME, PC.BARNAME,
PC.BARITEMNAME, PC.PNLITEMNAME;

Sample Output:

4. Components for a particular permission list.

Select distinct PC.MENUNAME, PC.BARNAME,
PC.BARITEMNAME, MI.PNLGRPNAME, PC.PNLITEMNAME,
PC.CLASSID
from PSAUTHITEM PC, PSMENUITEM MI
where PC.MENUNAME = MI.MENUNAME
and PC.BARNAME = MI.BARNAME
and PC.BARITEMNAME = MI.ITEMNAME
and PC.CLASSID = '<Permission List>'
order by PC.MENUNAME, PC.BARNAME, PC.BARITEMNAME,
PC.PNLITEMNAME, PC.CLASSID;


Sample Output:

5. Components for a particular role.

Select distinct PC.MENUNAME, PC.BARNAME,
PC.BARITEMNAME, MI.PNLGRPNAME, PC.PNLITEMNAME,
RP.ROLENAME
from PSROLECLASS RP, PSAUTHITEM PC, PSMENUITEM MI
where RP.CLASSID = PC.CLASSID
and PC.MENUNAME = MI.MENUNAME
and PC.BARNAME = MI.BARNAME
and PC.BARITEMNAME = MI.ITEMNAME
and RP.ROLENAME = '<Role Name>'
order by PC.MENUNAME, PC.BARNAME, PC.BARITEMNAME,
PC.PNLITEMNAME, RP.ROLENAME;


Sample Output:

6. Component for a particular user.

Select distinct PC.MENUNAME, PC.BARNAME,
PC.BARITEMNAME, MI.PNLGRPNAME, PC.PNLITEMNAME,
UR.ROLEUSER
from PSROLEUSER UR, PSROLECLASS RP, PSAUTHITEM PC,
PSMENUITEM MI
where UR.ROLENAME = RP.ROLENAME
and RP.CLASSID = PC.CLASSID
and PC.MENUNAME = MI.MENUNAME
and PC.BARNAME = MI.BARNAME
and PC.BARITEMNAME = MI.ITEMNAME
and UR.ROLEUSER = '<Role User>'
order by PC.MENUNAME, PC.BARNAME, PC.BARITEMNAME,
PC.PNLITEMNAME, UR.ROLEUSER;


Sample Output:

Please leave your helpful comments...