Excel just turned 40 years old and is as popular as ever. Getting data into a spreadsheet that you can control remains the most empowering experience for information workers. There’s not a BI tool on the planet that doesn’t have an “Export to Excel” button. Or if there is, you’ve probably never heard of it, because customers will have looked elsewhere. Excel is the one app that cannot be beaten.

Ever since the early Dynamics CRM days, there has been an “Export to Excel” button in Microsoft’s own business apps, of course. And not merely for static data dumps. Already 20 years ago, when these apps were deployed to on-premises servers in the internal corporate network, we had the option for using dynamic Excel workbooks that directly refreshed the latest figures from the underlying SQL database. Combine that with pivot tables and you’d have something bigger than Power BI, way before SaaS apps were a thing.

Moving things to the cloud took the servers further away from the clients and caused a bit of a data winter for business apps. Reporting became a struggle for many years when reading the data directly from the SQL views wasn’t possible. We’ve since then gained a bunch of new features and data middleware that offer a variety of ways to interact with cloud data in Power Apps / Dynamics 365.

Many people don’t understand how much power a regular user has for accessing data today. If they have a citizen developer mindset, there’s not much separating them from how professional software developers could interact with data stored in Dataverse tables. In this newsletter issue I want to demonstrate why you shouldn’t look for an easy way out, but rather acknowledge the new reality and plan your actions accordingly.

In every Power Apps model-driven app / Dynamics 365 CE app, the views will have a built-in command bar button that offers options like “Open in Excel Online”, “Static Worksheet”, “Dynamic Worksheet” to take the data into Excel.

Is that a bit too easy? With a button visible right there for every user, don’t we run the risk of encouraging people to grab the data into an Excel file? In scenarios like sales CRM, the prospect of an employee casually misusing the system is a common concerns. “What’s stopping our sales managers from downloading all the customers and leads before resigning and going to work for our competitor?”

It’s a natural question to ask. Especially if the intention behind implementing a proper business app like CRM has been to move away from Excel workbooks and into a central database with better control mechanisms for data access. Microsoft must have heard that question early on in the product lifecycle, which led to the inclusion of a specific privilege (prvExportToExcel) being introduced in the platform.

You can find this in the Dataverse security role editor, under privacy-related privileges. It’s an on/off switch that has no further granularity. You either can export to Excel or can’t.

What does this privilege do in practice? Now when our user logs back into the app and opens the view, all the Excel-related options will have disappeared from the command bar:

“Great, problem solved!” Well, not really. You have performed the bare minimum action to comply with the request coming from business leaders worried about data leaking to Excel files. You addressed the one visible reminder of what users can do, by swiping the problem under the table. In a way, this creates a false sense of security that can lead you to ignore actual risks.

Let’s start by looking at the built-in features of Microsoft products. Meaning, buttons that the user can simply click on and get the data into Excel.

How about that interesting “Visualize this view” button that remained in the command bar? What happens if we click on that Power BI icon? In a few seconds, we’ll have a visualization of the view contents in a modal window:

And how did these charts get created then? From the underlying Dataverse table data. If you’re familiar with what Power BI visuals offer, you may recall that the grids on the report will by default happily provide an “export data” option for the user to click on.

Well, that was quick! It is of course another feature that the admins could disable. Not via security roles, but rather via managing model-driven app features. Do note that you do not require a Power BI Pro license or anything for accessing the Dataverse table visualizations via this embedded PBI experience.

Now, let’s assume our smart admin has toggled off another useful feature from the product and we can no longer visualize this view. We don’t even have Power BI installed on our PC. But everyone has Excel.

Just because it’s been around for four decades doesn’t mean that Excel is merely the same spreadsheet application that it was in the 90s. These days there’s a bunch of Power BI style features built into Excel, too. The core capabilities of Power Query are present in both products when clicking on “Get Data”.

One of the built-in options is getting data from Power Platform - Dataverse. Anyone dealing with business application security configuration absolutely should ask a regular user in their organization to open this menu. You might be surprised/shocked to see how many places are accessible to a user with their Entra ID identity. In our demo case, the user can of course read all columns from any table that they’ve been granted read access to via the Dataverse security roles:

You don’t need to be an Excel wizard for any of this, thanks to AI! All the user has to do is ask Copilot for help with what they are trying to do. Remember, these days it’s easier to end up in a Copilot Chat than it is to find your business apps from under office.com.

I tested this with a non-premium user account and the basic Copilot Chat. Microsoft 365 Copilot suggested these alternatives to the Export to Excel button on the first prompt:

All valid options to go around the security role limitation. But the easiest way required me to specifically ask for it: “can I just use a URL to get the raw data?” Here, the beauty of personalized LLM responses is that Copilot can spit out the exact URL for you:

This gets us to what I think is the most important demo to show to any person who thinks that product-specific settings or UI-level elements can deliver “security”. Whenever someone is worried that an app or a community tool provides excessive access to data, just open the OData endpoint URL for some sensitive Dataverse table and ask them whether they knew this was possible.

You don’t have to settle for just looking at the raw data of all the columns and rows in the table. Copilot could probably create the necessary filters for you via prompting it, but there’s also a GUI for that. FetchXML Builder in XrmToolBox allows you to visually configure the query, after which you get the OData v4.0 URL to retrieve the data in your browser:

These are all things that our regular end user was able to do, just with the read-only rights to the underlying Dataverse tables. You must remember that it’s NOT an admin thing. Even smart folks who know a lot about technology in general can forget that the application UI does not deliver data security.

We used to worry about the powerful connectors that citizen developers gained access to via Power Automate. These days, any citizen can vibe code a piece of code or a direct URL to read the same REST APIs that are behind those connectors. None of this was impossible before. Yet it’s undeniable that just like low-code tooling before, the commoditization of LLMs via AI chatbots is democratizing code. Making its powers accessible to much wider audiences than before. It’s wonderful, and scary.

Just because by default the security roles of your Dynamics 365 or Power Apps applications provide access rights to data, that doesn’t mean it has to be an all-you-can-eat buffet. The best option, one that you need to always consider before anything else, is “does the user need to see all of this?” Knowing how to design the security model in Dataverse is the foundational skill.

If you’re just prompting your AI chatbot with questions about how to show X and Y in your app, it’s not going to proactively suggest how to limit visibility. You can, however, ask it to explain the security concepts. It won’t be 100% accurate but better than thinking “this is too complex for me to worry about, I’ll figure it out later”. Here’s a Dataverse security cheat sheet for app makers that Claude produced in a few minutes. I wouldn’t publish it as an article, but it’s not bad. Asking more questions from AI and then validating how things really work is a solid strategy to get better at this.

The inconvenient truth is, though, that often your employees will need access to a lot of business data to operate the business processes they are responsible for. Security roles can be easily used for protecting the data from unintentional updates, and especially delete operations. As for being allowed to view the data — you’ll inevitably need some level of trust in your employees to follow the rules. Yet like in society, rules work best when everyone knows there are potential consequences from breaking them.

How could we catch those who break the rules? Examining the activities of users retrospectively is possible via the comprehensive audit logging capabilities Microsoft has built into its cloud. Microsoft Dataverse and model-driven apps activity logging documentation describes how you might find the needle in the haystack. Assuming you’ve remembered to turn on read auditing for your Power Platform environment, of course. Plus enabled the tables you care about for auditing, because defaults won’t necessarily cover them.

It would be nice to proactively prevent bad things from happening, of course. Cloud applications by default will be accessible from anywhere there’s an internet connection, which can be a bit too much compared to what the legit use cases for accessing your CRM system would be, for example. Conditional access policies in Entra ID are commonly used by organizations to restrict what devices and identities can connect to their cloud apps.

We can also go deeper than that. There’s a preview feature in Power Platform managed environments for app access control. What does that mean? In short, when an access request is presented to Dataverse, it’s going to check the App ID against a block/allow list of known IDs. If the rules aren’t configured to let the app through, the response will be “Access to the Dataverse API is restricted for this application ID.”

So, we could make it much harder to grab data in bulk from a Dataverse table if we decided to invest in putting up the necessary guardrails. Restrictions placed on device level and the gathering event logs on the traffic between clients and cloud services will often involve talking to Microsoft security experts. Those folks who’ll set up your SIEM (e.g. Sentinel), XDR (think Defender products) and IAM (Entra ID advanced capabilities). For addressing internal threats, you’ll also want someone looking at data governance and compliance tools like Purview.

You can’t outsource the responsibility of your business app governance and security model design to that audience, though. When the business deploys or builds apps and automations on top of Power Platform, someone’s got to understand how things work within that low-code realm. User requirements are always focused on what the visible interface should do. As more and more internal data is made accessible to AI agents, the API surface of available business information may expand even without any new UIs being built for it.

It’s better to hack yourself first. Find out where too much data can leak out, then plug the holes with the tools that fit the job. If you need help in identifying where the leaks are in your Power Platform based solutions, feel free to get in touch and we can figure it out.