Row Level Filtering – Proof of Concept


David MeegoOver the years I have seen requests for Row Level Filtering functionality within Microsoft Dynamics GP.


This is functionality where specific users can only see the records or rows in the tables that relate to their division or area.  Most of the time people are told that this is not possible.


Well, saying something is not possible is just raising a challenge to me. So I decided to develop a Dexterity add-on tool that could prove it was possible.  I am attaching the resulting Proof of Concept demos to this posting. It only works for the lesson or sample company and is not available for sale.  It is just to show what is possible.


While it is possible to use Visual Basic for Applications (VBA) to filter records, VBA can only work at the user interface by hiding invalid records.  All the records are still being retrieved from the SQL Server while only valid records are being displayed. This can cause poor performance when a large number of records are being returned and discarded. Dexterity can use a SQL where clause to prevent the invalid records being returned from the server and so maintain the performance while transparently hiding invalid records.


The demo code uses the User Class matrixed with the Customer Classes, Vendor Classes, Item Classes, Employee Classes and SOP Document IDs to restrict what records can be seen. Below is a screenshot of the Setup window:


Row Level Filtering


The concept I used, is to use Dexterity to a FORM_PRE trigger on any window that you wish to apply Row Level Filtering to and in that trigger use the range table where command to apply a SQL where clause to the form's table buffer for the specific table. This limits what is seen by the end user.  You can use a subquery in the where clause to link across multiple SQL tables to create the business rules for the restrictions.
 
You will need a trigger in the primary key field to use a function to look at the table without the where clause and again with the where clause. This would be used to know when the record already exists in the system, but the user does not have access to it.
 
You may also need to add extra triggers to handle the situations where the calls are made to functions and procedures and the form's table buffer is not passed as a parameter.  In these situations you will need to add code to limit the temporary table buffer used inside the procedure or function.  You might need to have access to the source code to be able to achieve this properly.
 
Finally, it would be worth noting that this technique has the following limitations:



  • It does not allow you to filter reports as there are no Dexterity triggers available on reports.

  • It is possible to filter Advanced Lookups searching functionality, but this requires extra development against each lookup window.

  • It would probably be possible to restrict the standard Smartlists, but this would need additional development.  Not sure if Smartlist Builder Smartlists can be restricted.

  • It needs to be coded against every window that you want the restrictions to apply.

The Knowledge Base (KB) articles below explain some of the techniques used:


How to write "Passthrough" SQL statements and "Range Where" clauses in Microsoft Great Plains Dexterity (KB 910129) Secure Link


How to use a "Range Where" clause that is based on more than one table in Dexterity in Microsoft Dynamics GP (KB 922056) Secure Link


The only other technique I have heard about was to handle this all at the SQL level.  You would need to rename the original table and create a view with the same name as the original table.  This view is then limited by your filtering business rules. Note that the views would need to be removed and the tables renamed back to their original names, whenever service packs, hotfixes or version upgrades are installed.


NOTE: This is not an officially released tool and is not supported.  It is made available "as is" and is only functional with the lesson or sample company.


The code and documentation for v8.0, v9.0 & v10.0 is attached at the bottom of the article.


Please add your comments to say what you think of the demo code.


David

RowSecur.zip

Comments (9)

  1. eva_eagle says:

    it would be so great if this is made into an officially released tool 🙂  .. a number of our clients have been asking about this functionality..

    🙂

  2. Malek says:

    I wonder how would GP be without you Dave !!!

    Thanks for the help you offer to GP community.

    Wish you all the best.

    Malek.

  3. David,

    Its really a very nice functionality, why it couldn’t released by Microsoft?!

    Regards,

    Mohammad

  4. David Musgrave says:

    Hi Mohammad

    While the Row Level Filtering demo is very cool and proves that it is possible to hide data based on a set of business rules, it is is not perfect.

    It does not work on reports at all. It possibly could be coded to work with the standard SmartList favourites, but I doubt it could work with SmartList Builder favourites.

    It also only works on the windows it has been coded against.  So it would need to be customised not only to support windows from Microsoft supplied products but also add-on products from other vendors.

    It was just not solid enough to be an official product.  In fact, I was asked to rename it from the original name of Row Level Security to Row Level Filtering to indicate that it was not 100% secure.

    The main purpose of this post is to let people know this is possible and explain the techniques and concepts involved so they can develop their own versions.

    David

  5. Phil says:

    Hi Dave,

    Any possibility of releasing a 2010 version of this?

  6. Marcos says:

    Hi David, Had you ever think about altering 'zDP_' stored procedures to include 'where' clauses to hide data based on a set of business rules?

  7. David Musgrave says:

    Hi Marcos

    Modifying the zDP stored procedures is an interesting thought.

    It could work for most situations where Dexterity is calling the zDP sprocs.

    However, the zDP sprocs are not used when there is a where clause applied using the Dexterity range table where command, or when passthrough T-SQL or other stored procedures are called. Also, if the zDP is missing, the Dexterity code just issues a select statement itself.

    These situations will bypass anything done at the zDP level.  Also upgrades will drop and recreate zDP scripts, so you would need to re-apply your changes.

    Keep the ideas coming.

    David

Skip to main content