Peoplesoft Security Queries
Useful Security Queries in Peoplesoft.
1) Users with a Particular Role
Select ROLEUSER, ROLENAME
FROM PSROLEUSER
WHERE ROLENAME = ‘ROLE1’
ORDER BY ROLEUSER
2) Users with a particular Permission list
Select OPRID, OPRCLASS
FROM PSOPRCLS
WHERE OPRCLASS = ‘PERM1’
ORDER BY OPRID
3) Users with a particular Primary permission list
Select ORPID, OPRCLASS
From PSOPRDEFN
WHERE OPRCLASS = ‘PERM1’
ORDER BY OPRID
4) Users with a Particular Row security Permission List
SELECT OPRID, ROWSECCLASS
FROM PSOPRDEFN
WHERE ROWSECCLASS = ‘PERM1’
ORDER BY OPRID
5) Users, Role & Class with Access to a particular page
Select B.ROLEUSER,C.OPRDEFNDESC, A.ROLENAME, A.CLASSID
FROM PSROLECLASS = A.ROLENAME
AND B.ROLEUSER = C.OPRID
AND C.ACCTLOCK = 0
AND A.CLASSID IN (SELECT DISTINCT CLASSID
FROM PSAUTHITEM
WHERE PNLITEMNAME = ‘Page1’
AND DISPLAYONLY = 0)
ORDER BY B.ROLEUSER
6) Roles & Class with Access to a particular page
SELECT A.ROLENAME, A.CLASSID
FROM PSROLECLASS A, PSROLEDEFN B
WHERE A.CLASSID IN
(SELECT DISTINCT CLASSID
FROM PSAUTHITEM
WHERE PNLITEMNAME = ‘Page1’
AND A.ROLENAME = B.ROLENAME
ORDER BY A.ROLENAME
7) Permission Lists & Details with Access to a Particular page
SELECT * From PSAUTHITEM
WHERE PNLITEMNAME = ‘Page1’
8) Roles Assigned to a particular User
SELECT ROLEUSER, ROLENAME
FROM PSROLEUSER
WHERE ROLEUSER = ‘USER1’
ORDER BY ROLENAME
9) Permission Lists assigned to particular User
SELECT ROLEUSER, ROLENAME
FROM PSROLEUSER
WHERE ROLEUSER = ‘USER1’
ORDER BY ROLENAME
10) Permission lists Assigned to a particular user
SELECT OPRID, OPRCLASS
FROM PSOPRCLS
WHERE OPRID = ‘USER1’
ORDER BY OPRCLASS
11) Roles & Permission lists Assigned to a Particular User
SELECT A.ROLEUSER, A.ROLENAME, B.CLASSID
FROM PSROLEUSER A, PSROLECLASS B
WHERE A.ROLEUSER = ‘USER1’
AND A.ROLENAME = B.ROLENAME
12) Permission lists assigned to a particular Role
SELECT * FROM PSROLECLASS
WHERE ROLENAME = ‘ROLE1’
ORDER BY CLASSID
13) All Pages Accessible by a user
SELECT A.ROLEUSER, A.ROLENAME , B.CLASSID
C.MENUNAME, C.BARNAME, C.BARITEMNAME,
C.PNLITEMNAME, C.DISPLAYONLY, C.AUTHORIZEDACTIONS
FROM PSROLEUSER A, PSROLECLASS B, PSAUTHITEM C
WHERE A.ROLEUSER = ‘USER1’
AND B.CLASSID = C.CLASSID
ORDER BY A.ROLENAME, B. CLASSID,
C.MENUNAME, C.BARITEMNAME, C.PNLITEMNAME
14) All Pages Accessible by a Role
SELECT DISTINCT A.ROLENAME, B.CLASSID,
C.MENUNAME, C.BARNAME, C.BARITEMNAME,
C.PNLITEMNAME, C.DISPLAYONLY, C.AUTHORIZEDACTIONS
FROM PSROLEUSER A, PSROLECLASS B, PSAUTHITEM C
WHERE A.ROLEANME = ‘ROLE1’
AND B.CLASSID = C.CLASSID
ORDER BY B.CLASSID,
C.MENUNAME, C.BARITEMNAME, C.PNLITEMNAME
15) All Pages Accessible by a Permission list
SELECT * FROM PSAUHITEM
WHERE CLASSID = ‘PERM1’
ORDER BY MENUITEM, BARITEMNAME, PNLITEMNAME
16) All users with Access to Maintains Security. (Excluding change password and user self service).
SELECT B.ROLEUSER, C.OPERDEFNDESC, A.ROLENAME, A.CLASSID
FROM PSROLECLASS A, PSROLEUSER B, PSOPRDEFN C
WHERE B.ROLENAME = A.ROLENAME
AND B.ROLEUSER = C.OPRID
AND B.ACCTLOCK = 0
AND A.CLASSID IN
(SELET DISTINCT CLASSID
FROM PSAUITEM
WHERE MENUITEM LIKE ‘MAINTAIN_SECURITY%’ AND PNLITEMNAME NOT IN
(‘CHANGE_PASSWORD’, ‘USER_SELF_SERVICE’)
AND DISPLAYONLY = 0)
ORDER BY ROLENAME, ROLEUSER
17) All Users with Access to Application Designer
SELECT DISTINCT A.OPRID, B.OPRDEFNDESC
FROM PSOPRCLS A, PSORDEFN B
WHERE A.OPRID = B.OPRID
AND B.ACCTLOCK = 0
AND A.OPRCLASS IN
(SELECT DISTINCT CLASSID
FROM PSAUTHIEM
WHERE MENUNAME LIKE ‘APPLICATION_DESIGNER%’
AND DISPLAYONLY = 0)
Ganesh
Posts
