Skip to main content

Column and Row Level Security in BigQuery

· 3 min read
Hayssam Saleh
Hayssam Saleh
Starlake Core Team Member

Data exposition strategies​

Data may be exposed using views or authorized views and more recently using Row / Column level security.

Historically, to restrict access on specific columns or rows in BigQuery, one can create a (authorized) view with a SQL request like the one below:

CLS / RLS using Views

BigQuery Views require to grant access for the end users to the table on top of which the view is created. To bypass that limitation, BigQuery provide Authorized views. However, Authorized views come with the following restrictions:

  1. The underlying table is accessed through the authorized view where the end user is impersonated, loosing thus at the table level, the identity of the user making the request. Impersonation

  2. Each restriction policy require to define a specific authorized view making it difficult to identify who has access to what ? Multiplication of Authorized Views

  3. Authorized views need to be updated whenever a schema evolution on the underlying table bring in a sensitive field that need to be excluded or a field that need to be included in the view. In the example below, the new column "description" need to be added to the authorized view if we want it . Multiplication of Authorized Views

That's where Row Level Security and Column Level security features natively supported by BigQuery come in.

BigQuery Row Level Security​

Row Level Security restrict access to the rows based on the conditions set in the where clause using the custom SQL statement below:

RLS

Big Query Column Level Security​

Column level security in BigQuery is managed using a taxonomy. This taxonomy is a hierarchy of policy tags describing the table attributes or other resources. By assigning access rights to a tag, we restrict access to any resource tagged using this specific tag and this applies to BigQuery table fields.

In our example, restricting access to specific user/group/sa to the column price require the following steps:

  1. In Cloud Data Catalog/Policy Tags, create a Taxonomy. Note that Enfore access control should be checked.

CLS Taxonomy

  1. Assign permissions for each policy tag you defined

CLS Access

  1. Tag restricted columns in the BigQuery schema editor. CLS Assign
tip

Assigning policy tags may be done using the bq load/update command line tool

BigQuery RLS/CLS benefits​

Using BigQuery row and column level security features bring several benefits:

  • There is no need to create extra views
  • Users use the same name for the table but with different access rights
  • A company-wide taxonomy is defined allowing better Data Management
  • Access rights to a new column in the table are automatically handled

A word about RLS and CLS in Starlake​

Ingesting Data into BigQuery cannot be considered complete without taking into account the access level restrictions on the target table. Starlake will handle for you all the scripting required to secure BigQuery rows and columns using a YAML declarative syntax to make sure that your tables are secured in BigQuery:

Declarative Row Level & Column Level Security
  - name: "PRODUCT"
    rls:
      - name: "my-rls"
        predicate: "category like 'Food'"
        grants:
          - "user:me@company.com"
          - "group:financegroup@company.com"
          - "sa:serviceacount@gserviceaccount.com"
    attributes:
      - name: "id"
        accessPolicy: PII