Dataverse, formerly known as Common Data Service, is often considered an excellent data source due to its comprehensive set of features designed for enterprise-level solutions. It provides seamless integration with various Microsoft 365 services and supports complex data models with relationships, business rules, and workflows.

Even though Dataverse is a robust data source that integrates numerous security features such as row-level security, auditing, and role-based security, its licensing costs and storage capacity for holding files can be prohibitive. As a consultant, I often seek out more cost-effective resources that provide comparable levels of security. In this blog, I have compiled various methods to ensure that SharePoint can be effectively utilized as a data source while maintaining strong security features for your PowerApps.

Role-Based Access Control (RBAC)

Role-Based Access Control (RBAC) is a method of restricting system access to authorized users based on their roles within an Organization.

RBAC is also the most straight forward and easiest to plan and implement in majority of scenarios as everyone within your app should have a defined role and access to content and data.

Consider the three roles of A, B, and C with different access levels of Admin, Project User, and Basic User and how different levels of access can be implemented for each of these roles.

Implementing RBAC in PowerApps

To create RBAC in SharePoint, perhaps the easiest way is to start by setting up SharePoint groups. Here’s how you can do it:

Create SharePoint Groups:

First, create a list for all your permissions and ensure you “Stop Inheriting Permissions” if you wish to create unique permissions.

Next you can “Create Group” for your desired role and “Grant Permissions” to users whom you wish to be part of this group. This ensures that each user only has access to the resources they need based on their role.

Next, in the OnStar property in my app , I’m setting the permission values by looking up my group in SharePoint. Here’s how it works:

By doing this, I can control access to certain features in the app based on the user’s permissions. For example, if “CanCreateNewCustomerDetails” is true, the user can create a new account for new customers; if false, that functionality is hidden or disabled.

By combining SharePoint’s native security features with PowerApps’ flexible development environment, you can create a RBAC remarkably similar to existing capabilities within Dataverse.

Item-Level Security

Item-level security ensures that users can only access specific items in a SharePoint list or library based on their permissions.

Item-level security is necessary when different users or roles need varying levels of access to specific items within a SharePoint list or library. This is particularly useful in scenarios where sensitive information should be restricted to certain individuals, or when multiple roles interact with the same items but require distinct permissions.

Implementing Item-Level Security in SharePoint

1. Enable Item-Level Permissions: Go to the settings of the SharePoint list and configure item-level permissions. You can specify whether users can read and edit only their own items or all items.
2. Break Inheritance: For more granular control, break the inheritance of permissions on specific items and assign unique permissions.
3. Integrate with PowerApps: In your PowerApps application, use the SharePoint list as a data source. The item-level permissions configured in SharePoint will automatically apply when accessing the data in PowerApps.
4. Set Custom Level Permissions using Power Automate: If you require custom level permissions for specific list items or folders, you can utilise Power Automate to set these up. Create a flow that triggers when an item is created or modified and then use the “Send an HTTP request to SharePoint” action to break inheritance and apply unique permissions. This allows you to tailor access permissions to meet specific needs without manual intervention.

Column-Level Security

Column-level security controls access to specific columns (fields) within a list or library. This means you can restrict who can view or edit certain columns, regardless of their access to the rest of the item.

The difference between column-level security and item-level security is that item-level security controls access to entire items within a SharePoint list, allowing users to view or edit only specific items based on their permissions. Column-level security, on the other hand, restricts access to specific fields within an item, enabling more granular control over who can see or edit particular columns. For example, item-level security lets sales reps only access their clients’ records, while column-level security allows senior staff to see and edit sensitive fields like contract values.

Implementing Column-Level Security in SharePoint

1. Create Multiple Views: In SharePoint, create multiple views of the list, each showing or hiding specific columns based on the permissions required.
2. Configure Conditional Visibility: In PowerApps, implement conditional visibility using the roles or permissions defined. For example, display certain columns only if the user belongs to a specific group.

Here is an example of how you can incorporate this method using the SharePoint security groups:

Hierarchical Data Access

Hierarchical data access allows users to access data based on their position within the Organisational hierarchy.

When implementing hierarchical data access in PowerApps using SharePoint, slight differences may arise due to the integration with SharePoint lists and libraries. Specifically, data filtering and permissions management rely heavily on the structure and settings within SharePoint, potentially requiring more intricate configuration to ensure seamless access control across PowerApps and SharePoint.

I have identified methods to replicate an experience similar to Dataverse using SharePoint; however, this approach necessitates a greater investment of time and effort during the development phase.

Implementing Hierarchical Data Access in PowerApps using Combined View

1. Define Hierarchy: Establish the Organisational hierarchy in a SharePoint list, indicating the manager-subordinate relationships.
2. Configure Data Access: In PowerApps, use hierarchy information to filter data accessible to the user. For example, a manager can access data for all their subordinates.

Here is a high level summary of how this can be applied:

1.  Capture the current user’s email address using: Set(CurrentUserEmail, User().Email);
2.  Retrieve the user’s reporting manager’s information with LookUp and set it to CurrentUserReportingManager.
3.  Collect the groups associated with the user into a collection named UserGroups using Filter action.
4.  Loop through UserGroups and collect relevant projects into UserProjects
5.  Gather all reporting managers into a collection ReportingManagers with Filter.
6.  Determine if the user is a manager by counting the rows in ReportingManagers and set the result to IsManager.
7.  If the user is a manager, loop through ReportingManagers and collect relevant projects into ManagerProjects.
8.  Combine User and Manager Projects: Merge UserProjects and ManagerProjects into a single collection AllProjects.
9.  Use AllProjects in your Gallery to showcase the correct data to relevant users.

File-Level Security

File-level security involves controlling access to specific files stored in SharePoint document libraries.

Implementing File-Level Security in SharePoint

1. Unique Permissions: Break the inheritance of permissions on specific files and assign unique permissions based on user roles.
2. Integrate with PowerApps: Access the SharePoint document library from PowerApps. The file-level permissions will ensure that users can only interact with files they have access to.

Conclusion

Implementing security frameworks in PowerApps using SharePoint is a crucial step in ensuring the integrity, confidentiality, and availability of your Organisation’s data. By leveraging RBAC, item-level security, column-level security, hierarchical data access and file-level security, you can build robust and secure applications that meet your security requirements. With careful planning and execution, PowerApps and SharePoint can work together to provide a secure and efficient platform for your business needs.