cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
agigliotti
Level: Powered On

Role based Security implementation in PowerApps with SQL Server Connection

Is it possible to have role based security in a PowerApps app when the connection is a SQL Server database?  Can you give some users read only permissions and some users read/write permissions on certain tables from the SQL Server connection that are being used in PowerApps?

 

If so, how would this be done?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Role based Security implementation in PowerApps with SQL Server Connection

Hi agigliotti,

 

Please consider to use DataSourceInfo function to see if it will work for you, please check more details at here:
https://powerapps.microsoft.com/en-us/tutorials/function-datasourceinfo/

 

We can use information at the data-source level, for example, to disable or hide Edit and New buttons for users who don't have permissions to edit and create records.

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Role based Security implementation in PowerApps with SQL Server Connection

Hi agigliotti,

 

Please consider to use DataSourceInfo function to see if it will work for you, please check more details at here:
https://powerapps.microsoft.com/en-us/tutorials/function-datasourceinfo/

 

We can use information at the data-source level, for example, to disable or hide Edit and New buttons for users who don't have permissions to edit and create records.

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

pepeday
Level: Powered On

Re: Role based Security implementation in PowerApps with SQL Server Connection

I know this is marked as solved but how would that work? The connection only uses 1 user/pass combination which would be 1 login at the sql server, so the server would only understand all users as 1 login. Can we utilize sql logins at the powerapps level?
seadude
Level 10

Re: Role based Security implementation in PowerApps with SQL Server Connection

Great question. I have this on my list of unanswered as well...

Basically: Can you have multiple Connections per SQL Connector? For general "Roles" in SQL I create a User, a Role, assign Permissions to the Role, then assign the User to the Role. I then use the Users credentials for the PowerApps SQL Connection. This only results in 1 permission level though.

Example:

 

--Create Contained DB User
CREATE USER powerAppsUser with password = 'superHardPassword'
--Create Role
CREATE ROLE powerApps
--Grant Role the minimal SQL read/write Permissions
EXEC sp_addrolemember N'db_datareader', N'powerAppsUser' GO EXEC sp_addrolemember N'db_datawriter', N'powerAppsUser' GO
--Add User to Role
ALTER ROLE powerApps ADD MEMBER powerAppsUser;

I then Add Data Source and sign into the SQL Connection using these credentials. But this creates only a

 single User/Permission level. Maybe in SQL you could give this user access to ONLY certain tables? Then create another User with permissions to other tables and created another SQL Connection (Add Data Source/Login with this other users creds).
How can we choose which connection the user has access to? Views?

 

seadude
Level 10

Re: Role based Security implementation in PowerApps with SQL Server Connection

Looking further into this, @v-yamao-msft's suggestion has a link to determine if user has permission on a data source. I think weaving this into the the above scenario where multiple users are created and connected to the app could be the solution. I'll try messing with it and report back on what I find (as time allows 🙂 ).

seadude
Level 10

Re: Role based Security implementation in PowerApps with SQL Server Connection

Wow, Ok. It does not appear that you can specify different SQL Permissions in PowerApps. At least not that I can see. I spent ~4hours testing today.

What I did:

  1. Setup a new Azure SQL DB using Azure CLI
  2. Created 3 tables (table1, table2, table3)
    1. Populated tables with simple test data
  3. Created a view based on all 3 tables (vwDetails)
  4. Created 3 Users: (user1, user2, user3)
    1. Granted user1 permissions on table1 & table2 only
      1. CREATE user1 WITH PASSWORD = 'powerTest1';
        GRANT INSERT, SELECT, UPDATE on table1 TO user1;
        GRANT INSERT, SELECT, UPDATE on table2 TO user1
    2. Granted user2 permissions on table3 only
    3. Granted user3 permissions on all tables
    4. Granted no users permission to the view
  5. In PowerApps I added a SQL Server Connection (View/Data Sources/Add Data Source/New Connection)
    1. NOTE: Had to add 3 Connections, one for each user. There is not a way to specify multiple users in a single connection.
    2. As I created each Connection, only the tables I specified in SQL were visible for each user.
    3. Example:
      1. Enter SQL Server name, enter database name, UN: user1, PW: powerTest1, Connect
      2. Only table1 and table2 were available to connect to.
      3. With user2, only table3 was visible
    4. NOTE: When creating user3's connection, I selected all 3 tables. This is where the trouble began.
      1. Notice the "_1", "_2" appended to the table names where connections overlap.
      2. image.png
  6. I then tried to add some ClearCollect() functions but received an error upon executing. I have a feeling this is because PowerApps doesn't know which Connection to use to execute the function.
    1. "Connection not configured for this data source."
    2. image.png
    3. I then looked into the DataSourceInfo() functions that @v-yamao-msft mentioned. PowerApps had the same problem as above; it shows "true" for .Delete/.Edit/.ReadPermission no matter which table I select. Again, I'm thinking this is because it doesn't know which of the 3 Connections to use for this function.
    4. Example: user1 does NOT have read permissions on table3, but the DataSourceInfo() function below shows "true" because ONE of the Connections (user3) DOES have permission on table3.
      1. image.png
  7. I started looking into configuring SQL access by Azure Active Directory name/group, but I think this would result in the same problem.

I'm no SQL expert by any means so take this research with a grain of salt. Hope it helps further the discussion and I look forward to hearing how others are handling this.
Feels like the SQL Connector needs a way to see who (Active Directory) is logged into the app then translate this to SQL Permissions. I don't think this is currently available.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (6,481)