Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL to resolve query access security issues with Search Definitions
-- 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'))));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.