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)

Posted by

Ganesh

 

0 comments: