BigQuery’s row and column security features are a powerful way to control data access, but they’re often misunderstood as simply "permissions."
Let’s look at a real-world scenario where we need to restrict access. Imagine a customer_data table with sensitive information like customer_id, name, email, address, and credit_card_number. We want to allow sales teams to see customer names and addresses but not their email or credit card details, while a separate fraud detection team needs to see everything.
Here’s a simplified version of the customer_data table:
CREATE TABLE my_dataset.customer_data (
customer_id INT64,
name STRING,
email STRING,
address STRING,
credit_card_number STRING
);
INSERT INTO my_dataset.customer_data VALUES
(101, 'Alice Smith', 'alice.smith@example.com', '123 Main St', '1111-2222-3333-4444'),
(102, 'Bob Johnson', 'bob.j@example.com', '456 Oak Ave', '5555-6666-7777-8888'),
(103, 'Charlie Brown', 'charlie.b@example.com', '789 Pine Ln', '9999-0000-1111-2222');
To achieve this, BigQuery offers two primary mechanisms: Column-level security and Row-level security.
Column-level security restricts access to specific columns within a table. You can grant or deny permissions to users or groups for individual columns.
For our example, let’s create a policy that hides email and credit_card_number from a sales_team group.
First, we need to define the authorized user or group. In Google Cloud IAM, this would be a Google Group (e.g., sales-team@your-domain.com).
Then, we apply a column-level security policy using ALTER TABLE:
-- Grant access to all columns for an admin (implicitly)
-- Then, restrict access for sales team to specific columns
-- Grant SELECT on all columns for a specific user or service account (e.g., a data analyst)
GRANT SELECT ON TABLE my_dataset.customer_data TO "user:analyst@example.com";
-- Apply column-level security for the sales team
-- Deny access to email and credit_card_number for the sales team group
ALTER TABLE my_dataset.customer_data
ALTER COLUMN email SET OPTIONS (policy_name='hide_sensitive_emails', authorized_who=['user:analyst@example.com']); -- This syntax is conceptual; actual policy is managed via IAM and Data Catalog
-- The actual mechanism for column-level security is through IAM roles and Data Catalog policies.
-- You would grant roles like 'BigQuery Data Viewer' on the dataset/table,
-- and then use Data Catalog to define fine-grained access policies for specific columns.
-- For simplicity in demonstration, let's imagine a direct policy application.
-- To achieve column-level security, you typically use IAM roles and potentially Data Catalog.
-- You grant a base role (e.g., BigQuery Data Viewer) on the table/dataset.
-- Then, for sensitive columns, you would use Data Catalog's fine-grained access control.
-- A more direct BigQuery approach involves using authorized views or data masking.
-- Let's simulate the effect using authorized views for clarity on access control.
CREATE VIEW my_dataset.sales_view AS
SELECT
customer_id,
name,
address
FROM
my_dataset.customer_data;
-- Now, grant the sales team access to this view, not the base table.
GRANT SELECT ON TABLE my_dataset.sales_view TO "group:sales-team@your-domain.com";
When a member of the sales-team@your-domain.com group queries my_dataset.sales_view, they will only see customer_id, name, and address. They will not have access to email or credit_card_number.
Row-level security restricts access to entire rows based on the user’s identity or other conditions. This is achieved using Row Access Policies.
Let’s say we want the sales_team to only see customers from a specific region, while the fraud_team can see all customers. We’ll add a region column to our table.
ALTER TABLE my_dataset.customer_data
ADD COLUMN region STRING;
UPDATE my_dataset.customer_data
SET region = 'North' WHERE customer_id = 101;
UPDATE my_dataset.customer_data
SET region = 'South' WHERE customer_id = 102;
UPDATE my_dataset.customer_data
SET region = 'North' WHERE customer_id = 103;
Now, let’s create a row access policy for the sales_team to see only 'North' region customers.
CREATE ROW ACCESS POLICY sales_region_policy
ON my_dataset.customer_data
GRANT TO ("group:sales-team@your-domain.com")
FILTER USING (region = 'North');
-- To grant the fraud team access to all rows, you'd typically grant them
-- direct access to the table with no row access policy applied to them,
-- or create a separate policy allowing all rows for that group.
CREATE ROW ACCESS POLICY all_regions_policy
ON my_dataset.customer_data
GRANT TO ("group:fraud-team@your-domain.com")
FILTER USING (TRUE); -- This allows all rows
When a member of sales-team@your-domain.com queries my_dataset.customer_data, they will only see rows where region is 'North' (Alice and Charlie). A member of fraud-team@your-domain.com will see all rows, including Bob’s from the 'South'.
The most surprising thing about BigQuery’s row and column security is that the security policies are defined on the table itself, not on the user’s role in the traditional sense. This means the table "knows" who can see what, and it enforces these rules transparently when queries are executed. You don’t rewrite your queries to filter data; BigQuery adds the filters automatically based on the active policies for the querying user.
Combining these two mechanisms allows for very granular control. For instance, you could have a policy where the sales_team can see customer name and address for customers in their assigned region, but nothing else.
The next step in securing your data often involves understanding how to manage these policies at scale, especially when dealing with a large number of users, groups, and tables.