domingo, 10 de abril de 2016

Managing Query Security


Have you ever tried to look for an existing Query but you aren't able to see it through the PIA?




However, If you search it in the Database, it does exist and it's a Public Query ( there is no OPRID save in the record)

SELECT *
FROM PSQRYDEFN
WHERE QRYNAME IN ('SANI_USER_RPT_QRY');


The issue resides in the security that is applied to the Query Object. We have no access to any of the tables that are involved in the query, so we can't see it.

Let's see which records are included in the Query:
SELECT DISTINCT RECNAME
from PSQRYRECORD
WHERE QRYNAME IN ('SANI_USER_RPT_QRY');


Based on this, we have 2 ways of solving granting users with access to the Query:
First Option: 
Let's see in which Access Groups are the tables saved:
SELECT TREE_NAME, PARENT_NODE_NAME, TREE_NODE
FROM PSTREENODE
WHERE TREE_NODE_TYPE ='R' 
AND TREE_NODE IN('PSOPRDEFN','PSROLEDEFN','PSROLEUSER','PSXLATITEM','SANI_EMPL_TBL','SANI_RPT_USER')

Now let's check which of them are not in any user Permission List, (in this case I am logged with user VP2 )

SELECT TREE_NAME, PARENT_NODE_NAME, TREE_NODE

FROM PSTREENODE
WHERE TREE_NODE_TYPE ='R' 
AND TREE_NODE IN('PSOPRDEFN','PSROLEDEFN','PSROLEUSER','PSXLATITEM','SANI_EMPL_TBL','SANI_RPT_USER')
AND (TREE_NAME, PARENT_NODE_NAME) NOT IN (SELECT TREE_NAME, PARENT_NODE_NAME
                                          FROM PS_SCRTY_ACC_GRP 
                                          WHERE CLASSID IN( select B.CLASSID
                                          from PSROLEUSER A, PSROLECLASS B
                                          where A.ROLENAME = B.ROLENAME
                                          and A.ROLEUSER= 'VP2'))
So I should add this Access Group to any of the VP2 permission lists, so that this user can see the query. Let's add it to the ALLPAGES permission list:




Save the Permission List, and that's it.

Second Option: Let's look for a Query Access Tree. It will depends on the tables that are being used in the Query. In this case I'll add it to the QUERY_TREE_PT, since the involved records are PeopleTools records.


Once there, let's create a new Access Group:




And now let's add all the involved tables to the Access Group:



Save the Query Access Tree:



Now I should add the access group to the ALLPAGES permission list:



Save the changes, and that's it.

No matter what option have you chosen, you should now have access to see the Query.
I'll go and search once again, now the query is properly shown:



Just a Hint
There is no need to add ALL the query tables to the Query Tree, but only those ones that User has no access through another Acess Group.
Here you have a query that could help you checking which records are not able to be seen by the user:
SELECT Q.RECNAME
FROM PSQRYRECORD Q
WHERE Q.QRYNAME = 'SANI_INVOICE_HDR_QRY'
AND Q.RECNAME NOT IN (SELECT T.TREE_NODE
                        FROM PSTREENODE T
                      WHERE T.TREE_NODE_TYPE ='R' 
                      AND (T.TREE_NAME, T.PARENT_NODE_NAME) IN (SELECT AG.TREE_NAME, AG.ACCESS_GROUP
                                                                FROM PS_SCRTY_ACC_GRP AG 
                                                                WHERE AG.CLASSID IN( SELECT B.CLASSID
                                                                                FROM PSROLEUSER A, PSROLECLASS B
                                                                                WHERE A.ROLENAME = B.ROLENAME
                                                                                AND A.ROLEUSER= 'ihernandezn')
                                                            )
                  )

In this case, it was only the SANI_RPT_USER which was hidden for VP2 user.

And that's all.

I hope this blog could be so useful to you as it is to me.

Regards.
Facundo Salerno.