Integration of SQL Database Row-Level Security (RLS) with Power Apps and User Permissions

[HamidBee]

HI All, 

I'm currently working on a project that involves integrating an SQL database with Power Apps, and I'm encountering some questions regarding security and user permissions.

My plan is to create a SQL database table and implement Row-Level Security (RLS) to manage data access at a granular level. My primary question is: When this SQL database is integrated with a Power Apps application, will the RLS settings be effective within the Power Apps environment? In simpler terms, do the row-level security constraints set up in the SQL database automatically apply when the data is accessed through a Power Apps app?

Additionally, I need users of the Power Apps application to have the ability to edit records. This leads to my second question: Do these users require a special Power Apps license to edit records, especially considering the integration with a SQL database that has RLS? Are there specific licenses or permissions I need to be aware of to enable this functionality seamlessly?

Any insights, experiences, or best practices related to integrating SQL RLS with Power Apps, particularly regarding user permissions and licensing, would be immensely helpful. I'm looking for guidance on ensuring a smooth, secure, and functional integration of these two platforms, with a focus on user access and editing capabilities.

Thank you in advance for your expertise and advice!

@dpoggemann , @EricRegnier @Jmanriquerios @ChrisPiasecki @parvezghumra 

[Jmanriquerios]

@HamidBee 

A little about Power Apps and its capabilities and limitations.

Power Apps has two types of applications Model Driven Apps and Canvas Apps, each one has specific scenarios. For example model driven apps do not allow you to modify the user interface, and you must adapt to the model set up. Canvas apps, on the other hand, is a canvas that allows you to set up your own interface.

Having said that, another aspect to consider is that Model Driven apps use Dataverse as a requirement, so you can have virtual tables from Sharepoint, SQL or another source. Canvas Apps allows you to use data from the 1000+ connectors that currently exist.

Now answering specifically, you can't combine a sharepoint list with a form and have it point to Dataverse, you can't embed a canvas unless it has sharepoint as a source.

Now, the security issue is that when you share an application the users will have access to the application so you can't control the security under that scheme.

To edit the logs they will have to have a premium license because SQL is a premium connector.

At this point I would really analyse whether it is better to use Power Apps Premium as licensing and at the same time use a model based application with Sharepoint integration to use it as a document repository. The security in dataverse if it is role based and granular, you can even go down to the column and user level.

¿Qué es Power Apps? - Power Apps | Microsoft Learn

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
You can accept more than one post as a solution
Follow me on Linkedin, I talk about Power Platform
www.linkedin.com/in/jonathan-manrique-rios

[ivan_apps]

Take a look at this article: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/sql-server-security#clien...

If you want to implement row level security you will have to use an explicit connection to SQL Server, likely Microsoft Entra Integrated, so that the users will pass down their ID to SQL and you can filter their requests appropriately. 

I assume you are not using dataverse due to a specific requirement, but if you were you could simply use the Dataverse Security Model using Security Roles.

Licensing - SQL is a premium connector so all users that use this app would need a premium license.

[HamidBee]

Thanks for the reply. You mentioned:

---

To edit the logs they will have to have a premium license because SQL is a premium connector.

At this point I would really analyse whether it is better to use Power Apps Premium as licensing and at the same time use a model based application with Sharepoint integration to use it as a document repository. The security in dataverse if it is role based and granular, you can even go down to the column and user level.

If I was to build an app with a table from Dataverse would users need a premium connector to edit records too?. The reason I was trying to see if it was possible to use a SQL db instead of a Dataverse table is due to the answer I got to this question:

https://powerusers.microsoft.com/t5/Microsoft-Dataverse/How-to-Assign-Ownership-to-Existing-Records-...

Because Dataverse uses Record ownership I'm trying to find a way to quickly assign users ownership of records. I was advised to look into "FetchXML Builder + Bulk Data Updater". I was put off by this as I'm not familiar with the tools. 

[HamidBee]

If I were to build an app using a table from Dataverse, would users also require a premium connector to edit records? My investigation of using a SQL database instead of a Dataverse table stems from the response I received to this answer:

https://powerusers.microsoft.com/t5/Microsoft-Dataverse/How-to-Assign-Ownership-to-Existing-Records-...

Given that Dataverse employs record ownership, I am seeking an efficient method to bulk assign users ownership of records. In my case, records will only be uploaded once from an Excel file and then only edited. I just need a quick way to assign record ownership. I was advised to explore "FetchXML Builder + Bulk Data Updater" for this purpose. However, I am hesitant about this approach as I am not familiar with these tools.

[ivan_apps]

If you connect a Canvas app to Dataverse or SQL, it will mark the app as 'Premium' - Premium apps will run a license check for users accessing the app before it lets them in. Once you pass this license check, it really doesn't matter whether the user reads, creates, updates, or deletes records - it's all allowed by the licensing model.  Model-Driven apps are all premium so license checks will apply.

Now your application design can assign security roles to modify their access to tables, and what they can do within those tables - this is done using security roles. You'll specify what CRUD operations they are allowed to perform and at what level - organization, business unit, user. Row level security is done by specifying user or business unit level, Organization level gives them all access.  

Record ownership - this is important as records can be owned by users or teams, teams being a collection of users. Users in teams can inherit the security roles assigned to their teams in order to gain access to records that are not owned explicitly by a user. Say you want to make a record in your table owned by a team, ensure that team has the proper security role to own that record. Then all members of that team will have access to that record.  You can expand this team ownership model to quickly grant several users access to anything a 'team' should have access to by adding them to that team.

How to bulk assign record ownership - Several ways to do this. If you have a model-driven app, you can select Export To Excel option on the table view - then select the Open in Excel Online. If the owner field is there, you can copy that owner field down to all records. Save and close Excel Online and it will run a bulk update job in the background. Easy.

If you have a canvas app - I would probably recommend creating a power automate flow to bulk update record ownership.  Use the dataverse connector and get all records using the List Rows action. Then iterate and use the Update Row action to change the owner field to either /systemuser([rowGUID]) or /teams([rowGUID]) if you want to update the owner to a team or user. This works quickly without the need fetchXML builder or Bulk Updater tools.