Here is the scenario:
A user needs to create a query, but doesn't have security access to view a record. The user asks you for help.
Before running or writing queries, a user must have access to records through Query Security Trees. We assign records to trees, trees to permission lists, permission lists to roles, and roles to users. The solution is trivial: grant security access through a security tree. The difficult part is deciding which tree and which permission list. When I receive a request like this, I have two groups of questions:
- Is there already a query security tree that grants access to the target record? If so, which one and which permission lists? Would any of those permission lists be appropriate for this user?
- Is there already a query security tree available to this user that would be a good fit for this record?
Here are some SQL statements I created to help answer these questions.
Find existing trees that include the target record:
Finding Permission Lists that grant access to a specific record is more challenging. Locating permission lists that contain a tree that contains a record is trivial. Narrowing that list based on Access Group is more challenging because Access Groups represent node hierarchies and therefore require recursive logic:
Do you have SQL statements and tricks to help you manage Query Security? If so, share them in the comments!
At JSMpros, we teach PeopleTools tips like this every week. Check out our website to see what we are offering next!
4 comments:
Thanks for the post.
I have a little tool that I use a lot to find records in query where the user does not have access
-- query-record-user access
-- if you know a query name and a user name and want to find records that they don't have access to
select * from psqryrecord x
where qryname = '&qryname'
and X.RECNAME NOT IN (select C.TREE_NODE from PS_SCRTY_ACC_GRP A, PSROLECLASS B, PSTREENODE C
WHERE c.tree_node = x.recname
and c.effdt = (select max(c1.effdt) from pstreenode c1
where c1.setid = c.setid and c1.setcntrlvalue = c.setcntrlvalue and c1.tree_name = c.tree_name and c1.effdt <= sysdate)
and (A.CLASSID, B.ROLENAME) IN (SELECT X.OPRCLASS, Z.ROLENAME FROM PSOPRCLS X, PS_ROLEXLATOPR Y, PSROLEUSER Z
WHERE X.OPRID='&USERID'
AND Y.OPRID = X.OPRID
AND Z.ROLEUSER = Y.ROLEUSER)
AND A.ACCESSIBLE='Y'
AND A.CLASSID = B.CLASSID
AND C.TREE_NAME = A.TREE_NAME
AND C.TREE_NODE_NUM BETWEEN (SELECT U.TREE_NODE_NUM FROM PSTREENODE U
WHERE U.SETID = C.SETID
AND U.TREE_NAME = C.TREE_NAME
AND U.TREE_NODE = A.ACCESS_GROUP
and u.effdt = (select max(u1.effdt) from pstreenode u1
where u1.setid = u.setid and u1.setcntrlvalue = u.setcntrlvalue and u1.tree_name = u.tree_name and u1.effdt <= sysdate))
AND (SELECT U.TREE_NODE_NUM_END FROM PSTREENODE U
WHERE U.SETID = C.SETID
AND U.TREE_NAME = C.TREE_NAME
AND U.TREE_NODE = A.ACCESS_GROUP
and u.effdt = (select max(u1.effdt) from pstreenode u1
where u1.setid = u.setid and u1.setcntrlvalue = u.setcntrlvalue and u1.tree_name = u.tree_name and u1.effdt <= sysdate))
)
/
we use this one to figure out the missing records for a user for a particular query they can't access.
SELECT A.QRYNAME, A.RECNAME, A.OPRID, A.SELNUM, A.RCDNUM, A.JOINTYPE, A.JOINRCDNUM, A.JOINFLDNUM, A.CORRNAME
FROM PSQRYRECORD A
WHERE
A.QRYNAME LIKE 'XXXXXXXXXX'
AND NOT EXISTS (SELECT B.TREE_NODE
FROM PSTREENODE B, PSTREEDEFN C
WHERE B.SETID = C.SETID
AND B.SETCNTRLVALUE = C.SETCNTRLVALUE
AND B.TREE_NAME = C.TREE_NAME
AND B.EFFDT = C.EFFDT
AND B.TREE_NODE = A.RECNAME
AND C.TREE_STRCT_ID = 'ACCESS_GROUP'
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PSTREENODE B_ED
WHERE B.SETID = B_ED.SETID
AND B.SETCNTRLVALUE = B_ED.SETCNTRLVALUE
AND B.TREE_NAME = B_ED.TREE_NAME)
AND B.TREE_NAME IN (SELECT F.TREE_NAME
FROM PSOPRDEFN A
, PSROLECLASS B
, PSROLEUSER C
, PSAUTHITEM D
, PSPGEACCESSDESC E
, PS_SCRTY_ACC_GRP F
WHERE B.ROLENAME = C.ROLENAME
AND A.OPRID = C.ROLEUSER
AND B.CLASSID = D.CLASSID
AND B.CLASSID = F.CLASSID
AND D.AUTHORIZEDACTIONS = E.AUTHORIZEDACTIONS
AND A.OPRID = 'XXXXXXXXXX'));
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.
SELECT DISTINCT(Q.RECNAME), Q.QRYNAME FROM PSQRYRECORD Q WHERE Q.QRYNAME IN ('**RECORD_NAME**')
AND NOT EXISTS(SELECT 'X' FROM PSQRYACCLSTRECS A, PSUSERCLASSVW B
WHERE A.RECNAME = Q.RECNAME AND A.CLASSID = B.CLASSID
AND B.OPRID = UPPER('**OPRID**'));
@Unknown: when / how do you run the AE (PSQRYACCLIST ) that fills that PSQRYACCLSTRECS record?
Kind regards,
ernst
Post a Comment