CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerygoogle-iamgoogle-cloud-iam
Mobeen
Mobeen

Reputation: 985

How to restrict BigQuery's dataset access for everyone having (Project level Viewer) role

I want to restrict access for 1 dataset in BigQuery so that no one except 1 user(me) is able to view that dataset. From the documentation, it seems that this isn't possible unless I change my project-level permissions and remove the Editor role from there.

Currently, N(10-12) people have project-level Editor access on my Google project. They all interact with multiple services there from time to time. I would like to keep it that way, or at least have a new Role with almost the same permissions.

From my current understanding of Google IAM permissions, we can do this by giving resource-level access to users. But If I give BigQuery Editor role to a user, they are still able to view/access all datasets.

To counter this, I would have to give more granular level access to each user, Which means giving individual dataset level access to all users. Again that means If I have 10/12 people and 10/12 datasets this can get hard to manage.

Is there a better way to do it, I mean somehow I can specify all Editors or Custom Role can access everything like they are already doing except this particular dataset

Upvotes: 3

Views: 3300

Answers (3)

Pramod Gupta
Pramod Gupta

Reputation: 41

In case you have default policy where other team members will get access to table automatically (Principal is inherited from another policy) via IAM.

You can follow this steps to restrict access to dataset or table using Row Level Security and Hidden Dataset.

Step 1. You can create Hidden dataset in console using _ key before dataset name, this will make dataset hidden.

Step 2. Create Row Level Security on table using this query

CREATE ROW ACCESS POLICY your_filter_name
ON `projectid._dataset_name.table name`
GRANT TO ('user:[email protected]')
FILTER USING (True);

Upvotes: 1

Kevin Glasson
Kevin Glasson

Reputation: 408

I think the solution here is to have Terraform (or something else) manage the resources for you.

You can develop a module that creates the appropriate things for a user e.g. a dataset, a bucket, some perms, a service account etc.

That way all you need to do is add another user to your list and re-deploy. The other additional benefit here is that you can use the repo where the TF is stored as a source of truth.

Upvotes: 0

Christopher Rodriguez Conde
Christopher Rodriguez Conde

Reputation: 899

You cannot define access control on table. Only down to dataset level, but there are other options that can help you:

As mentioned per BigQuery's documentation about creating datasets:

Datasets that begin with an underscore are hidden from the navigation pane in the BigQuery web UI and the classic web UI. You can still query tables and views in these datasets even though they are not visible.

You can give yourself bigquery.datasets.create in order to create these "hide" datasets away from this users or assign the following Cloud IAM roles to yourself, as specified here:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.user
  • bigquery.admin

Another solution is to create authorized views within BigQuery. As stated here:

An authorized view allows you to share query results with particular users and groups without giving them access to the underlying tables. You can also use the view's SQL query to restrict the columns (fields) the users are able to query.

Finally I attach a post about how to control access to BigQuery at row level with groups, that can help you get a better idea on how row access level works.

I will also attach this question about giving users access to different rows without creating separate views here.

I hope this helps.

Upvotes: 1

Related Questions