How to Restrict Access in BigQuery Using Views?

In BigQuery, permissions are granted at the dataset level—not at the table or view level. If you give a user access to a dataset, they can see everything inside it: all tables and all views. 

So how can you allow a team to see only a filtered version of a table without giving them access to the full table?

The answer: use views in a separate dataset.

 

Example to understand this better

Let’s say you have an employee table in the dataset my_org. This table has sensitive information like salaries and social security numbers. You want the HR team to see full details, but give Support access to only names and contact info.

 

If you create both the table and views inside my_org, any user with access to the dataset can query both the full table and all views.

 

To restrict access properly:

·      Keep the base table in one dataset (e.g., my_org)

·      Create a view in another dataset (e.g., my_org_views)

·      Grant access to the my_org_views dataset only

 

Follow below step-by-step procedure to implement the same.

 

Step 1: Create Two Datasets

-- Dataset with the original table
CREATE SCHEMA my_org;

-- Dataset to hold restricted views
CREATE SCHEMA my_org_views;

 

Step 2: Create a Table in my_org

CREATE TABLE my_org.employee (
  emp_id INT64,
  full_name STRING,
  department STRING,
  email STRING,
  salary NUMERIC
);

 

Step 3: Insert some data into employee table.

INSERT INTO my_org.employee
VALUES 
  (1, 'Ram', 'HR', 'ram@company.com', 90000),
  (2, 'Krishna', 'Support', 'krishna@company.com', 50000);

Step 4: Print all the employee details by executing below statement.

SELECT * FROM my_org.employee;

 

 

Step 5: Create a View in my_org_views

This view hides the salary column.

CREATE VIEW my_org_views.employee_basic_view AS
SELECT 
  emp_id,
  full_name,
  department,
  email
FROM 
  my_org.employee;

 

Upon successful execution of the above statement, you can see employee table is in my_org dataset, where as employee_basic_view is in my_org_views dataset.

 

Query employee_basic_view by executing below statement.

SELECT * FROM  my_org_views.employee_basic_view

 

 

Step 5: Grant Permissions

Give Support team access to my_org_views only: 

# Example using bq CLI:
bq update --dataset --access_group=group:support-team@company.com:READER my_org_views

 

Pros and Cons of this Approach

Pros:

·      Granular control: You decide exactly what each team can see.

·      Enhanced security: Sensitive data remains hidden.

·      Clear separation: Keeps your data model clean and easy to manage.

 

Cons:

·      Slightly more setup and management.

·      Cross-dataset access may need permissions to be handled carefully (the view needs access to the underlying table).

 

 

Note:

You’ll also need to grant the view’s dataset service account permission to read the base dataset (my_org). This can be done by giving the BigQuery Data Viewer role to the view's dataset service account on the source dataset.

 

In summary, creating views in a separate dataset is a smart way to share limited data access in BigQuery. It aligns with the platform's dataset-level security model and offers better control when working with diverse teams or departments.

 

Previous                                                    Next                                                    Home