Auditing PeopleSoft Query Tree Access with SQL

When a security review flags sensitive PeopleSoft tables — such as JOB compensation rows or payroll accounting records — one of the first questions is whether end users can pull those records through PS Query.

Because PS Query authorizes tables through hierarchical Query Access Group Trees rather than component permissions, answering “who can actually query JOB?” in the PIA requires bouncing between Tree Manager, Permission Lists, Roles, and User Profiles. Worse, naive SQL against PSTREENODE produces a pile of false positives because delivered PeopleSoft databases ship dozens of QUERY_TREE_* definitions that no active user is ever granted.

Below is a four-step SQL progression (written for Oracle; swap SYSDATE for GETDATE() on SQL Server) that narrows raw tree definitions down to real, active user exposure.

How Query Tree Grants Map to Users

Before running the queries, it helps to see the exact join path from an active user to a record inside a Query Access Tree:

  flowchart LR
  O["PSOPRDEFN (ACCTLOCK = 0)"] --> UC["PS_USERCLASS_VW (OPRID, ROLENAME, CLASSID)"]
  UC --> AG["PS_SCRTY_ACC_GRP (ACCESSIBLE = 'Y')"]
  AG --> GN["PSTREENODE Group (TREE_NODE_TYPE = 'G')"]
  GN --> TN["PSTREENODE Record (TREE_NODE_TYPE = 'R')"]

Two PeopleTools details make these queries cleaner and much more accurate:

• PS_USERCLASS_VW saves a join: PeopleTools delivers USERCLASS_VW (PS_USERCLASS_VW), a view that joins PSROLEUSER_VW and PSROLECLASS into (OPRID, ROLENAME, CLASSID). Using it avoids hand-writing PSROLEUSER and PSROLECLASS joins every time.
• PS_SCRTY_ACC_GRP stores exclusions too: On a Permission List’s Query > Access Group Permissions tab, unchecking Accessible on a child group inserts a row with ACCESSIBLE = 'N' to carve out that branch from a broader parent grant. Any audit query against PS_SCRTY_ACC_GRP must check ACCESSIBLE = 'Y' (and account for excluded child branches), or revoked groups will show up as granted access.

Finding Query Trees That Contain Sensitive Records

Start by scanning active ACCESS_GROUP trees at their current effective date for record nodes (TREE_NODE_TYPE = 'R') matching your target pattern (here, %JOB%).

Joining PSTREEDEFN and filtering TREE_NODE_TYPE = 'R' avoids three sources of noise you get when querying PSTREENODE alone: superseded effective-dated tree rows, non-Query trees (such as department or financial rollup trees), and access group folder names like PRIMARY JOBS (TREE_NODE_TYPE = 'G').

SELECT DISTINCT
  TN.TREE_NAME,
  TN.PARENT_NODE_NAME AS PARENT_GROUP,
  TN.TREE_NODE AS RECNAME
FROM PSTREEDEFN TD
JOIN PSTREENODE TN
  ON TN.SETID = TD.SETID
 AND TN.SETCNTRLVALUE = TD.SETCNTRLVALUE
 AND TN.TREE_NAME = TD.TREE_NAME
 AND TN.EFFDT = TD.EFFDT
WHERE TD.TREE_STRCT_ID = 'ACCESS_GROUP'
  AND TD.SETID = ' '
  AND TD.SETCNTRLVALUE = ' '
  AND TD.EFF_STATUS = 'A'
  AND TD.EFFDT = (
    SELECT MAX(TD1.EFFDT)
    FROM PSTREEDEFN TD1
    WHERE TD1.SETID = TD.SETID
      AND TD1.SETCNTRLVALUE = TD.SETCNTRLVALUE
      AND TD1.TREE_NAME = TD.TREE_NAME
      AND TD1.EFFDT <= SYSDATE
  )
  AND TN.TREE_NODE_TYPE = 'R'
  AND TN.TREE_NODE LIKE '%JOB%'
ORDER BY TN.TREE_NAME, TN.TREE_NODE;

Filtering Down to Trees With Active User Access

In most environments, the first query returns delivered trees like QUERY_TREE_BAS or QUERY_TREE_BENEFIT that nobody can actually use. Before cloning or modifying a tree, check whether any unlocked user (O.ACCTLOCK = 0) is granted the branch containing that record.

Every branch node (GN) in PSTREENODE spans a nested-set node range from TREE_NODE_NUM to TREE_NODE_NUM_END. Checking TN.TREE_NODE_NUM BETWEEN GN.TREE_NODE_NUM AND GN.TREE_NODE_NUM_END works whether your permission lists grant the root node of the tree or a deeply nested sub-group — and the inner NOT EXISTS ensures a revoked child access group (ACCESSIBLE = 'N') is respected:

SELECT DISTINCT
  TN.TREE_NAME,
  GN.TREE_NODE AS GRANTED_ACCESS_GROUP,
  TN.TREE_NODE AS RECNAME
FROM PSTREEDEFN TD
JOIN PSTREENODE TN
  ON TN.SETID = TD.SETID
 AND TN.SETCNTRLVALUE = TD.SETCNTRLVALUE
 AND TN.TREE_NAME = TD.TREE_NAME
 AND TN.EFFDT = TD.EFFDT
JOIN PSTREENODE GN
  ON GN.SETID = TD.SETID
 AND GN.SETCNTRLVALUE = TD.SETCNTRLVALUE
 AND GN.TREE_NAME = TD.TREE_NAME
 AND GN.EFFDT = TD.EFFDT
 AND GN.TREE_NODE_TYPE = 'G'
 AND TN.TREE_NODE_NUM BETWEEN GN.TREE_NODE_NUM AND GN.TREE_NODE_NUM_END
WHERE TD.TREE_STRCT_ID = 'ACCESS_GROUP'
  AND TD.SETID = ' '
  AND TD.SETCNTRLVALUE = ' '
  AND TD.EFF_STATUS = 'A'
  AND TD.EFFDT = (
    SELECT MAX(TD1.EFFDT)
    FROM PSTREEDEFN TD1
    WHERE TD1.SETID = TD.SETID
      AND TD1.SETCNTRLVALUE = TD.SETCNTRLVALUE
      AND TD1.TREE_NAME = TD.TREE_NAME
      AND TD1.EFFDT <= SYSDATE
  )
  AND TN.TREE_NODE_TYPE = 'R'
  AND TN.TREE_NODE LIKE '%JOB%'
  AND EXISTS (
    SELECT 1
    FROM PS_SCRTY_ACC_GRP AG
    JOIN PS_USERCLASS_VW UC
      ON UC.CLASSID = AG.CLASSID
    JOIN PSOPRDEFN O
      ON O.OPRID = UC.OPRID
    WHERE AG.TREE_NAME = TD.TREE_NAME
      AND AG.ACCESS_GROUP = GN.TREE_NODE
      AND AG.ACCESSIBLE = 'Y'
      AND O.ACCTLOCK = 0
      AND NOT EXISTS (
        SELECT 1
        FROM PS_SCRTY_ACC_GRP EX_AG
        JOIN PSTREENODE EX_GN
          ON EX_GN.SETID = TD.SETID
         AND EX_GN.SETCNTRLVALUE = TD.SETCNTRLVALUE
         AND EX_GN.TREE_NAME = TD.TREE_NAME
         AND EX_GN.EFFDT = TD.EFFDT
         AND EX_GN.TREE_NODE = EX_AG.ACCESS_GROUP
         AND EX_GN.TREE_NODE_TYPE = 'G'
        WHERE EX_AG.CLASSID = AG.CLASSID
          AND EX_AG.TREE_NAME = AG.TREE_NAME
          AND EX_AG.ACCESSIBLE = 'N'
          AND EX_GN.TREE_NODE_NUM > GN.TREE_NODE_NUM
          AND TN.TREE_NODE_NUM BETWEEN EX_GN.TREE_NODE_NUM AND EX_GN.TREE_NODE_NUM_END
      )
  )
ORDER BY TN.TREE_NAME, GRANTED_ACCESS_GROUP, RECNAME;

Listing Unlocked Accounts With Query Tree Access

Once you know which trees and access groups matter, this query produces the user-level audit list showing every unlocked account, the role and permission list granting the access, and the target tree and access group. Add a filter like AND AG.TREE_NAME NOT LIKE 'QRY_%' if you want to spot users who still hold grants to delivered trees instead of your site’s custom trees:

SELECT DISTINCT
  AG.TREE_NAME,
  AG.ACCESS_GROUP,
  O.OPRID,
  O.OPRDEFNDESC,
  UC.ROLENAME,
  AG.CLASSID
FROM PS_SCRTY_ACC_GRP AG
JOIN PS_USERCLASS_VW UC
  ON UC.CLASSID = AG.CLASSID
JOIN PSOPRDEFN O
  ON O.OPRID = UC.OPRID
WHERE O.ACCTLOCK = 0
  AND AG.ACCESSIBLE = 'Y'
ORDER BY AG.TREE_NAME, AG.ACCESS_GROUP, O.OPRID, UC.ROLENAME;

Summarizing Active User Counts by Tree, Access Group, and Role

Before changing a role or pulling an access group off a permission list, aggregate by (TREE_NAME, ACCESS_GROUP, ROLENAME) to see the blast radius across unlocked users. Use COUNT(DISTINCT O.OPRID) rather than COUNT(1) — if two permission lists inside the same role both grant the same access group, COUNT(1) will double-count every user in that role:

SELECT
  AG.TREE_NAME,
  AG.ACCESS_GROUP,
  UC.ROLENAME,
  COUNT(DISTINCT O.OPRID) AS UNLOCKED_USER_COUNT
FROM PS_SCRTY_ACC_GRP AG
JOIN PS_USERCLASS_VW UC
  ON UC.CLASSID = AG.CLASSID
JOIN PSOPRDEFN O
  ON O.OPRID = UC.OPRID
WHERE O.ACCTLOCK = 0
  AND AG.ACCESSIBLE = 'Y'
GROUP BY AG.TREE_NAME, AG.ACCESS_GROUP, UC.ROLENAME
ORDER BY AG.TREE_NAME, AG.ACCESS_GROUP, UNLOCKED_USER_COUNT DESC;