Row-Level Security in BigQuery Using Views and SESSION_USER()

In collaborative environments, not every user should be able to view every row of a dataset. For example, a manager should see only their team's records, or users should see only their own transactions.

BigQuery doesn’t natively support fine-grained row-level security like some traditional RDBMS, but with a clever use of views, SESSION_USER(), and join tables, we can simulate row-level access control effectively.

 

Let’s say, you're building an internal dashboard. Each user should only see rows that belong to them (based on their email address). You’ll create:

 

·      A base table with sensitive data

·      A mapping table defining which user has access to which row

·      A view that restricts data based on the logged-in user

 

Step 1: Create the Main Dataset

CREATE SCHEMA row_security_demo;

 

Step 2: Create a Base Table with Sensitive Data

CREATE TABLE row_security_demo.customer_orders (
  order_id INT64,
  customer_name STRING,
  product STRING,
  amount NUMERIC
);

Step 3: Insert some data into customer_orders table

INSERT INTO row_security_demo.customer_orders
VALUES
  (1, 'Ram', 'Laptop', 1200),
  (2, 'Krishna', 'Smartphone', 800),
  (3, 'Chamu', 'Tablet', 400);

Step 4: Create a Table to Manage Row-Level Access, and insert user emails into it.

CREATE TABLE row_security_demo.order_access_map (
  order_id INT64,
  user_email STRING
);

// Assume multiple users can accesss same row

INSERT INTO row_security_demo.order_access_map
VALUES
  (1, 'ram@example.com'),
  (2, 'krishna@example.com'),
  (3, 'chamu@example.com'),
  (3, 'ram@example.com');

Step 5: Create a View with SESSION_USER() Filter

CREATE VIEW row_security_demo.customer_orders_view AS
SELECT
  co.*
FROM
  row_security_demo.customer_orders co
JOIN
  row_security_demo.order_access_map map
ON
  co.order_id = map.order_id
WHERE
  map.user_email = SESSION_USER();

Here:

·      co is an alias for the table row_security_demo.customer_orders

·      The * means “select all columns”

·      So co.* means "select all columns from the customer_orders table"

 

What’s Happening?

·      The view joins the base table with the mapping table

·      It filters rows using SESSION_USER(), which returns the currently logged-in user’s email

·      Only matching rows are shown to the user

 

How to Test It?

To test this:

·      Share the view with test users using IAM

·      When users run SELECT * FROM customer_orders_view, they will only see rows mapped to their email in order_access_map

 

Advantages

·      Works with standard BigQuery

·      No external tools or paid features needed

·      Easy to scale for multi-user scenarios

·      Keeps logic centralized in the view

 

In summary, this approach to row-level security is a simple, powerful, and native way to protect sensitive rows in BigQuery. By externalizing access logic to a mapping table and leveraging SESSION_USER(), you can maintain flexibility and scalability without losing control.

 

 

Previous                                                    Next                                                    Home