Row-level and column-level security

To restrict which rows principals can see and to mask or block sensitive column values without duplicating underlying Cloud Storage files, configure BigQuery fine-grained access control (FGAC) on Apache Iceberg tables managed by the Lakehouse runtime catalog. FGAC includes row-level security and column-level security with dynamic data masking. This document shows you how to create row-level access policies and apply data policies directly to columns.

You attach and manage row-level access policies and column data policies on Apache Iceberg tables by running GoogleSQL data definition language (DDL) statements in BigQuery that use the four-part P.C.N.T (Project.Catalog.Namespace.Table) table identifier. When your catalog uses credential vending mode, the Apache Iceberg REST catalog endpoint enforces a credential vending guardrail on FGAC-protected tables. The endpoint returns a 403 Forbidden HTTP status code for direct Cloud Storage credential requests unless the calling principal has unmasked (RAW_DATA_ACCESS_POLICY) access to every protected column and, if the table has row-level access policies, unfiltered row access (FILTER USING (TRUE)).

Limitations

Fine-grained access control on Apache Iceberg tables managed by the Lakehouse runtime catalog is subject to the following limitations, in addition to the standard column data policy limitations and row-level security limitations:

  • SQL DDL only: You can attach, update, or detach column data policies and create or drop row-level access policies only through BigQuery GoogleSQL DDL statements (CREATE TABLE, ALTER TABLE ... ADD COLUMN, ALTER TABLE ... ALTER COLUMN ... SET OPTIONS, CREATE ROW ACCESS POLICY, and DROP ROW ACCESS POLICY). Managing these table bindings using the Google Cloud console, the BigQuery API (tables.patch), the bq command-line tool, or Terraform isn't supported.
  • Four-part P.C.N.T table identifier required: When you reference an Apache Iceberg table managed by the Lakehouse runtime catalog in DDL statements or queries, you must use the four-part P.C.N.T (PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME) format.
  • Policy tags and data governance tags aren't supported: Data Catalog policy tags (and taxonomies) and Resource Manager data governance tags aren't supported on Apache Iceberg tables in the Lakehouse runtime catalog. To enforce column-level security and data masking, you must apply data policies directly to columns.
  • Subqueries in row-level access policies aren't supported: Row-level access policies on Apache Iceberg tables in the Lakehouse runtime catalog don't support subqueries in the FILTER USING filter expression.
  • Credential vending guardrail for open source engines: On catalogs configured with credential vending mode, the Apache Iceberg REST catalog endpoint returns a 403 Forbidden HTTP status code when a client, such as an open source engine, requests temporary Cloud Storage credentials for an FGAC-protected table. To obtain vended credentials, the calling principal must have unmasked (RAW_DATA_ACCESS_POLICY) access to every protected column and, if the table has row-level access policies, unfiltered row access (FILTER USING (TRUE)). For configuration steps, see Configure open source engine credentials for FGAC-protected tables.
  • End-user credentials mode: On catalogs that don't use credential vending mode, BigQuery enforces row-level and column-level access policies only on queries that run in BigQuery, not when principals read the underlying Cloud Storage files directly. To prevent principals from bypassing fine-grained access controls by reading storage files directly, use credential vending mode.
  • Federated catalogs aren't supported: Fine-grained access control (row-level security and column data policies) isn't supported on federated catalogs or external tables accessed using cross-cloud connections.

Before you begin

  1. Verify that billing is enabled for your Google Cloud project.

  2. Enable the BigLake and BigQuery Data Policy APIs, if any are not already enabled.

    Roles required to enable APIs

    To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.

    Enable the APIs

  3. Set up the Lakehouse runtime catalog with the Apache Iceberg REST catalog endpoint in credential vending mode.
  4. Create an Apache Iceberg table.

Required roles

Managing and querying fine-grained access control policies on Apache Iceberg tables requires Identity and Access Management (IAM) permissions on both the Lakehouse table (biglake.*) and the BigQuery data policy or query job (bigquery.*).

To get the permissions that you need to configure and query row-level and column-level security on Apache Iceberg tables, ask your administrator to grant you the following IAM roles:

  • Create, update, delete, or grant access on a data policy:
  • Attach or detach a column data policy on an Apache Iceberg table:
  • Create, update, list, or delete a row-level access policy on an Apache Iceberg table:
  • Query an FGAC-protected Apache Iceberg table:

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to configure and query row-level and column-level security on Apache Iceberg tables. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to configure and query row-level and column-level security on Apache Iceberg tables:

  • Create, update, delete, or grant access on a data policy:
    • bigquery.dataPolicies.create on the project
    • bigquery.dataPolicies.update on the data policy
    • bigquery.dataPolicies.delete on the data policy
    • bigquery.dataPolicies.get on the data policy
    • bigquery.dataPolicies.setIamPolicy on the data policy
    • bigquery.jobs.create on the query project
  • Attach a data policy to an Apache Iceberg table column:
    • biglake.tables.setColumnDataPolicy on the table (or namespace on CREATE TABLE)
    • biglake.tables.update on the table (or biglake.tables.create on CREATE TABLE)
    • bigquery.dataPolicies.get on the data policy
    • bigquery.dataPolicies.attach on the data policy
    • bigquery.jobs.create on the query project
  • Detach a data policy from an Apache Iceberg table column:
    • biglake.tables.setColumnDataPolicy on the table
    • biglake.tables.update on the table
    • bigquery.jobs.create on the query project
  • Create or update a row-level access policy:
    • biglake.rowAccessPolicies.create on the table
    • biglake.rowAccessPolicies.update on the table
    • biglake.rowAccessPolicies.setIamPolicy on the table
    • biglake.tables.getData on the target table
    • bigquery.jobs.create on the query project
  • List or view row-level access policies:
    • biglake.rowAccessPolicies.list on the table
    • biglake.rowAccessPolicies.get on the table
    • biglake.rowAccessPolicies.getIamPolicy on the table
  • Delete one or all row-level access policies:
    • biglake.rowAccessPolicies.delete on the table
    • biglake.rowAccessPolicies.setIamPolicy on the table
    • biglake.rowAccessPolicies.list on the table (when dropping all policies)
    • bigquery.jobs.create on the query project
  • Query an FGAC-protected Apache Iceberg table:
    • biglake.tables.get on the table
    • biglake.tables.getData on the table
    • bigquery.jobs.create on the query project

You might also be able to get these permissions with custom roles or other predefined roles.

In addition to table-level IAM permissions, the fine-grained policies on the table must also authorize querying principals:

  • Column-level security: The principal must be granted FINE_GRAINED_READ in the grantees list of a RAW_DATA_ACCESS_POLICY (for unmasked values) or a DATA_MASKING_POLICY (for masked values) attached to the queried column.
  • Row-level security: The principal must be included in the grantee_list of a row-level access policy on the table, which automatically grants the principal the system-managed roles/biglake.filteredDataViewer role. This role contains the biglake.rowAccessPolicies.getFilteredData permission.

In addition to table-level IAM permissions, the fine-grained policies on the table must also grant access to querying principals:

  • Column-level security: The principal must be granted FINE_GRAINED_READ in the grantees list of a RAW_DATA_ACCESS_POLICY (for unmasked values) or a DATA_MASKING_POLICY (for masked values) attached to the queried column.
  • Row-level security: The principal must be included in the grantee_list of a row-level access policy on the table, which automatically grants the principal the system-managed roles/biglake.filteredDataViewer role. This role contains the biglake.rowAccessPolicies.getFilteredData permission.

Configure column-level security and data masking

You configure column-level access control and dynamic data masking on Apache Iceberg tables in the Lakehouse runtime catalog by creating BigQuery data policies and attaching them directly to table columns by using GoogleSQL DDL. For more information about general concepts and masking rule definitions, see Introduction to data masking and Mask data by applying data policies to a column.

Create data policies

A data policy defines either a data masking rule (DATA_MASKING_POLICY) or unmasked raw data access (RAW_DATA_ACCESS_POLICY). You can create data policies using GoogleSQL DDL or the BigQuery Data Policy API v2. Depending on your use case, run one of the following statements:

  • To create a data policy with a predefined masking expression (such as SHA256 or ALWAYS_NULL), run the CREATE DATA_POLICY statement:

    CREATE DATA_POLICY IF NOT EXISTS `PROJECT_ID.region-LOCATION.MASKING_POLICY_NAME`
    OPTIONS (
        data_policy_type = "DATA_MASKING_POLICY",
        masking_expression = "SHA256"
    );
    
  • To create a raw data access policy that authorizes principals to read unmasked column values, set data_policy_type to RAW_DATA_ACCESS_POLICY:

    CREATE DATA_POLICY IF NOT EXISTS `PROJECT_ID.region-LOCATION.RAW_POLICY_NAME`
    OPTIONS (
        data_policy_type = "RAW_DATA_ACCESS_POLICY"
    );
    

Replace the following:

  • PROJECT_ID: Your Google Cloud project ID.
  • LOCATION: The region or multi-region location of the data policy (for example, us or us-central1).
  • MASKING_POLICY_NAME: A unique name for the data masking policy.
  • RAW_POLICY_NAME: A unique name for the raw data access policy.

Grant or revoke fine-grained read access

When you apply data policies directly to columns, the grantees list on the data policy controls query-time read access. In GoogleSQL, you grant or revoke the FINE_GRAINED_READ privilege on the data policy:

  • Granting FINE_GRAINED_READ on a DATA_MASKING_POLICY gives the principal masked read access to columns protected by that policy.
  • Granting FINE_GRAINED_READ on a RAW_DATA_ACCESS_POLICY gives the principal unmasked (raw) read access to columns protected by that policy.
  • User: principal://goog/subject/USER_EMAIL
  • Service account: principal://iam.googleapis.com/projects/-/serviceAccounts/SA_EMAIL
  • Google Group: principalSet://goog/group/GROUP_EMAIL

To manage fine-grained read access on a data policy, run one of the following statements:

To grant fine-grained read access to principals on a data policy, run the GRANT FINE_GRAINED_READ statement:

GRANT FINE_GRAINED_READ ON DATA_POLICY `PROJECT_ID.region-LOCATION.DATA_POLICY_NAME`
TO "principal://goog/subject/USER_EMAIL",
    "principalSet://goog/group/GROUP_EMAIL";

To revoke fine-grained read access from a principal, run the REVOKE FINE_GRAINED_READ statement:

REVOKE FINE_GRAINED_READ ON DATA_POLICY `PROJECT_ID.region-LOCATION.DATA_POLICY_NAME`
FROM "principal://goog/subject/USER_EMAIL";

Replace the following:

  • PROJECT_ID: your Google Cloud project ID.
  • LOCATION: the region or multi-region location of the data policy.
  • DATA_POLICY_NAME: the name of the data masking or raw data access policy.
  • USER_EMAIL: the email address of the user account.
  • GROUP_EMAIL: the email address of the Google Group.

Attach data policies to columns with P.C.N.T DDL

Attach data policies to columns on your Apache Iceberg table in the Lakehouse runtime catalog by using the four-part P.C.N.T (PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME)

identifier. Depending on your use case, run one of the following statements:

  • To attach data policies to an existing column by appending (+=) to the column's data_policies option, run the ALTER TABLE ... ALTER COLUMN SET OPTIONS statement:

    ALTER TABLE `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`
    ALTER COLUMN COLUMN_NAME SET OPTIONS (
      data_policies += ["{'name':'PROJECT_ID.region-LOCATION.MASKING_POLICY_NAME'}",
                        "{'name':'PROJECT_ID.region-LOCATION.RAW_POLICY_NAME'}"]
    );
    
  • To add a new column with a data policy attached, run the ALTER TABLE ... ADD COLUMN statement:

    ALTER TABLE `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`
    ADD COLUMN COLUMN_NAME STRING OPTIONS (
      data_policies = ["{'name':'PROJECT_ID.region-LOCATION.MASKING_POLICY_NAME'}"]
    );
    
  • To create a new Apache Iceberg table with column data policies attached, run the CREATE TABLE statement:

    CREATE TABLE `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME` (
      id INT64,
      COLUMN_NAME STRING OPTIONS (
        data_policies = ["{'name':'PROJECT_ID.region-LOCATION.MASKING_POLICY_NAME'}",
                         "{'name':'PROJECT_ID.region-LOCATION.RAW_POLICY_NAME'}"]
      )
    );
    

Replace the following:

  • PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME: the four-part P.C.N.T identifier of the Apache Iceberg table.
  • COLUMN_NAME: the name of the column to protect.
  • LOCATION: the region or multi-region location of the data policy.
  • MASKING_POLICY_NAME and RAW_POLICY_NAME: the names of the data policies to attach.

Detach or replace column data policies

To remove or replace data policies on a column in an Apache Iceberg table, use one of the following statements with the four-part P.C.N.T table identifier:

  • To remove all data policies from a column, set data_policies to an empty array ([]):

    ALTER TABLE `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`
    ALTER COLUMN COLUMN_NAME SET OPTIONS (
      data_policies = []
    );
    
  • To replace existing data policies on a column with a new list, use the = operator instead of the += operator:

    ALTER TABLE `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`
    ALTER COLUMN COLUMN_NAME SET OPTIONS (
      data_policies = ["{'name':'PROJECT_ID.region-LOCATION.NEW_POLICY_NAME'}"]
    );
    
  • To delete a data policy after detaching it from all table columns that reference it, run the DROP DATA_POLICY statement:

    DROP DATA_POLICY IF EXISTS `PROJECT_ID.region-LOCATION.MASKING_POLICY_NAME`;
    

Replace the following:

  • PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME: the four-part P.C.N.T identifier of the Apache Iceberg table
  • COLUMN_NAME: the name of the column
  • LOCATION: the region or multi-region location of the data policy
  • NEW_POLICY_NAME and MASKING_POLICY_NAME: the names of the data policies to attach or delete.

Configure row-level security

You can restrict access to specific rows in an Apache Iceberg table managed by the Lakehouse runtime catalog by creating row-level access policies with GoogleSQL DDL statements that use the four-part P.C.N.T table identifier. For more information about general row-level security concepts and filter semantics, see Use row-level security.

Create or update a row-level access policy

When you configure row-level security on a table, create a policy with a FILTER USING (TRUE) clause for administrators and writer service accounts that need full row access, and then create filtered policies for specific user groups.

  1. Grant full row access (FILTER USING (TRUE)) to table administrators or writer service accounts:

    CREATE OR REPLACE ROW ACCESS POLICY ADMIN_POLICY_NAME
    ON `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`
    GRANT TO ("group:ADMIN_GROUP_EMAIL")
    FILTER USING (TRUE);
    
  2. Create a row-level access policy that filters rows based on a predicate expression:

    CREATE OR REPLACE ROW ACCESS POLICY FILTER_POLICY_NAME
    ON `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`
    GRANT TO ("user:USER_EMAIL", "group:ANALYST_GROUP_EMAIL")
    FILTER USING (FILTER_EXPRESSION);
    

    Replace the following:

    • ADMIN_POLICY_NAME and FILTER_POLICY_NAME: Unique names for the row-level access policies on the table.
    • PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME: The four-part P.C.N.T identifier of the Apache Iceberg table.
    • ADMIN_GROUP_EMAIL, USER_EMAIL, and ANALYST_GROUP_EMAIL: The email addresses of the Google Groups or users granted row access.
    • FILTER_EXPRESSION: A Boolean filter expression evaluated like a WHERE clause (for example, region = 'APAC').

Delete row-level access policies

To remove row-level access policies from an Apache Iceberg table, run one of the following statements:

  • To delete a specific row-level access policy when other row-level access policies remain on the table, run the DROP ROW ACCESS POLICY statement:

    DROP ROW ACCESS POLICY IF EXISTS FILTER_POLICY_NAME
    ON `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`;
    
  • To delete the last remaining row-level access policy or all row-level access policies on the table, run the DROP ALL ROW ACCESS POLICIES statement:

    DROP ALL ROW ACCESS POLICIES
    ON `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`;
    

Replace the following:

  • FILTER_POLICY_NAME: the name of the row-level access policy to delete.
  • PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME: the four-part P.C.N.T identifier of the Apache Iceberg table

Query an FGAC-protected table in BigQuery

When a principal queries an FGAC-protected Apache Iceberg table in BigQuery by using the four-part P.C.N.T identifier, BigQuery evaluates both row-level and column-level policies at query time:

  • Row-level evaluation: Principals in the grantee_list of one or more row-level access policies see the union of rows matching those policies' filter_expression predicates. Principals not included in any row-level access policy on the table see zero rows.
  • Column-level evaluation:
    • Principals granted FINE_GRAINED_READ on a RAW_DATA_ACCESS_POLICY attached to a column see unmasked (raw) data.
    • Principals granted FINE_GRAINED_READ on a DATA_MASKING_POLICY (and not on a RAW_DATA_ACCESS_POLICY for that column) see masked data. If multiple masking policies apply to the same principal on a column, BigQuery selects the rule according to the data masking rule hierarchy.
    • Principals not granted FINE_GRAINED_READ on any data policy attached to a protected column receive an Access Denied error when querying that column. Those principals can still query unprotected columns explicitly or use SELECT * EXCEPT(PROTECTED_COLUMN).

For example, the following query returns rows and column values according to the caller's policies:

SELECT id, COLUMN_NAME
FROM `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`;

Replace the following:

  • COLUMN_NAME: the name of the column to query.
  • PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME: the four-part P.C.N.T identifier of the Apache Iceberg table.

Configure open source engine credentials for FGAC-protected tables

When an open source query engine (such as Apache Spark, Trino, or Apache Flink) reads from or writes to an Apache Iceberg table in a catalog configured with credential vending mode, the engine calls the Apache Iceberg REST catalog endpoint to obtain reduced-scope, short-lived Cloud Storage credentials.

Because direct Cloud Storage file access bypasses query-time policy evaluation in BigQuery, the Apache Iceberg REST catalog endpoint enforces a credential vending guardrail. The guardrail returns a 403 Forbidden HTTP status code when a principal requests storage credentials for an FGAC-protected table, unless the principal has full access to the table:

  • Unmasked (raw) data access to all protected columns: Every protected column on the table must also have a RAW_DATA_ACCESS_POLICY attached. Grant the calling principal the FINE_GRAINED_READ privilege on that RAW_DATA_ACCESS_POLICY.
  • Unfiltered row access (all rows): If the table has any row-level access policies attached, grant the calling principal a row-level access policy that has a FILTER USING (TRUE) clause.

Grant full access to an open source engine writer service account

A service account that runs open source engine jobs, such as jobs in Managed Service for Apache Spark or Dataflow, needs full table access to obtain vended credentials for an FGAC-protected Apache Iceberg table. To grant this access, complete the following steps in BigQuery:

  1. Grant raw data access (RAW_DATA_ACCESS_POLICY) on all protected columns: Attach a RAW_DATA_ACCESS_POLICY to every protected column on the table, and then grant the FINE_GRAINED_READ privilege to the service account by using its IAM v2 principal identifier, principal://IAM.googleapis.com/projects/-/serviceAccounts/SA_EMAIL:

    -- 1. Create a raw data access policy (if not already created)
    CREATE DATA_POLICY IF NOT EXISTS `PROJECT_ID.region-LOCATION.RAW_POLICY_NAME`
    OPTIONS (
      data_policy_type = "RAW_DATA_ACCESS_POLICY"
    );
    
    -- 2. Grant FINE_GRAINED_READ on the raw data access policy to the writer service account
    GRANT FINE_GRAINED_READ ON DATA_POLICY `PROJECT_ID.region-LOCATION.RAW_POLICY_NAME`
    TO "principal://iam.googleapis.com/projects/-/serviceAccounts/WRITER_SA_EMAIL";
    
    -- 3. Attach the raw data access policy to each protected column on the table
    ALTER TABLE `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`
    ALTER COLUMN COLUMN_NAME SET OPTIONS (
      data_policies += ["{'name':'PROJECT_ID.region-LOCATION.RAW_POLICY_NAME'}"]
    );
    
  2. Grant an all-rows access policy if row-level security is enabled: If the table has row-level access policies, grant the service account a row-level access policy that has a FILTER USING (TRUE) clause:

    CREATE OR REPLACE ROW ACCESS POLICY WRITER_ROW_POLICY_NAME
    ON `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`
    GRANT TO ("serviceAccount:WRITER_SA_EMAIL")
    FILTER USING (TRUE);
    

Replace the following:

  • PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME: the four-part P.C.N.T identifier of the Apache Iceberg table
  • LOCATION: the region or multi-region location of the data policy
  • RAW_POLICY_NAME: the name of the raw data access policy
  • COLUMN_NAME: the name of each protected column on the table
  • WRITER_ROW_POLICY_NAME: a unique name for the writer row-level access policy
  • WRITER_SA_EMAIL: the email address of the service account that calls the Apache Iceberg REST catalog endpoint, for example, spark-writer-sa@PROJECT_ID.iam.gserviceaccount.com.

What's next