SET SCHEMA PSUSER
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 self service.
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)
18) PS record is associated with which Query
security tree
SELECT
A.TREE_NAME,
A.DESCR,
B.PARENT_NODE_NAME,
B.TREE_NODE,
B.TREE_NODE_TYPE,
C.CLASSID,
B.TREE_NODE_NUM,
B.PARENT_NODE_NUM,
C.ACCESS_GROUP,
C.ACCESSIBLE
FROM
PSTREEDEFN A,
PSTREENODE
B,
PS_SCRTY_ACC_GRP
C
WHERE
A.SETID = ' '
AND
A.TREE_STRCT_ID = 'ACCESS_GROUP'
AND
A.SETID = B.SETID
AND
A.SETCNTRLVALUE = B.SETCNTRLVALUE
AND
A.TREE_NAME = B.TREE_NAME
AND
B.TREE_NODE = 'AR_ACCESS_GROUP'
AND
B.TREE_NAME = C.TREE_NAME
ORDER
BY 7, 8, 6
19) Roles
attached to a component (portal_uri_seg2) or content reference (portal_objname)
SELECT DISTINCT
C.PORTAL_NAME,
C.PORTAL_OBJNAME,
B.ROLENAME,
E.DESCR
FROM PSROLECLASS B, PSPRSMPERM C, PSPRSMDEFN D,
PSROLEDEFN E
WHERE C.PORTAL_OBJNAME = D.PORTAL_OBJNAME
AND B.CLASSID = C.PORTAL_PERMNAME
AND B.ROLENAME = E.ROLENAME
AND (D.PORTAL_URI_SEG2 = 'ACL_PURGE_USER_RUN' OR
D.PORTAL_OBJNAME = 'ACL_PURGE_PW_RUN_GBL');
20) Permissions
attached to a component (portal_uri_seg2) or content reference (portal_objname)
SELECT DISTINCT
D.PORTAL_NAME,
D.PORTAL_OBJNAME,
C.PORTAL_PERMNAME,
E.CLASSDEFNDESC
FROM PSPRSMPERM C , PSPRSMDEFN D , PSCLASSDEFN E
WHERE D.PORTAL_OBJNAME = C.PORTAL_OBJNAME
AND
D.PORTAL_NAME = C.PORTAL_NAME
AND C.PORTAL_PERMNAME = E.CLASSID
AND (D.PORTAL_URI_SEG2 = 'ACL_PURGE_USER_RUN' OR
D.PORTAL_OBJNAME = 'ACL_PURGE_PW_RUN_GBL');
21) Users
that can access a component (portal_uri_seg2) or content reference
(portal_objname)
SELECT
DISTINCT
C.PORTAL_NAME,
C.PORTAL_OBJNAME,
A.ROLEUSER,
E.OPRDEFNDESC
FROM
PSROLEUSER A,
PSROLECLASS
B,
PSPRSMPERM
C,
PSPRSMDEFN
D,
PSOPRDEFN
E
WHERE
D.PORTAL_OBJNAME = C.PORTAL_OBJNAME
AND
A.ROLENAME= B.ROLENAME
AND
B.CLASSID = C.PORTAL_PERMNAME
AND
A.ROLEUSER = E.OPRID
AND
(D.PORTAL_URI_SEG2 = 'ACL_PURGE_USER_RUN' OR D.PORTAL_OBJNAME =
'ACL_PURGE_PW_RUN_GBL');
22) Finding Navigation Path by component or page
name (component (portal_uri_seg2))
SELECT
RTRIM(E.PORTAL_LABEL)
||
' >> ' || RTRIM(D.PORTAL_LABEL)
||
' >> ' || RTRIM(C.PORTAL_LABEL)
||
' >> ' || RTRIM(B.PORTAL_LABEL)
||
' >> ' || RTRIM(A.PORTAL_LABEL)
FROM
PSPRSMDEFN A LEFT JOIN PSPRSMDEFN B ON B.PORTAL_NAME = A.PORTAL_NAME
AND
B.PORTAL_OBJNAME = A.PORTAL_PRNTOBJNAME LEFT JOIN PSPRSMDEFN C
ON
C.PORTAL_NAME = B.PORTAL_NAME AND C.PORTAL_OBJNAME = B.PORTAL_PRNTOBJNAME LEFT
JOIN PSPRSMDEFN D
ON
D.PORTAL_NAME = C.PORTAL_NAME AND D.PORTAL_OBJNAME = C.PORTAL_PRNTOBJNAME LEFT
JOIN PSPRSMDEFN E
ON
E.PORTAL_NAME = D.PORTAL_NAME AND E.PORTAL_OBJNAME = D.PORTAL_PRNTOBJNAME WHERE
A.PORTAL_URI_SEG2
= 'ACL_PURGE_USER_RUN'
23) Users that can access a to Access group,
tree name and tree node
SELECT DISTINCT C.ROLEUSER, C.ROLENAME,
B.CLASSID, A.TREE_NAME, A.ACCESS_GROUP, A.ACCESSIBLE, D.TREE_NODE
FROM PS_SCRTY_ACC_GRP A, PSROLECLASS B, PSROLEUSER C, PSTREENODE D
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND C.ROLEUSER = ‘nukas’
AND A.TREE_NAME = D.TREE_NAME
AND D.EFFDT (SELECT MAX(D_ED.EFFDT) FROM PSTREENODE D_ED
WHERE D.SETID = D_ED.SETID
AND D.SETCNTRLVALUE = D_ED.SETCNTRLVALUE
AND D.TREE_NAME = D_ED.TREE_NAME
AND D_ED.EFFDT <= SYSDATE)
FROM PS_SCRTY_ACC_GRP A, PSROLECLASS B, PSROLEUSER C, PSTREENODE D
WHERE A.CLASSID = B.CLASSID
AND B.ROLENAME = C.ROLENAME
AND C.ROLEUSER = ‘nukas’
AND A.TREE_NAME = D.TREE_NAME
AND D.EFFDT (SELECT MAX(D_ED.EFFDT) FROM PSTREENODE D_ED
WHERE D.SETID = D_ED.SETID
AND D.SETCNTRLVALUE = D_ED.SETCNTRLVALUE
AND D.TREE_NAME = D_ED.TREE_NAME
AND D_ED.EFFDT <= SYSDATE)
