PowerShell script: Prevent Power Apps users from accessing lists from SharePoint UI

Power Apps with SharePoint lists as a data source are becoming widely built, especially due to licensing, as if a company already has the Microsoft 365/Office 365 enterprise plans, SharePoint connectors for Power Apps are included (while Azure SQL/Common Data services and others require premium plans).

If we connect any SharePoint list to Power Apps, it has to be shared with all users for them to be able to use the app, and if the list is shared using the out-of-the-box permission levels, users will be able to bypass the app and navigate to the list and edit the data in the browser, which can override rules in your app or expose data in a way it was meant to be.

There are some tweaks we can do to block the access from SharePoint UI.

Configurations needed:

Below is the sequence of actions that should be taken to make sure users can’t open the list(s) from the SharePoint UI:

  1. Create two new custom permission levels, removing the View Application Pages permission (users will still be able to access content from APIs, but not from SharePoint UI):
    • Read from Power Apps (Copied from Read) 
    • Collaborate from Power Apps (Copied from Collaborate)
  2. Create two new user groups that will be used by users that should access the lists data from your app only (no permission added at the site level, we just want the groups to access explicitly the lists used in the Power App):
    • Power Apps Readers
    • Power Apps Contributors
  3. Break permissions in the lists you want to use in the App and grant permissions to the new groups:
    • Power Apps Readers: Read from Power Apps
    • Power Apps Contributors: Collaborate from Power Apps

Additional/optional tweak: Hide the lists from SharePoint search results.

Configuration Results

If users from those two created/configured groups don’t have access to the list by other groups or direct permissions and try to access the lists or site even if they have the URL, they’ll get the classic access denied message:

permissions denied

But results are normally shown when accessing data in the Power App:

App

PowerShell Script to apply the configurations

The script below executes the steps mentioned. Replace the site URL and list names used in the Power App to your site collection and list names before running it. The same script can be used to apply the right permissions to existing site groups if you prefer to use existing groups instead of creating the two groups I suggested above for the purpose. With the option of removing current existing list permissions for other groups or not. Just change the script settings part according to your needs.

PnP Powershell library is needed to execute the script, and we are assuming the out-of-the-box Read and Contribute levels for the site were never changed.

$currSiteCollectionUrl = “<your site URL>

#Array with the names for the lists you want to apply the permissions, add more list names if needed 

$listNames = @(“Test List”, “Second Test List”)

#Group names: Change to existing group names if you want to update existing group permissions instead of creating new groups

#For existing groups, they are not removed from root site. Permissions updated at list level only

$readersName = “Power Apps Readers”

$membersName = “Power Apps Contributors”

##keeps current permissions for other groups in the list

$keepOtherGroupsPemissions = $true 

$readersName = “Site Visitors” # “Power Apps Readers”

$membersName = “Site Members”# “Power Apps Contributors”

 

#Connect to your site

Connect-PnPOnline -Url $currSiteCollectionUrl -UseWebLogin

 

#Permission level names

$paContribute = “Contribute from Power Apps”

$paRead = “Read from Power Apps”

$existingRoleDefinitions = Get-PnPRoleDefinition

##Custom permission levels (Assign the next calls to variables to avoid the dummy format-output errors): 

$roleDefContribute = Add-PnPRoleDefinition -RoleName $paContribute -Clone “Contribute” `

 -Exclude ViewFormPages 

$roleDefRead = Add-PnPRoleDefinition -RoleName $paRead -Clone “Read” `

-Exclude ViewFormPages

 

##Creates the two new groups: 

$readers = Get-PnPGroup -Identity $readersName -ErrorAction Ignore

$members = Get-PnPGroup -Identity $membersName -ErrorAction Ignore

 

$readersExisted = ($readers -ne $null)

$membersExisted = ($members -ne $null)

 

if(!$readersExisted){ $readers = New-PnPGroup -Title $readersName }

if(!$membersExisted){ $members = New-PnPGroup -Title $membersName }

 

##Iterates through the specified lists and do the configuration in each

$listNames | ForEach-Object {

  $listName = $_   

  $list = Get-PnPList -Identity $listName -Includes HasUniqueRoleAssignments,Title

  if($list.HasUniqueRoleAssignments -and !$keepOtherGroupsPemissions){

    ##Resets role inheritance to break it later clearing it

     $list.ResetRoleInheritance()

     $list.Context.Load($list)

     Invoke-PnPQuery   

  }

  ##Excludes from search results

  $list.NoCrawl = $True  

  $list.Update()    

  ##Breaks role inheritance if it was not done before

  if(!$list.HasUniqueRoleAssignments){

     $list.BreakRoleInheritance($keepOtherGroupsPemissions,$false)

  }

  $list.Context.Load($list)

  Invoke-PnPQuery  

  if($keepOtherGroupsPemissions -and ($membersExisted -or $readersExisted)){      

     ##If not clearing current permissions, remove any for current groups to add them later

     $existingRoleDefinitions | ForEach-Object { 

       if($readersExisted){            

         Set-PnPListPermission -Identity $listName -Group  $membersName `

          -RemoveRole $_.Name -ErrorAction Ignore

       }

       if($membersExisted){    

         Set-PnPListPermission -Identity $listName -Group  $readersName `

         -RemoveRole $_.Name -ErrorAction Ignore

       }

     }        

  } 

  ##Grants right permisisons to groups

  Set-PnPListPermission -Identity $listName -Group  $membersName `

  -AddRole $paContribute 

 

  Set-PnPListPermission -Identity $listName -Group  $readersName `

  -AddRole $paRead 

Disconnect-PnPOnline  

Script (also in my Github account):

After the configurations are done, add the users to the newly created groups (according to the permissions needed) and share the app with them as users.

Make sure they only have permissions on the list through the mentioned groups.

Users would still be able to edit/view the data from any tool that can connect to the list using SharePoint APIs/CSOM (as SPFx solutions hosted in other site or another Power App created by someone else). For a more restrictive security model, folder or item level security have to be used.

7 comments

  1. Hi!
    I believe this is exactly what I need, but I´m new to Sharepoint and PowerApps.
    Where should I write this script?

    1. You can use PowerShell ISE or another compatible editor as Visual Studio Code. Remember to install PnP PowerShell to run it.

  2. Can you demonstrate how PowerApps exposes the URLs of its data sources to the users?
    I don’t see that.

    1. Power Apps wouldn’t expose the URL to anyone….However, a simple leaked link could do it…or if the person has access to other content in the site, they may find the lists or guess the link if it’s hidden only.

Leave a Reply

Your email address will not be published. Required fields are marked *