Table Permissions and FetchXML queries in Power Pages

When some new non-Power Platform developers joined a past project I worked on, they were looking at some existing code and api endpoints and were like:

Oh this web api accepts any FetchXML I put here! This should not be like this!

But…

They were not aware of how table permissions work and how they are appended to the queries. The same is valid for any FetchXML query executed.

Even if a rogue developer writes a query trying to access data that the users should not have access to, as long as the table permissions are properly configured, the data will be automatically trimmed by Power Pages.

Sample query

For example, in this demo scenario, for the accounts table, there are 2 table permissions configured:

1. To see the own account that relates to the current user (via parent customer field and a Contact access type)
2. To see all child accounts of that account (via account access type and the parent account field)

When we run the following query to list accounts:

<fetch top="5000" returntotalrecordcount="true">
    <entity name="account">
        <attribute name="accountid" />
        <attribute name="name" />
        <attribute name="emailaddress1" />
        <filter type="and">
            <condition attribute="statecode" operator="eq" value="0" />
        </filter>
    </entity>
</fetch>

This is what gets actually executed:

<fetch mapping="logical" distinct="true" returntotalrecordcount="true">
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="emailaddress1" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
    <filter type="and">
      <filter type="or" hint="union">
        <condition entityname="generated_alias_contact_0" attribute="contactid" operator="eq" value="688f822a-6db1-ee11-a569-000d3aa9a09b" />
        <condition attribute="parentaccountid" operator="eq" value="2db20c7c-db49-ef11-bfe2-0022488317e7" />
      </filter>
    </filter>
    <link-entity name="contact" from="parentcustomerid" to="accountid" alias="generated_alias_contact_0" link-type="outer" />
  </entity>
</fetch>

Power Pages will append filters to the query to allow only items within the user’s permissions to be read (note the bold part, accounts for both conditions, filter the account that is the parent one of the user and also the child ones – the account and contact IDs are automatically generated for the account of the current user).

The table permission will be automatically combined with the existing query filters if any (see the statecode filter is kept).

Debug queries

As the table permissions filters are automatically appended, the query that we are running via code will not be the same that gets executed most of the time, and therefore, if you test the query outside of Power Pages (for example on FetchXML Tester or FetchXML builder in XRMToolBox), the results won’t match.

But you can easily grab the exact query executed by Power Pages in liquid, using the syntax yourfetchqueryvariable.xml And then output it via javascript as below (see the script tag grabbing value from liquid to log in the console):

{% fetchxml fetch %}
<fetch top="5000" returntotalrecordcount="true">
  <entity name="account">
    <attribute name="accountid" />
    <attribute name="name" />
    <attribute name="emailaddress1" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>
{%endfetchxml%}
<script>
  console.log("This is the final FetchXML:");
  console.log(`{{fetch.xml}}`)
</script>

When you open the page, you will see the outputs on the console.

This helps troubleshoot any query:

Conclusion

Power Pages’ built-in security ensures data access is controlled via table permissions, and it automatically appends filters to FetchXML queries to restrict unauthorized access. Even if developers try to expose data that the users should not have access to, any query executed via FetchXML and Web API is amended in the background to match user permissions.

References

Configuring table permissions – Microsoft Learn

Assign table permissions – Microsoft Learn

Liquid template tags – FetchXML – Microsoft Learn

The post Table Permissions and FetchXML queries in Power Pages appeared first on michelcarlo.

Original Post https://michelcarlo.com/2025/05/03/table-permissions-and-fetchxml-queries-in-power-pages/