Useful Security Queries

1) Users with a Particular Role:
Select ROLEUSER, ROLENAME FROM PSROLEUSER WHERE ROLENAME = 'DP_ALLPNLS' ORDER BY ROLEUSER

2) Users with a particular Permission list:
Select OPRID, OPRCLASS
FROM PSOPRCLS
WHERE OPRCLASS = 'EVERYONE'
ORDER BY OPRID

3) Users with a particular Primary permission list:
Select OPRID, OPRCLASS FROM PSOPRDEFN
WHERE OPRCLASS = 'EVERYONE'
ORDER BY OPRID

4) Users with a Particular Row security Permission List:
SELECT OPRID, ROWSECCLASS
FROM PSOPRDEFN
WHERE ROWSECCLASS = 'EVERYONE'
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 , PSROLEUSER B , PSOPRDEFN C
WHERE A.ROLENAME = B.ROLENAME
AND B.ROLEUSER = C.OPRID
AND C.ACCTLOCK = 0
AND A.CLASSID IN (SELECT DISTINCT CLASSID
FROM PSAUTHITEM
WHERE PNLITEMNAME = 'VCHR_SUMMARY_PG'
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 = 'VNDR_LOC')
AND A.ROLENAME = B.ROLENAME
ORDER BY A.ROLENAME

7) Permission Lists & Details with Access to a Particular page:
SELECT * From PSAUTHITEM
WHERE PNLITEMNAME =  'VNDR_LOC'

8) Roles Assigned to a particular User:
SELECT ROLEUSER, ROLENAME
FROM PSROLEUSER
WHERE ROLEUSER = 'nukas'
ORDER BY ROLENAME

10) Permission lists Assigned to a particular user:
SELECT OPRID, OPRCLASS
FROM PSOPRCLS
WHERE OPRID = 'nukas'
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 = 'nukas'
AND A.ROLENAME = B.ROLENAME

12) Permission lists assigned to a particular Role:
SELECT * FROM PSROLECLASS
WHERE ROLENAME = 'APDEVELOPER'
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 = 'nukas'
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.ROLENAME = 'APDEVELOPER'
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 PSAUTHITEM
WHERE CLASSID = 'AEPNLS'
ORDER BY MENUNAME, BARITEMNAME, PNLITEMNAME

16) All users with Access to Maintains Security. (Excluding change password and user selfservice):
SELECT B.ROLEUSER, C.OPRDEFNDESC, A.ROLENAME, A.CLASSID
FROM PSROLECLASS A, PSROLEUSER B, PSOPRDEFN C
WHERE B.ROLENAME = A.ROLENAME
AND B.ROLEUSER = C.OPRID
AND C.ACCTLOCK = 0
AND A.CLASSID IN
(SELECT DISTINCT CLASSID
FROM PSAUTHITEM
WHERE MENUNAME 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, PSOPRDEFN B
WHERE A.OPRID = B.OPRID
AND B.ACCTLOCK = 0
AND A.OPRCLASS IN
(SELECT DISTINCT CLASSID
FROM PSAUTHITEM
WHERE MENUNAME LIKE 'APPLICATION_DESIGNER%'
AND DISPLAYONLY = 0)