Insufficient permissions to the underlying data set. No Data Set Access

[adkrk]

Hello everyone,

Data Description: I have a Looker Studio report with multiple dashboards, all connected to BigQuery for underlying data. Some tables in BigQuery are base tables, while others are external. Many data sources are created as views.

The Task: I need to create a filter that ensures certain people accessing the dashboard can only see their data tied to their email.

Tried Solutions:

• I tried creating a filter on each dashboard within Looker Studio using LOWER(SESSION_USER()) AS viewer_email, and then creating a calculated field with a case clause:
  IF person.email = viewer_email THEN TRUE. This approach didn’t work.

• I tried giving BigQuery view access to specific users, but this didn’t help either.

• I attempted to filter by email in the data source Edit section, but it filters the data so that I can’t see it, and users are shown a message saying, “You don't have access to the underlying data.”

• I wanted to create a RAP (Row Access Policies), but since some of the dashboards are views or external tables, RAP cannot be created on them in BigQuery.

Summary: The task is a bit tricky, so I’m reaching out for help in finding a solution. Any suggestions or guidance would be greatly appreciated!

[lauratilton]

Sounds like your data source credentials are set to Viewer…try setting to Owner to get the Filter by Email approach to work. But yes, if your email address is not in the data source, you’ll see nothing. 

[adkrk]

Thank you for your reply! 

The issue with credentials is that when it's set to 'data owner' or 'service account,' all data is shown to all users. However, I still need to filter the data by the user accessing the report so they only see their own data. From my understanding, this should be done through viewer access, or maybe I’m missing something. But I can’t understand why they are still seeing 'No Data Set Access' even though they have data viewer rights at the BigQuery level and the report is shared with their exact email.

[lauratilton]

I haven't done much with BQ viewer access lately, but I looked up my notes from a previous project that sounds similar. I had to add the users as BQ Data Viewers (which it sounds like you've done), but also had to add them as Job Users in IAM, and also had to make sure that the filtered table was set as an authorized view on the original dataset. (Again, it's been a while since I worked on this, but maybe that will help?) I remember the permissions were tricky to get right.

[adkrk]

Yes, I’ve tried adjusting the permissions and even gave owner credentials to the service account being used, but I’m still receiving insufficient permissions messages on some data sources. I also tried comparing the working and non-working data sources side by side, with the same setups, but the results were different after all.

[Sichali1]

Hi,

I completely understand your question because I’ve worked on similar projects before. One approach that worked well for me was using this calculated field:

IF(CONTAINS_TEXT(Email_Group, DS_USER_EMAIL()), Region, "Filter")

Here’s how it works:

1. Email_Group is the column that contains user email addresses.
2. Region represents any field in your dataset that doesn’t have empty values.
3. "Filter" is a placeholder that gets excluded when you apply a global filter in your report.

DS_USER_EMAIL() in Looker Studio automatically retrieves the logged-in user's email. This makes it a powerful tool for creating personalized dashboards, ensuring that each user sees only the data relevant to them without needing manual filtering.
Hope this helps! Let me know if you have any questions

[adkrk]

Hi Sichali1,

Thank you for this thorough explanation. I’ll try this approach as well. However, the question is: which permissions should be used in the backend for data sources in BigQuery? 

[Sichali1]

Hi,

With this approach, there’s no need to assign special permissions at BigQuery data source level. Instead, in Looker Studio, configure the data source to use Owner’s Credentials. This ensures that all viewers access the data through your credentials, bypassing individual user-level restrictions while maintaining controlled access

[adkrk]

Thank you for the solution! It worked after setting up and applying the filter on each data source and adding exclusions with my own email. At first, I couldn’t see the data, even with data owner permissions.

[mars124 · Accepted solution]

Have you explored this documentation on how to filter by email in BigQuery? You can also refer to a similar post on how to enable filter by email in Looker Studio.

TLDR: Use @ DS_USER_EMAIL in your BigQuery data source.

[adkrk]

Not yet, but I’ll try that approach a bit later and let you know the result. Thank you. 

[adkrk]

Yes, that worked. Thank you for the links, I used them to finalize the setup.