Virtual Tables - Custom Ownership

October 3, 2022

7 min read

This will be a follow-up post on my session about Virtual Tables at Nordic Summit. People have asked me if there is a blog post about how to set up custom security on SQL Virtual Tables that are available out of the box via the virtual connector.

The Problem

Virtual Tables are organization owned and that makes them quite limited in real business world scenarios where some users should not see all the data that is stored in the database. We will show how to implement a basic security model around them to make them more usable.

This example will use Azure SQL Virtual connector, but it's working just the same with any other data source that is available out there. We need a custom owner field since there is no out-of-the-box one on the virtual table defined.

Polymorphic Lookup Issue

The owner on a normal table can be a team or a user, but on a virtual table, it's kinda tricky. The problem lays in polymorphic lookup for virtual tables. You can create a polymorphic lookup, with a help of some code because it's not available via UI, but it's acting a bit weird when you see it for the first time.

The polymorphic lookup field will store only GUID in your external data source and because of that, you will have a problem while rendering the value in the UI. Lookup picker will allow you to choose tables defined in the lookup just fine, but the problem starts when you try to present the value to the user. It will be rendered in the right way for just the primary relationship defined in the lookup field and all other values will be rendered as No Name and will point to the wrong table.

Since we now know the limitations we will try to implement the security with a simple lookup this time until there is full support for polymorphic lookups in virtual tables space.

Prerequisites

We will not cover the whole process of creating a SQL virtual table via a virtual connector in this blog so if you are interested in how to do that there is a pretty well-written step-by-step guide by Microsoft here.

We will start building the extension to the virtual table that already exists in the Dataverse.

Solution Overview

We will create a simple virtual table against the SQL database which will contain two owner columns: one as a lookup to the team and the second one as a text column. Then we will create two plugins to help us achieve the final goal.

Steps:

Create duplicate owner ID fields in the SQL table
Create a custom lookup field that points to the team table
Create a single line of text field to store GUID as text
Implement a plugin that will store GUID as text on owner change
Implement a plugin that will inject filters on RetrieveMultiple message

SQL Table

Let's create a simple SQL table as shown below.

CREATE TABLE Trainer(
    Id int IDENTITY(1,1) NOT NULL,
    FirstName varchar(255) NULL,
    LastName varchar(255) NULL,
    ImageURL varchar(255) NULL,
    OwnerId varchar(255) NULL,
    OwnerIdString nvarchar(255) NULL
)

There are only two columns that you need to include here. Those are OwnerId and OwnerIdString columns.

OwnerId will be used for a lookup field and OwnerIdString will store the text representation of the GUID selected in the lookup.

You may ask why do we need 2 fields when we can use only one lookup?

The answer is that currently there is a limitation on a virtual table where you can't filter it by the lookup field so we need to filter it by the text instead, but we still want users to be able to edit the ownership in the lookup field like they are used to.

Ok, but still do we really need two fields in the SQL table or we can use just one?

There is also another limitation of the virtual table that doesn't allow us to use the same column twice in the virtual table as an external name so we need to duplicate data in our data source.

SQL table should look something like this one:

That's all we need to do in SQL to make it work, let's jump into the Dataverse.

Lookup Field

First, we need to make a lookup field that will point to the Team table. This field will be exposed to the users via UI and users will be able to update it just like any other field.

There is nothing special about this one, just a simple lookup field that must have an external name set to the OwnerId column in the SQL table.

Text Field

The second field that we need is a single line of text field that will store the string representation of the GUID.

We should map this one, by setting the external name, to the second Owner field in the SQL database, OwnerIdString in this case.

Update plugin

Users should not experience our little hack with two fields that will hold the same value so we need to automate the process of copying the data to the second field first.

Let's create a simple plugin that will be triggered when the Owner lookup field is changed.

public class TrainerUpdatePlugin : IPlugin
{
    public void Execute(IServiceProvider serviceProvider)
    {
        var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

        var trainer = context.InputParameters["Target"] as Entity;

        if (trainer == null) { return; }

        var ownerRef = (EntityReference)trainer["ns_ownerid"];

        trainer["ns_owneridstring"] = ownerRef.Id.ToString();
    }
}

There is really nothing spectacular happening in this plugin. It will only copy the GUID to the string field every time the owner field is changed.

We should trigger it as a pre-operation plugin step on the change of an owner field.

Now we can jump to the most important part.

RetriveMultiple Plugin

RetrieveMutliple plugins don't get so much love from people in general and I think that they definitely deserve to be used more since there is so much potential there.

What will we do here?

We will intercept the RetrieveMultiple message and add some prefiltering to the original query so we filter out records that should not be shown to the user.

First of all, we need to normalize the query we get in the plugin since you will not always get QueryExpression which is easy for manipulation. Sometimes you will get FetchExpression instead and we need to convert it to QueryExpression.

It can be done with a simple helper function:

public QueryExpression GetRetrieveMultipleQueryExpression(IPluginExecutionContext context, IOrganizationService service)
{
    var retrieveMultipleQuery = context.InputParameters["Query"] as QueryExpression;

    if (!(retrieveMultipleQuery is null)) return retrieveMultipleQuery;

    var retrieveMultipleFetch = context.InputParameters["Query"] as FetchExpression;
    var fetchXmlToQueryExpressionRequest = new FetchXmlToQueryExpressionRequest()
    {
        FetchXml = retrieveMultipleFetch?.Query
    };
    var fetchXmlToQueryExpressionResponse = service.Execute(fetchXmlToQueryExpressionRequest) as FetchXmlToQueryExpressionResponse;
    retrieveMultipleQuery = fetchXmlToQueryExpressionResponse?.Query;

    return retrieveMultipleQuery;
}

Now we have a QueryExpression every time, perfect!

Since we will do the filtering based on the owner team we need to get all the teams that our user is a member of.

public List<Entity> GetUserTeams(IOrganizationService service, Guid userId)
{
    var fetchXml = $@"
    <fetch>
      <entity name='teammembership'>
        <attribute name='teamid' />
        <filter>
          <condition attribute='systemuserid' operator='eq' value='{userId:D}' />
        </filter>
      </entity>
    </fetch>";

    var teams = service.RetrieveMultiple(new FetchExpression(fetchXml)).Entities.ToList();

    return teams;
}

When we have teams we can start editing the initial query.

var filterExpression = new FilterExpression();
filterExpression.FilterOperator = LogicalOperator.Or;

foreach (var team in userTeams)
{
    var teamId = (Guid)team["teamid"];

    tracer.Trace($"  {teamId:D}");

    var teamCondition = new ConditionExpression("ns_owneridstring", ConditionOperator.Equal, teamId.ToString("D"));
    filterExpression.AddCondition(teamCondition);
}

retrieveMultipleQuery.Criteria.AddFilter(filterExpression);

context.InputParameters["Query"] = retrieveMultipleQuery;

Since we need to add our filters on top of all other conditions in the query we need to create a FilterExpression first to hold our conditions and set the operator to OR.

Then we should loop through all the teams we got in the GetUserTeams function and add them as ConditionExpressions to the filter.

In the end, we should append our filter to the original query and set the Query input parameter to the newly updated query.

Here is the whole plugin code:

public class TrainerRetrieveMultiplePlugin : IPlugin
{
    public void Execute(IServiceProvider serviceProvider)
    {
        var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
        var serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
        var service = serviceFactory.CreateOrganizationService(context.UserId);
        var tracer = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

        var retrieveMultipleQuery = GetRetrieveMultipleQueryExpression(context, service);

        var userTeams = GetUserTeams(service, context.UserId);

        tracer.Trace($"Number of teams found: " + userTeams.Count);

        var filterExpression = new FilterExpression();
        filterExpression.FilterOperator = LogicalOperator.Or;

        foreach (var team in userTeams)
        {
            var teamId = (Guid)team["teamid"];

            tracer.Trace($"  {teamId:D}");

            var teamCondition = new ConditionExpression("ns_owneridstring", ConditionOperator.Equal, teamId.ToString("D"));
            filterExpression.AddCondition(teamCondition);
        }

        retrieveMultipleQuery.Criteria.AddFilter(filterExpression);

        context.InputParameters["Query"] = retrieveMultipleQuery;
    }

    public List<Entity> GetUserTeams(IOrganizationService service, Guid userId)
    {
        var fetchXml = $@"
        <fetch>
          <entity name='teammembership'>
            <attribute name='teamid' />
            <filter>
              <condition attribute='systemuserid' operator='eq' value='{userId:D}' />
            </filter>
          </entity>
        </fetch>";

        var teams = service.RetrieveMultiple(new FetchExpression(fetchXml)).Entities.ToList();

        return teams;
    }

    public QueryExpression GetRetrieveMultipleQueryExpression(IPluginExecutionContext context, IOrganizationService service)
    {
        var retrieveMultipleQuery = context.InputParameters["Query"] as QueryExpression;

        if (!(retrieveMultipleQuery is null)) return retrieveMultipleQuery;

        var retrieveMultipleFetch = context.InputParameters["Query"] as FetchExpression;
        var fetchXmlToQueryExpressionRequest = new FetchXmlToQueryExpressionRequest()
        {
            FetchXml = retrieveMultipleFetch?.Query
        };
        var fetchXmlToQueryExpressionResponse = service.Execute(fetchXmlToQueryExpressionRequest) as FetchXmlToQueryExpressionResponse;
        retrieveMultipleQuery = fetchXmlToQueryExpressionResponse?.Query;

        return retrieveMultipleQuery;
    }
}

When we have all code written it's time to register a new RetrieveMultiple plugin step in the Plugin Registration Tool.

Make sure that you register it as a sync Pre-operation plugin, otherwise you will not be able to edit the Query parameter.

Final Solution

Let's see how it works. I have created records that have different owning teams, but my user is a member of only one of them.

Here is the list of all records before we turn the RetrieveMultiple plugin.

After we turn on the plugin we should see the filtered list. The user in this example is a member of Team Rocket so all the records that are owned by Team Pikachu will not be shown to him in the list.

This is the situation after we turned on the plugin:

Finally, we can have a working example for basic security around the virtual tables. Woohoo!

Conclusion

Working with virtual tables is still not perfect and we still need to pull out some hacks, but the situation is getting better with each release.

This method is not tied to SQL data sources so feel free to try it with any other data source out there.

I hope that in the future we will be able to add working polymorphic lookups to the virtual tables which will help us to achieve the final goal and that is the owner field that we are used to.

Until then we can go this basic route that allows us to edit the owner or simply hide the ownership from our users in the UI and set it in the hidden text field so we can do the filtering that way.

Blog.