tag:blogger.com,1999:blog-34020393.post3717506682985169684..comments2024-03-27T10:59:38.307-07:00Comments on Jim's PeopleSoft Journal: Which Query Security Tree?Jim Marionhttp://www.blogger.com/profile/12995110203807924786noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-34020393.post-40247636869460653342022-07-26T05:03:49.127-07:002022-07-26T05:03:49.127-07:00@Unknown: when / how do you run the AE (PSQRYACCLI...@Unknown: when / how do you run the AE (PSQRYACCLIST ) that fills that PSQRYACCLSTRECS record? <br /><br />Kind regards,<br />ernst<br />Ernsthttps://www.blogger.com/profile/11163242620354669060noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-20379668031532485832022-07-22T04:57:24.333-07:002022-07-22T04:57:24.333-07:00This is one that I use all the time. It is a query...This is one that I use all the time. It is a query to show which records the user is missing in order to run a query(ies). It is my go to when a user cannot run a query.<br /><br />SELECT DISTINCT(Q.RECNAME), Q.QRYNAME FROM PSQRYRECORD Q WHERE Q.QRYNAME IN ('**RECORD_NAME**')<br />AND NOT EXISTS(SELECT 'X' FROM PSQRYACCLSTRECS A, PSUSERCLASSVW B<br />WHERE A.RECNAME = Q.RECNAME AND A.CLASSID = B.CLASSID<br />AND B.OPRID = UPPER('**OPRID**'));Anonymoushttps://www.blogger.com/profile/06591695727802436759noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-77247938562688122012022-07-22T04:56:23.934-07:002022-07-22T04:56:23.934-07:00we use this one to figure out the missing records ...we use this one to figure out the missing records for a user for a particular query they can't access.<br /><br />SELECT A.QRYNAME, A.RECNAME, A.OPRID, A.SELNUM, A.RCDNUM, A.JOINTYPE, A.JOINRCDNUM, A.JOINFLDNUM, A.CORRNAME<br /> FROM PSQRYRECORD A<br /> WHERE<br /> A.QRYNAME LIKE 'XXXXXXXXXX'<br /> AND NOT EXISTS (SELECT B.TREE_NODE<br /> FROM PSTREENODE B, PSTREEDEFN C<br /> WHERE B.SETID = C.SETID<br /> AND B.SETCNTRLVALUE = C.SETCNTRLVALUE<br /> AND B.TREE_NAME = C.TREE_NAME<br /> AND B.EFFDT = C.EFFDT<br /> AND B.TREE_NODE = A.RECNAME<br /> AND C.TREE_STRCT_ID = 'ACCESS_GROUP'<br /> AND B.EFFDT =<br /> (SELECT MAX(B_ED.EFFDT) FROM PSTREENODE B_ED<br /> WHERE B.SETID = B_ED.SETID<br /> AND B.SETCNTRLVALUE = B_ED.SETCNTRLVALUE<br /> AND B.TREE_NAME = B_ED.TREE_NAME)<br /> AND B.TREE_NAME IN (SELECT F.TREE_NAME <br />FROM PSOPRDEFN A <br />, PSROLECLASS B <br />, PSROLEUSER C <br />, PSAUTHITEM D <br />, PSPGEACCESSDESC E <br />, PS_SCRTY_ACC_GRP F <br />WHERE B.ROLENAME = C.ROLENAME <br />AND A.OPRID = C.ROLEUSER <br />AND B.CLASSID = D.CLASSID <br />AND B.CLASSID = F.CLASSID <br />AND D.AUTHORIZEDACTIONS = E.AUTHORIZEDACTIONS <br />AND A.OPRID = 'XXXXXXXXXX'));<br />Ernsthttps://www.blogger.com/profile/11163242620354669060noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-86483325814851077472022-07-21T14:48:21.609-07:002022-07-21T14:48:21.609-07:00Thanks for the post.
I have a little tool that I ...Thanks for the post.<br /><br />I have a little tool that I use a lot to find records in query where the user does not have access<br /><br /><br />-- query-record-user access<br />-- if you know a query name and a user name and want to find records that they don't have access to <br />select * from psqryrecord x<br />where qryname = '&qryname'<br />and X.RECNAME NOT IN (select C.TREE_NODE from PS_SCRTY_ACC_GRP A, PSROLECLASS B, PSTREENODE C<br />WHERE c.tree_node = x.recname<br />and c.effdt = (select max(c1.effdt) from pstreenode c1<br /> where c1.setid = c.setid and c1.setcntrlvalue = c.setcntrlvalue and c1.tree_name = c.tree_name and c1.effdt <= sysdate) <br />and (A.CLASSID, B.ROLENAME) IN (SELECT X.OPRCLASS, Z.ROLENAME FROM PSOPRCLS X, PS_ROLEXLATOPR Y, PSROLEUSER Z<br /> WHERE X.OPRID='&USERID'<br /> AND Y.OPRID = X.OPRID<br /> AND Z.ROLEUSER = Y.ROLEUSER)<br />AND A.ACCESSIBLE='Y'<br />AND A.CLASSID = B.CLASSID<br />AND C.TREE_NAME = A.TREE_NAME<br />AND C.TREE_NODE_NUM BETWEEN (SELECT U.TREE_NODE_NUM FROM PSTREENODE U<br /> WHERE U.SETID = C.SETID<br /> AND U.TREE_NAME = C.TREE_NAME<br /> AND U.TREE_NODE = A.ACCESS_GROUP<br /> and u.effdt = (select max(u1.effdt) from pstreenode u1 <br /> where u1.setid = u.setid and u1.setcntrlvalue = u.setcntrlvalue and u1.tree_name = u.tree_name and u1.effdt <= sysdate))<br /> AND (SELECT U.TREE_NODE_NUM_END FROM PSTREENODE U<br /> WHERE U.SETID = C.SETID<br /> AND U.TREE_NAME = C.TREE_NAME<br /> AND U.TREE_NODE = A.ACCESS_GROUP<br /> and u.effdt = (select max(u1.effdt) from pstreenode u1 <br /> where u1.setid = u.setid and u1.setcntrlvalue = u.setcntrlvalue and u1.tree_name = u.tree_name and u1.effdt <= sysdate))<br /> ) <br />/<br />Fran Williamsonhttps://www.blogger.com/profile/13161235192843050497noreply@blogger.com