Role based Security implementation in PowerApps with SQL Server Connection

[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?

[v-yamao-msft · Accepted solution]

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

[pepeday]

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]

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]

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]

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