How to Approach Advanced XDS Scenarios in D365FO

I’ve written about Extensible Data Security (XDS) in D365FO a couple times before, in those posts the XDS security policy is applied between two tables that have a direct tie to each other (ex: customers and customer groups). In this post, I wanted to show how you can use XDS even in scenarios where there isn’t a direct association or relationship between two tables.

The Scenario

The scenario I am using is similar to several end user requests I have gotten in the past, wanting a user to only see some piece of data that has a financial dimension (business unit, department, cost center) as the same as their worker/person associated record is assigned to in their position. In my scenario I am going to use ‘wanting a user to only see customers that are assigned to the same department as the position the worker associated to the user is assigned’.

To put that in simpler terms, if I have a user that’s associated to a person record with a position assigned to the ‘Finance’ department:

Then that user would only be able to see customers that have a financial dimension Department set to ‘Finance’.

Since there is no direct association between a user -> person -> position -> department -> customers we have to create one.

Solution

In cases like this, we will have to create a custom intermediary table to store the necessary association, but what does this table look like? The first thing to do is to determine the data we need to be able to use as the ‘primary table’ for our XDS policy. In our case we need a way to association a user -> customers they can interact with. So my custom table contains a user ID field and a customer reference which will store the RecId of the customer record the user has access to:

Now we can start to create the query we will use. In our case we are going to use two separate filters, one is going to be a Range for the current user and the other is a join between our FpUserCust table and the SysUserInfo table on the user Id. This will allow us to take the current user and filter the customer results to only those in the FpUserCust table:

Now we can set up the XDS policy, we set the following properties on the policy:

Constrained Table -> Yes (to let the policy know we are using a constrained table as the method of restricting access)
Content String -> (the policy applies to any role with a context string of DeptXDSPolicy)
Context Type -> Role Property (the policy applies to any role with a context string of DeptXDSPolicy)
Operation -> All Operations (the policy will apply to all SQL operations Create, Read, Update, Delete)
PrimaryTable -> FpUserCust (the primary table of the policy is going to be our custom table from above)
Query -> CurrentUserDepartment (set the policy to use query we created in the previous step)

Then we create a constrained table expression of: (CustTable.RecId == FpUserCust.CustRef)

This creates the join between our FpUserCust table to the CustTable and will actually restrict the user’s access. Also again be sure to set the Constrained property -> Yes.

Now we need a way to actually populate the FpUserCust table, in my example I am just using the main method of my demo solution but this could also easily be logic behind a button click or a batch job:

Here is a text version of the code above.

Once executed we can create a role that has access to the customers and set the Context String property as ‘DeptXDSPolicy’:

And then assign this role to our user:

Testing the Solution

Now we can validate that the above solution is working. First I wrote a SQL query to determine which customers this user should be able to see:

So this user should be able to see customers with Account Numbers of ‘004003’ and ‘3001’. If we log into the user and navigate to the customers page we see that this is what the user sees: