Authorized views let you grant fine-grained access to specific rows and columns in a BigQuery table without duplicating data.
Let’s see it in action.
Imagine you have a sales table with customer PII, sales figures, and product details. You want to give your marketing team access to sales figures and product details, but not the customer PII. You also want to give your regional sales managers access to all sales data, but only for their specific region.
Here’s a simplified sales table:
-- sales table
CREATE TABLE my_dataset.sales (
transaction_id STRING,
customer_id STRING,
customer_name STRING,
customer_email STRING,
region STRING,
product_id STRING,
quantity INT64,
price FLOAT64,
sale_date DATE
);
INSERT INTO my_dataset.sales VALUES
('txn_001', 'cust_101', 'Alice Smith', 'alice@example.com', 'North', 'prod_A', 2, 15.50, '2023-10-26'),
('txn_002', 'cust_102', 'Bob Johnson', 'bob@example.com', 'South', 'prod_B', 1, 120.00, '2023-10-26'),
('txn_003', 'cust_101', 'Alice Smith', 'alice@example.com', 'North', 'prod_C', 5, 5.00, '2023-10-27'),
('txn_004', 'cust_103', 'Charlie Brown', 'charlie@example.com', 'West', 'prod_A', 1, 15.50, '2023-10-27'),
('txn_005', 'cust_102', 'Bob Johnson', 'bob@example.com', 'South', 'prod_A', 3, 15.50, '2023-10-28');
Now, let’s create an authorized view for the marketing team. This view will expose transaction_id, region, product_id, quantity, price, and sale_date, but not customer_id, customer_name, or customer_email.
-- Authorized view for marketing team
CREATE VIEW my_dataset.marketing_sales_view AS
SELECT
transaction_id,
region,
product_id,
quantity,
price,
sale_date
FROM
my_dataset.sales;
To grant access, you don’t grant permissions on the sales table directly to the marketing group. Instead, you grant them bigquery.dataViewer (or higher) on the marketing_sales_view.
bq grant --view_as=owner my_dataset.marketing_sales_view marketing_group@example.com
When the marketing team queries my_dataset.marketing_sales_view, they can only see the columns defined in the view. They cannot access the underlying sales table directly, nor can they see the customer_id, customer_name, or customer_email columns even if they tried to join or scan the base table.
For the regional sales managers, we need a different approach. We can create a view that filters by region. Let’s say we want to give access to the "North" region sales data to north_sales_manager@example.com.
-- Authorized view for North region sales manager
CREATE VIEW my_dataset.north_sales_view AS
SELECT
transaction_id,
customer_id,
customer_name,
customer_email,
region,
product_id,
quantity,
price,
sale_date
FROM
my_dataset.sales
WHERE
region = 'North';
And grant access:
bq grant --view_as=owner my_dataset.north_sales_view north_sales_manager@example.com
Now, north_sales_manager@example.com can query my_dataset.north_sales_view and see all columns, but only for transactions in the 'North' region.
The magic here is that the permissions are checked against the view, not the base table. When a user queries an authorized view, BigQuery checks if the user has permission to access the view itself. If they do, BigQuery then executes the view’s underlying query, and the results are filtered based on the view’s definition. The user never directly interacts with the base table through this mechanism. This is crucial for security because it prevents users from bypassing the intended access controls by querying the base table directly.
Authorized views also support row-level security by incorporating WHERE clauses in their definition, as demonstrated with the regional sales manager example. You can also combine column and row filtering in a single view.
An interesting aspect of authorized views is how they handle schema changes in the base table. If you add a new column to the sales table, it won’t automatically appear in the marketing_sales_view because the view’s definition is static. You’d need to explicitly ALTER VIEW or CREATE OR REPLACE VIEW to include the new column. Conversely, if you drop a column from the base table that is not selected by the view, the view remains unaffected. However, if you drop a column that is selected by the view, the view will break, and any queries against it will fail until the view definition is updated.
The "owner" role in bq grant --view_as=owner is important. It means the user querying the view is impersonated by the view’s owner for permission checks on the underlying data. This is the default and most common behavior, but it’s worth noting that authorized views can also be configured with a specific service account to act as the identity for access checks using CREATE VIEW ... WITH SECURITY DEFINER. This is less common for interactive user access but powerful for service-to-service scenarios.
The next step in managing BigQuery access is often exploring row-level security policies directly on tables, which offer a more dynamic way to control access based on user attributes.