SQL to resolve query access security issues with Search Definitions

SearchDefnSecurity.sql

-- Shows records in a Search Definition
select recname from PSQRYRECORD where qryname in (
    select qrynamechild from PSCONQRSMAP where CONQRSNAME in (
    select PTSF_SOURCE_NAME from psptsf_sd where PTSF_SBO_NAME = 'SEARCH_DEFINITION_NAME'));

-- Shows query access groups a user as permission to access
select tree_name, access_group from ps_scrty_acc_grp where tree_name like 'SEARCH%' and classid in (
    select classid from psroleclass where rolename in (
    select rolename from psroleuser where roleuser = 'USERNAME'));

select tree_node from PSTREENODE where tree_name like 'SEARCH%'and tree_node in (
    select access_group from ps_scrty_acc_grp where tree_name like 'SEARCH%' and classid in (
    select classid from psroleclass where rolename in (
    select rolename from psroleuser where roleuser = 'USERNAME')));


-- Show records in a search definition that are not in a SEARCH% Query Tree 
-- If records are displayed here, add them to a SEARCH% Query Tree
select recname from PSQRYRECORD where qryname in (
    select qrynamechild from PSCONQRSMAP where CONQRSNAME in (
    select PTSF_SOURCE_NAME from psptsf_sd where PTSF_SBO_NAME = 'SEARCH_DEFINITION_NAME')) 
  and recname not in (
    select tree_node from PSTREENODE where tree_name like 'SEARCH%');
    

-- Show records that a user does not have access to for a Search Definition
-- If records are displayed here, but not in the query above, 
--   check the PLs assigned to the user to grant access
select recname from PSQRYRECORD where qryname in (
    select qrynamechild from PSCONQRSMAP where CONQRSNAME in (
    select PTSF_SOURCE_NAME from psptsf_sd where PTSF_SBO_NAME = 'SEARCH_DEFINITION_NAME')) 
  and recname not in (
    select tree_node from PSTREENODE where tree_name like 'SEARCH%'and tree_node in (
    select access_group from ps_scrty_acc_grp where tree_name like 'SEARCH%' and classid in (
    select classid from psroleclass where rolename in (
    select rolename from psroleuser where roleuser = 'USERNAME'))));