Row-level security is restricted data access based on users with a user-based filter mechanism. A user-based filter defines the restrictions for each user, helping to avoid re-creating the same dashboard for each user; you can maintain one dashboard for all users by restricting other users’ access. In a previous blog post (“Row-Level Security with User-Based Filters”), we explained how row-level security works with user-based filtering, and we discussed its benefits in detail by looking at a simple use case with illustrations.
In this blog post, we will explore how to configure a user-based filter in Bold BI and how to embed a dashboard to achieve row-level security in your application.
How to create user-based filtering with Bold BI
After creating a dashboard for embedding in your application, you can configure user-based filtering through a data source designer and configure a user-based filtering dialog.
To configure user-based filtering, edit the data source and click the Configure User Filters icon on the toolbar.
The Configure User Filters window will open, as shown in the following image.
You can configure user-based filtering in the following modes:
Before configuring the user filter, you need to add users and groups to provide permission to access your dashboard. You can check out the video “Managing Users, Groups and Permissions in Bold BI” to do this.
Now let’s see how to configure a user-based filter in Manual mode.
Configure user-based filter in Manual mode
Manual mode is suitable for static data and data presented as columns within data to be used for the dashboard. The columns could be for email addresses or full names, for example.
Let’s consider a school management dashboard, which helps administration and faculty monitor student performance and faculty details. At some point, management decides to provide access to the dashboard for each faculty branch so they can visualize their specific branch’s data and make necessary changes in their workflow.
While in Manual mode, select branch_name to filter the column based on a branch. Select Science for faculty member Alyssa Harris to visualize science branch data only. You can restrict data to each faculty branch as shown as in the following image.
You can ensure the correct user filter is applied while previewing the dashboard. View the user list from the Preview as drop-down list and select a faculty member to view their corresponding branch data by restricting other data.
The following dashboard screenshot shows the administrator view, which shows all branch data.
The following screenshot shows the Faculty1 view, and it shows only that branch’s data.
You can see the Science branch alone in the grid widget for Alyssa Harris. Check out our user filter manual documentation for more details on configuring a filter.
Now let’s see how to configure a user-based filter in Data Source mode.
Configure user-based filter in Data Source mode
Data Source mode is suitable for cases where applied filters are more dynamic, and data used in the dashboard does not have any user information in any of its columns. If you have a table that has the mapping details for the users—such as email, full name, or group name—then you can use Data Source mode. This will provide much more control over the data shown in the dashboard, so if a new user is added or removed, these changes need to be made in the mapping table alone; you don’t have to reconfigure the user-based filter for this.
In a hospital management dashboard, you can monitor the quality of care, operational activities, and financial activities. KPIs like average length of stay of a patient directly impact the costs and the workload of your staff. Also, you can monitor details related to bed occupancy rate, upcoming appointments per doctor, outpatient and in-patient count with respect to division, and so on.
The following dashboard image shows the Doctor1 view, showing only hospital and specialization data.
You can see the Cardiologist specialization alone in the grid widget and Hospital_2 data in the drop-down widget for Laurence Moos.
To achieve this view, configure using Data Source mode in the user filter window. Select the identity as Full Name in the Identity Type field and Doctor_Name as the column to map the user based on the name column.
You can set a filter based on multiple columns, so choose Hospital_Name and Specialization to map the column from the Mapping data source to Hospital Management data source, and then check the column in the mapping data source to filter data in the Hospital Management data source, as shown as following screenshot.
After configuring the user-based filter, you can preview what that user, or users in that group, will be able to see in the dashboard.
With this mode you can have a mapping data source that will hold the details of the user along with the hospital that they serve. So, when a doctor appointment is changed, only the mapping data source data needs to be updated. The dashboard will reflect the changes automatically.
Refer to the user filter data source documentation for more details about configuration.
Let’s see how to configure a user-based filter with Query mode.
Configure user-based filter with Query mode
Query mode is similar to Data Source mode. It allows a user to write their own query to fetch data for filtering. Query mode provides more control over data that is used for mapping. You can write your own SQL query that will be executed toward the connection of the data source selected from the Mapping Data Source list.
A financial analysis dashboard shows an overview of revenue and expense details, online versus retail sales comparison, regional revenue, and predicted revenue for the next three months.
In Query mode, select the customer table, map the customer name, and choose the region to filter the data in the RevenueExpense data source.
The following dashboard screenshot shows the administrator view, which shows all regional data.
The following screenshot shows the user1 view, displaying only a specific region’s data.
You can see the Missouri region alone in the grid widget for Paolo Pipes.
With Query mode, you can fetch any table from a current database that is used in mapping a data source and map the column to the current data source. Check out our user filter query documentation for more details about configuring Query mode.
We have seen how to configure user-based filters in Bold BI. Once the dashboard is configured and published, you can embed it in any web application. We discussed in detail on how to integrate dashboards with the embed SDK into ASP.NET MVC and ASP.NET Core applications in a previous blog post. Refer to our documentation to find the steps to embed the Bold BI dashboard in a supported application.
Now, let’s see an example of how row-level security is applied to an embedded dashboard.
A dashboard is embedded in my ASP.NET Core application, as shown in the following image.
I applied a row-level filter to nine employees to restrict visible data from other users.
The following screenshot shows the user1 view, which shows six employees’ data.
The following screenshot shows the user2 view, displaying only its own data.
You can see Laura Callahan in the grid widget for Laura Callahan.
Finally, you can achieve row-level security for your embedded dashboard based on user-based filtering configuration with Bold BI Embedded.
I hope this article provided you with needed information about user-based filtering imposing row-level security in embedded dashboards with Bold BI. If you have any questions about user-based filtering, please post them as comments in this blog. You can also contact us by submitting your questions through the Bold BI website or, if you already have an account, you can log in.