Project Senty issue affecting the ticket details screen in Customer Service

Hi All,

I have created a sentry plugin to prevent users from being able to see projects in SuperOffice unless they are a project member or project manager, and it works exactly as I need it to in SuperOffice.  It does however cause a bug in Customer Service, where if there is a project relationship set up against the request table, if the request does not have a project, or the user does not have access to the project because of the rules in the sentry plugin, none of the request details show.

The error only seems to occur, if you add restrictions to the ModifySelect, to prevent the project row being returned if the user should not see the project.  If you always allow the row to be returned, and then use the ModifyTableRights function to restrict access to the project, then you do not get this issue.  For the query I need to perform I don't believe I can achieve this using the second method.

I basically use the ModifySelect option to to filter out the rows, so that if the project members person id is not equal the SuperOffice.SoContext.CurrentPrincipal.PersonId and the project managers associate id is not equal to SuperOffice.SoContext.CurrentPrincipal.AssociateId no rows are returned.  I then do not add any code in the ModifyTableRights procedure, as it is already handled.  Looking at the code example here, this looks like a valid way of achieving this. https://community.superoffice.com/en/content/content/netserver-sdk/netserver-3x/sentry-plugin-with-user-defined-tables/

I can even simply change the ModifySelect query to just prevent access to opening any project using this ...

        [SentryPluginQueryTableUpdater("project")]
        public class SentryPluginQueryTableUpdaterContact : ISentryPluginQueryTableUpdater
        {
            public void ModifySelect(Select sql, TableInfo tableInfo)
            {
                ProjectTableInfo pti = (ProjectTableInfo)tableInfo;
                sql.RestrictionAnd(pti.ProjectId.Equal(S.Parameter(-1)));;
            }
        }

And the sentry works in SuperOffice, but causes the error in Customer Service.  SO I think this is a bug in Sentry.  I have tested this on 8.3 R02.

Many thanks,

Trevor

 

RE: Project Senty issue affecting the ticket details screen in Customer Service

Bug 60009

Av: Christian Mogensen 30. jul 2018

RE: Project Senty issue affecting the ticket details screen in Customer Service

Thanks Christian.

Av: Trevor Sharp 30. jul 2018

RE: Project Senty issue affecting the ticket details screen in Customer Service

Try fixing the sentry plugin logic to consider whether the project table is inner or outer joined:

 

        public void ModifySelect(Select sql, TableInfo tableInfo)
        {
                if( sql.IsTableOuterJoined(tableInfo) )
                {
                    var rels = sql.GetRelations(tableInfo);
                    var outerJoin = rels.FirstOrDefault();
                    if( outerJoin != null )
                        outerJoin.OriginalJoin.RestrictionAnd( ... );
                }
                else
                {
                    sql.RestrictionAnd(...);
                }
        }
Av: Christian Mogensen 31. jul 2018

RE: Project Senty issue affecting the ticket details screen in Customer Service

Hi Christian,

I don't fully understand how this syntax works, I alway need the project hidden from view even if it is a left outer join.

Reading your response to the other sentry post, you say "You need to make a sentry plugin that treats OuterJoins properly, and place the restriction on the join, rather than on the result.".  I have tried to do that, adding the restriction to the join so that no more than one row is ever returned.

                ProjectTableInfo pti = (ProjectTableInfo) tableInfo;
                ProjectMemberTableInfo pmti = TablesInfo.GetProjectMemberTableInfo();

                sql.JoinRestriction.LeftOuterJoin(pti.ProjectId.Equal(pmti.ProjectId),
                    pmti.PersonId.Equal(S.Parameter(SuperOffice.SoContext.CurrentPrincipal.PersonId)));

But I have had to also add the restriction to the result as well, as originally I tried it without the restriction, to then add the criteria to the ModifyTableRights routine.  But, although I can see my fields in the sentry lookups, sometimes the same field reference appears more than once, due to the orginal query being used, and the code below just gets the first instance of this field, not neccesarily my instance of the field.

                        ProjectMemberTableInfo pmti = TablesInfo.GetProjectMemberTableInfo();
                        projMemb = Convert.ToInt32(_sentry.Lookups[0].GetFieldValue(pmti.PersonId));

I could really do with creating a unique name for my field, by adding an alias, then try to use this in the ModifyTableRights routine, but this does not seem to work.

I hope this makes some kind of sense.

Trevor

Av: Trevor Sharp 31. jul 2018

RE: Project Senty issue affecting the ticket details screen in Customer Service

The first thing to realize is that the sentry plugin may be modifying a query where the table you are interested is not the main focus of the query.

Ok - this will need to be turned into an article at some point - but here goes:

Assume we want to hide all contacts with an X in the name.

public void ModifySelect(Select sql, TableInfo tableInfo)
{
    ContactTableInfo contactTable = (ContactTableInfo)tableInfo;
    sql.RestrictionAnd(contactTable.Name.NotLike("%X%"));
}

A query like this

     SELECT * FROM contact c WHERE c.contact_id = 123

Gets turned into

     SELECT * FROM contact c WHERE c.contact_id = 123 AND c.name NOT LIKE '%x%'

Because of our query modification. This works great! Ship it!

 

However, our query modification turns an e-mail search, where the primary table is the e-mail table

    SELECT * FROM email e 
    LEFT OUTER JOIN person p ON e.person_id = p.contact_id
    LEFT OUTER JOIN contact c ON e.contact_id = c.contact_id
    WHERE e.address = 'foo@bar.com'

Into this

    SELECT * FROM email e 
    LEFT OUTER JOIN person p ON e.person_id = p.person_id 
    LEFT OUTER JOIN contact c ON e.contact_id = c.contact_id 
    WHERE e.address = 'foo@bar.com' AND c.name NOT LIKE '%x%'

which basically throws away all the person matches (where the contact fields are NULL, and therefore NOT LIKE '%x%').

So we want to modify the LEFT OUTER JOIN clause instead

    SELECT * FROM email e 
    LEFT OUTER JOIN person p ON e.person_id = p.person_id 
    LEFT OUTER JOIN contact c ON e.contact_id = c.contact_id AND c.name NOT LIKE '%x%'
    WHERE e.address = 'foo@bar.com' 

This means that we get all our person matches, while skipping the contact matches that happen to belong to contacts that include an X in their name.

public void ModifySelect(Select sql, TableInfo tableInfo)
{
        ContactTableInfo contactTable = (ContactTableInfo)tableInfo;
        if( sql.IsTableOuterJoined(contactTable) )
        {
            var rels = sql.GetRelations(contactTable);
            var outerJoin = rels.FirstOrDefault();
            if( outerJoin != null )
                outerJoin.OriginalJoin.RestrictionAnd(contactTable.Name.NotLike("%X%"));
        }
        else
        {
            sql.RestrictionAnd(contactTable.Name.NotLike("%X%"));
        }
}
Av: Christian Mogensen 1. aug 2018

RE: Project Senty issue affecting the ticket details screen in Customer Service

Hi Christian,

Modifying the LEFT OUTER JOIN clause instead, does make perfect sense, and I have acheived this in a slightly different way to you, but I think I get the same result.  My code is,

                ProjectTableInfo pti = (ProjectTableInfo) tableInfo;
                ProjectMemberTableInfo pmti = TablesInfo.GetProjectMemberTableInfo();

                sql.JoinRestriction.LeftOuterJoin(pti.ProjectId.Equal(pmti.ProjectId),
                    pmti.PersonId.Equal(S.Parameter(SuperOffice.SoContext.CurrentPrincipal.PersonId)));

In a SQL trace, I can see the code this generates it as follows.

SELECT ...

FROM crm7.PROJECT T0

LEFT OUTER JOIN crm7.PROJTYPE T1 ON (T0.type_idx = T1.ProjType_id)

LEFT OUTER JOIN crm7.TEXT T2 ON (T0.text_id = T2.text_id)

LEFT OUTER JOIN crm7.PROJECTMEMBER T3 ON (T0.project_id = T3.project_id AND T3.person_id = 5)

But now I need to either add it to the Where clause, so that no row is returned, and therefore the user cannot see the record, or as you have suggested leave the Where clause alone, and instead add the T3.person_id as a return field, and in the ModifyTableRights check to see if the value is 5 (the currently logged on user) or Null, it should only be one of these two values, and if it is Null then the user does not have access to the record.

The trouble is whilst I do be seem to be able to get to this field, using the following Syntax in the ModifyTableRights procedure.

                        ProjectMemberTableInfo pmti = TablesInfo.GetProjectMemberTableInfo();
                        projMemb = Convert.ToInt32(_sentry.Lookups[0].GetFieldValue(pmti.PersonId));

This does not work correctly in all cases, as in some cases the project member PersonId, is already in the query generated by SuperOffice, and the code returns the first entry of this field, which is the original instance of the field, based on those table joins.  Not my instance of the field based on my join in the Modified SQL Query.  If I could Alias my fields I have added to the query, then guarnatee I get the correct instance of the field when I try to select my fields in the ModifyTableRights procedure, I think it would work, because it is working in some places, for example when you choose a project where you are a project member for, and not the project manager, you can see the project in the list (and the searches), but when you select the project it does not show.

When I add the restriction to the Where clause instead, all works in SuperOffice, it is just Customer Service so far where I have found any issues.

Trevor

Av: Trevor Sharp 1. aug 2018