Answered

Role based Security implementation in PowerApps with SQL Server Connection

(0) Share

Report

Posted on by agigliotti

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Verified answer

v-yamao-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
pepeday 62 on at

Like
a
(1)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
seadude 1,855 on at

Like
a
(0)

Report
Copy link

Link copied!
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?

Was this reply helpful? Yes No
seadude 1,855 on at

Like
a
(0)

Report
Copy link

Link copied!

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 🙂 ).

Was this reply helpful? Yes No
seadude 1,855 on at

Like
a
(2)

Report
Copy link

Link copied!
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:
Setup a new Azure SQL DB using Azure CLI
Created 3 tables (table1, table2, table3)
Populated tables with simple test data
Created a view based on all 3 tables (vwDetails)
Created 3 Users: (user1, user2, user3)
Granted user1 permissions on table1 & table2 only
CREATE user1 WITH PASSWORD = 'powerTest1'; GRANT INSERT, SELECT, UPDATE on table1 TO user1; GRANT INSERT, SELECT, UPDATE on table2 TO user1
Granted user2 permissions on table3 only
Granted user3 permissions on all tables
Granted no users permission to the view
In PowerApps I added a SQL Server Connection (View/Data Sources/Add Data Source/New Connection)
NOTE: Had to add 3 Connections, one for each user. There is not a way to specify multiple users in a single connection.
As I created each Connection, only the tables I specified in SQL were visible for each user.
Example:
Enter SQL Server name, enter database name, UN: user1, PW: powerTest1, Connect
Only table1 and table2 were available to connect to.
With user2, only table3 was visible
NOTE: When creating user3's connection, I selected all 3 tables. This is where the trouble began.
Notice the "_1", "_2" appended to the table names where connections overlap.
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.
"Connection not configured for this data source."
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.
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.
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.

Was this reply helpful? Yes No
SKV 6 on at

Like
a
(0)

Report
Copy link

Link copied!

I am also exactly in the same boat! Have you found a workaround for this scenario. How did you ended up solving this case

Was this reply helpful? Yes No