Introduction
Hello everyone, Glad to have you on my blog!
In this blog post, I am going to share some useful information regarding one security feature of Microsoft Dataverse, that I have been struggling to find the solution from past couple of weeks. So, the requirement was to conditionally secure/mask the Microsoft Dataverse column. There are numerous business requirements, where you have to conditionally secure/mask the Dataverse column. It seems easy to implement at first glance, however it’s not as easy as you think. Let’s think about following security requirements:
- Mask the last 4 digit of Account number (1234XXXX), if the account is PREMIUM.
- Users should only able to see last 4 digit of customer’s credit card number for ex: XXXX-XXXX-XXXX-4444
- Users should not be able to see the customer’s mobile no if he/she has opted to keep it PRIVATE.
- Solution becomes more complex. If you get the above security requirements in the later phase of the project. Sometimes, it’s easy to implement/design the solution, if you get such security requirements in the initial phase of the project (before production go live). Because, in that case. you only have to deal with new data. However, just imagine if such requirements comes in the later phase of the project, then it becomes more complex because till that time, some data has already been exposed to the end users and then you have to think about the solution that should work on new data and existing data both. And therefore sometimes, you might also need to run bulk update job as well to apply security on existing data.
What possible solutions that comes to your mind?
Field Level Security
Field Level Security is one of the most popular feature that strikes to your mind immediately whenever you think about securing the column’s value. However, it can not be used to conditionally secure the column’s value (for the requirements given above). Additionally, it’s a best solution for the security requirements where you have to completely secure/mask the column’s value (for ex. *******). not fit for the requirements, where you have to secure/mask only few characters/digits of the column’s value (for ex. 1234**** or ****ABCD).
One more limitation with Field Level Security is that, it cannot be applied on columns that you have used as Alternate Key in Microsoft Dataverse.
Plug-In
Plug-In is the best solution to conditionally and non-conditionally secure Dataverse data at server side. However, plug-in require triggers to run the business logic. Just imagine, if you already have the data on production then create, update trigger won’t work. You have to trigger the business logic on existing data. So, the only trigger point that meet the requirement is RetriveMultiple. However, i personally don’t recommend writing plug-in on Retrieve and RetrieveMultiple messages, because it might hampers your app performance, make your model driven apps unresponsive and slow down the client interaction. Check more details here.
Having said that, it’s your personal choice whether you still want to go ahead with plug-in based solution. However, I still think there should be a better solution, so that you don’t need to compromise with the app performance.
JavaScript
JavaScript is a client side language, so it can only secure/mask the column’s value either on forms or views. Data can still be exposed from various places like Advanced Find, Export to Excel, WEB APIs, Power Automate, Plugin, Workflow etc.
PCF Control
PCF Controls is a custom component that also made up with HTML/TypeScript/JavaScript and can only secure the data at client side. Data can still be exposed from various places like Advanced Find, Export to Excel, WEB APIs, Power Automate, Plugin, Workflow etc.
So is it really not possible to implement it?
Until you dare to take new challenges, you wouldn’t discover your full potential. So, I love taking challenges and working on challenging requirements😊
However, this requirement literally freaked me out from past couple of weeks, since I was struggling to find it’s solution neither on any article/MS Docs on internet nor by any community experts. So thought of posting this as new Idea on Power Apps Idea Portal. Can check here.
Additionally, I have posted it on my LinkedIn wall to know the views from my fellow MVPs, Experts and Dynamics Specialist. And, got to know that this is quite common business requirement, however the solution is still unknown to everyone. I really appreciate who voted for my Idea and suggested that there should be some inbuilt feature in Microsoft Dataverse to implement such requirements.
So without any further due – Here is the solution
Option 1 – Power Fx Formula Type Column
Power Fx – Formula type column is a new type of Dataverse column introduced by Microsoft that empower citizen developers to express business logic on top of existing columns and reference column from other tables, directly in Dataverse.
The major advantage of using formula type column is – All calculations are done at the Dataverse level, therefore the results can be seen and used in all Dataverse endpoints, including the Dataverse for Teams table view, canvas and model-driven Power Apps, Power Automate, Power BI, the Dataverse APIs, to name just a few.
Additionally, In Formula type column all calculations are done on the fly as added projections of the SQL Select query. Therefore, Results are always real time.
More details you can find here.
Note: At the time of writing this blog, Power Fx Formula type column is in PREVIEW. Therefore, it’s not recommended to use on production unless it is generally available. Can check the release plan here.
Example: I have used Power Fx Formula type column to conditionally mask the Mobile Number available on Contact form. My requirement is to mask the last 5 digits of mobile number if customer has opted to keep it hidden, else display the complete mobile number to CRM users.
So to implement that, I have created a Formula Type Column and Write Power Fx Expression to conditionally mask the last 5 digits of mobile number.
Power Fx Expression: If(‘Keep it secret’, Concatenate(Left(‘Mobile Phone’,5),””,”*****”),’Mobile Phone’)
Description: If Keep it secret (boolean type) column’s value is set to true then get the first 5 digits of mobile number and concatenate it with *****. So the outcome will look like this on form and view.
Major Advantages of using formula type column are:
- It works On Read operation. That means all calculations are done on the fly as added projections of the SQL Select query. Therefore, Results are always real time.
- It execute the expression server side. So security will never be compromised. Hence, user can not access it’s value neither via form, view, advanced find, export to excel etc. nor via Plugin, Power Automate, Workflow, WEB API etc.
- You don’t need to worry about updating existing data, if you create this field in the later phase of the project. So no bulk update job needed to update legacy data.
- You can conditionally mask/secure column’s value.
Option 2 – Calculated Column
Obviously, Power Fx Formula type column is a next generation column in Dataverse. However until you are not able to use Power Fx Formula type column and you have an urgency to immediately write business logic on production, then you can use Calculated Field as well to conditionally secure/mask the column’s value that also does the same.
Will Microsoft replace Calculated and Rollup Columns with Power Fx?
As per Microsoft – Yes in the future, But not at present and there will be no rush, those existing technologies are tried and true. Only when we have more feedback and experience, and we’ve worked through any functionality gaps, will we will draw up and communicate a long term migration plan. In time formula columns should be able to do everything that can be done today, and much more, in an easier to use and Power Platform consistent manner.
So, as of now there is no such plan to completely deprecate the calculated and rollup columns from Microsoft Dataverse. Power Fx is a new feature that is being introduced by Microsoft to overcome the limitations of calculated/rollup columns along with some advance expressions to write excel like business logic.
Hence, Power Fx Formula type column is a future and it’s a long term migration plan of calculated and rollup columns. Having said that, unless Microsoft includes all the features of calculated and rollup columns in the Power Fx, it’s not going to deprecate. So currently we are allowed to use in our Dataverse environments.
Conclusion
So, finally we have got the feature in Microsoft Dataverse that can mask the field conditionally. Although, Power Fx is not that mature feature right now, however since it’s in the Microsoft’s release plan, so definitely it will be more powerful in the future compared to Calculated and Rollup fields. Some features like masking/unmasking based on security roles or based on some related table is not possible right now however, it will be there for sure in near future.
Important Tip: The only challenge that still I want to highlight is – You can’t apply it on the existing field. So for example, if you create an account number column of type single line text and deployed it to production. Then, later if client asks to mask the account number based on Account type (Standard/Premium), you can’t do anything with the original field. You’ll have to create a new field (of Formula or Calculated type) and need to write the Power Fx expression/formula to copy the value from original column, mask it and store it in the new column. Additionally, you will have to apply the field level security also to the original field, so that user could not read the value from original column. Bit tricky, but you have the solution!
Please do not forget to share your valuable feedback/input, if you find this information worth reading it.
Also, Subscribe/Follow to get notified for such more interesting blog post in the future. Stay Tuned!
Thank You!
Arpit Power Guide 