Query Tree Security SQL

Query Trees are assigned to a permission list in PeopleSoft under the Query tab of: -

PeopleTools > Security > Permissions and Roles > Permission Lists

Working out who's got access to what query trees can be a bit of a nightmare manually searching through each permission list in turn. To that end, the following piece of SQL is quite useful in establishing which roles have access to a particular query tree or trees.

select distinct ROL.ROLENAME,
ROL.CLASSID,
SAG.TREE_NAME
from PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG
where ROL.CLASSID = SAG.CLASSID
and SAG.TREE_NAME in (Query Tree List)

To see which query trees a particular user has access to, use: -

select distinct USR.ROLENAME,
ROL.CLASSID,
SAG.TREE_NAME
from PSROLEUSER_VW USR,
PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG
where USR.OPRID = 'Operator ID'
and USR.ROLENAME = ROL.ROLENAME
and ROL.CLASSID = SAG.CLASSID

To see which users have access to a particular query tree: -

select distinct ROL.ROLENAME,
OPR.OPRDEFNDESC
from PSROLECLASS ROL,
PS_SCRTY_ACC_GRP SAG,
PSROLEUSER_VW USR,
PSOPRDEFN OPR
where ROL.CLASSID = SAG.CLASSID
and SAG.TREE_NAME = 'QRY_TMUK_ALL'
and ROL.ROLENAME = USR.ROLENAME
and USR.OPRID = OPR.OPRID
order by ROLENAME, OPR.OPRDEFNDESC