AWS Big Data Blog

Applying row-level and column-level security on Amazon QuickSight dashboards

Amazon QuickSight is a cloud-scale business intelligence (BI) service that you can use to deliver easy-to-understand insights to the people you work with, wherever they are. QuickSight connects to your data in the cloud and combines data from many different sources. On a single data dashboard, QuickSight can include AWS data, third-party data, big data, spreadsheet data, SaaS data, B2B data, and more.

QuickSight users in your organization should have access to only certain data for compliance and security reasons. Without proper integrated features to enforce row-level and column-level security, you have to develop additional solutions such as views, data masking, or encryption, or try to integrate third-party solutions around your data to enforce security.

In this post, we walk through how to implement row-level and column-level security in QuickSight.

Overview of solution

You can use row-level and column-level security to share a dashboard with several users who can only view the data that they have permissions to. For example, if you have an internal team of sales agents who contact a list of potential customers, you need a single dashboard that is accessible to all the agents but displays only the list of prospects assigned to them.

Another example could be regarding financial data. You might want to restrict access to sensitive fields to a small set of users. You could further restrict access based on some other field, such as geography. Let’s assume we have three users: A, B and C. Users A and B are admin users and have access to financial data, but user C does not. Furthermore, users A and B are admins for different countries and have access to financial data for their respective countries only.

In this post, we set up row-level and column-level security permissions in our dataset for different sets of users and experiment with grant and deny access rules. We walk you through the following steps:

  1. Upload the dataset and apply the permissions to it.
  2. Create appropriate filters and reports for users.
  3. Log in as different users and see how the dashboard’s data has been tailored just to what they are allowed to see.

Prerequisites

For this walkthrough, you should have the following prerequisites:

In the Enterprise edition of QuickSight, you can restrict access to a dataset by configuring row-level and column-level security. Only the people you shared with can see any of the data.

You can also send dashboard snapshots as emails to groups of readers, with each reader receiving customer reports as per the security configurations set on the dashboard.

Granting row-level access

For our use case, a business analyst in the marketing team creates a dataset of monthly sales in QuickSight using the file month_b2bsalesdata.xlsx. The following screenshot shows the dataset details. We use the location field to restrict user access to the data.

To grant row-level access, complete the following steps:

  1. Upload the permission file and define appropriate user access.

In the permission file, we have the location field same as our primary month_b2bsalesdata dataset. It doesn’t matter what order the fields are in. However, all the fields are case-sensitive. They must exactly match the field names and values. You must have at least one field that identifies either users or groups. The field you use for users or groups can have any name you choose.

If you’re specifying groups, use only QuickSight groups or Microsoft AD groups.

The following screenshot shows the dataset rules (permissions) for row-level security. Here we have defined access for users to different states and cities.

  1. On the Datasets tab, choose the dataset you want to enable row-level security for.
  2. Choose Row-level security this dataset.

  1. For Selected data set rules, select the appropriate permission file (for this post, permissions.csv).
  2. For Permission Policy, you can grant permissions. For this post, select Grant access to data set.

By default, access is denied for all users, and you use the permissions file to allow access to the data. For example, if I don’t put a row for users in the permissions file, they don’t see any data in the report. If I put blanks (empty strings “”) for users in the permissions file, they see everything.

  1. Choose Apply data set.

Testing row-level restrictions

You can now test the restrictions by logging in as various users.

  1. Log in as the user AlejandroRosalez.

This user has access to data where the state is California or Texas, and the city is Los Angeles or Fort Worth. The following screenshot shows the data visible to AlejandroRosalez.

  1. Log in using SaanviSarkar.

The following screenshot shows the data visible to SaanviSarkar. They can see data only for any city, but only if the state is Texas.

  1. Log in using MarthaRivera.

The following screenshot shows the data visible to MarthaRivera. Martha can see the data for any city or state.

  1. Log in using the workshop user, which isn’t present in the permissions.csv file.

The following screenshot shows that no data is visible to the workshop user.

Granting column-level access

We can also grant access at the column level to specific users and groups.

  1. On the QuickSight console, on the Datasets page, choose the dataset you want to enable column-level security for.
  2. Choose Column-level security.

By default, all users and groups have access to the data. Column-level security allows you to manage access to specific columns in your dataset.

You can restrict column access to particular users and groups. In the following screenshot, I’m restricting access for the sales column only to PauloSantos and ZhangWei.

Testing column-level access

To test these additional restrictions, log in as the Zhangwei user.

The following screenshot shows the data visible to Zhangwei user. They can see all the columns.

If you log in as the workshop user, Sales is marked with the Restricted symbol in the Fields list pane. This user doesn’t have access to view or use the Sales field in any visual within the analysis.

Pivot tables and regular tables

If the column is in the Values field, you can see Not Authorised in pivot tables and regular tables.

In the following screenshots, you can see Sales is in the Values field.

If the column is in the Group by, Row, or Column field, you get an error message saying You can’t access this visual because this field is restricted : Sales.

In the following screenshots, you can see Sales is in the Column and Group by fields.

Cleaning up

To avoid incurring future charges, delete the QuickSight users and Enterprise account.

Conclusion

This post showed how QuickSight allows you to set up row-level and column-level security at the user and group level. QuickSight also supports emailing dashboards with row-level security enabled. You can send dashboard snapshots as emails to groups of readers, and each reader receives custom reports as per the security configurations set on the dataset. For more information, see Sending Reports by Email.

You can try this solution for your own use cases. If you have comments or feedback, please leave them in the comments.

About the Author

Niyati Upadhyay is a Solutions Architect at AWS. She joined AWS in 2019 and specializes in building and supporting Big Data solutions that help customers analyze and get value out of their data.