A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://developers.google.com/bigquery/docs/row-level-security-intro below:

Introduction to BigQuery row-level security

Stay organized with collections Save and categorize content based on your preferences.

Introduction to BigQuery row-level security Note: This feature may not be available when using reservations that are created with certain BigQuery editions. For more information about which features are enabled in each edition, see Introduction to BigQuery editions.

This document explains the concept of row-level security, how it works in BigQuery, when to use row-level security to secure your data, and other details.

What is row-level security?

Row-level security lets you filter data and enables access to specific rows in a table based on qualifying user conditions.

BigQuery supports access controls at the project, dataset, and table levels, as well as column-level security through policy tags. Row-level security extends the principle of least privilege by enabling fine-grained access control to a subset of data in a BigQuery table, by means of row-level access policies.

One table can have multiple row-level access policies. Row-level access policies can coexist on a table with column-level security as well as dataset-level, table-level, and project-level access controls.

How row-level security works

At a high level, row-level security involves the creation of row-level access policies on a target BigQuery table. These policies act as filters to hide or display certain rows of data, depending on whether a user or group is in an allowed list. Any users or groups not specifically included in the allowed list are denied access.

Note: If you create a new row-level security policy to limit row access, users that previously had full access must be added to a TRUE filter to maintain their access.

An authorized user, with the Identity and Access Management (IAM) roles BigQuery Admin or BigQuery DataOwner, can create row-level access policies on a BigQuery table.

When you create a row-level access policy, you specify the table by name, and which users or groups (called the grantee-list) can access certain row data. The policy also includes the data on which you want to filter, called the filter_expression. The filter_expression functions like a WHERE clause in a typical query.

Remember: Like a WHERE clause, the filter_expression matches the data that you want to be visible to the principals in the grantee_list. The users that are not in the grantee_list cannot see any rows.

For instructions on how to create and use a row-level access policy, see Managing row-level security.

See the DDL reference for the complete syntax, usage, and options when creating row-level access policies.

Example use cases

The following examples demonstrate potential use cases for row-level security.

Note: When managing access for users in external identity providers, replace instances of Google Account principal identifiers—like user:kiran@example.com, group:support@example.com, and domain:example.com—with appropriate Workforce Identity Federation principal identifiers. Filter row data based on region

Consider the case where the table dataset1.table1 contains rows belonging to different regions (denoted by the region column).

You can create and populate the example table by using the following query:

CREATE TABLE IF NOT EXISTS
  dataset1.table1 (partner STRING,
    contact STRING,
    country STRING,
    region STRING);
INSERT INTO
  dataset1.table1 (partner,
    contact,
    country,
    region)
VALUES
  ('Example Customers Corp', 'alice@examplecustomers.com', 'Japan', 'APAC'),
  ('Example Enterprise Group', 'bob@exampleenterprisegroup.com', 'Singapore', 'APAC'),
  ('Example HighTouch Co.', 'carrie@examplehightouch.com', 'USA', 'US'),
  ('Example Buyers Inc.', 'david@examplebuyersinc.com', 'USA', 'US');

Row-level security lets a data owner or administrator implement policies. The following statement implements a policy that restricts users in the APAC mailing group to see only partners from the APAC region:

CREATE ROW ACCESS POLICY
  apac_filter
ON
  dataset1.table1 GRANT TO ("group:sales-apac@example.com")
FILTER USING
  (region="APAC" );

The resulting behavior is that users in the sales-apac@example.com group can view only rows where the value for region is APAC.

The following statement implements a policy that restricts both individuals and groups to see only partners from the US region:

CREATE ROW ACCESS POLICY
  us_filter
ON
  dataset1.table1 GRANT TO ("group:sales-us@example.com",
"user:jon@example.com")
FILTER USING
  (region="US");

The resulting behavior is that users in the group sales-us@example.com and the user jon@example.com can view only rows where the value for region is US.

The following image shows how the previous two access policies restrict which users and groups can view which rows in the table:

Users that aren't in the APAC or US groups don't see any rows.

Filter row data based on sensitive data

Now, consider a different use case, where you have a table that contains salary information.

You can create and populate the example table by using the following query:

CREATE OR REPLACE TABLE
  dataset1.table1 (name STRING,
    department STRING,
    salary INT64,
    email STRING);
INSERT INTO
  dataset1.table1 ( name,
    department,
    salary,
    email)
VALUES
  ('Jim D', 'HR', 100000, 'jim@example.com'),
  ('Anna K', 'Finance', 100000, 'anna@example.com'),
  ('Bruce L', 'Engineering', 100000, 'bruce@example.com'),
  ('Carrie F', 'Business', 100000, 'carrie@example.com');

The row access policy in the following statement restricts querying to members of the company domain. In addition, the use of the SESSION_USER() function restricts access only to rows that belong to the user running the query, based on their user email address.

CREATE ROW ACCESS POLICY
  salary_personal
ON
  dataset1.table1 GRANT TO ("domain:example.com")
  FILTER USING
  (Email=SESSION_USER());

The following image demonstrates how the row access policy restricts the table containing salary information. In this example, the user is named Jim, with the email address jim@example.com.

Note: For additional row-level security examples, see Use row-level security. Filter row data based on lookup table

With subquery support, row access policies can reference other tables and use them as lookup tables. Data used in filtering rules can be stored in a table and a single subquery row access policy can replace multiple configured row access policies. To update the row access policies, you only need to update the lookup table, which replaces multiple row access policies. You don't need to update each individual row access policy.

When to use row-level security versus other methods

Authorized views, row-level access policies, and storing data in separate tables all provide different levels of security, performance, and convenience. Choosing the right mechanism for your use case is important to ensure the proper level of security for your data.

Both row-level security and enforcing row-level access with an authorized view can have vulnerabilities, if used improperly.

When you use either authorized views or row-level access policies for row-level security, we recommend that you monitor for any suspicious activity using audit logging.

Side channels, such as the query duration, can leak information about rows that are at the edge of a storage shard. Such attacks would likely require either some knowledge of how the table is sharded, or a large number of queries.

For more information about preventing such side-channel attacks, see Best practices for row-level security.

Comparison of authorized views, row-level security, and separate tables

The following table compares the flexibility, performance, and security of authorized views, row-level access policies, and separate tables.

Method Security considerations Recommendation Authorized
views Recommended for flexibility. Can be vulnerable to carefully crafted queries, query durations, and other types of side-channel attacks. Authorized views are a good choice when you need to share data with others and flexibility and performance are important. For example, you can use authorized views to share data within your work group. Row-level access policies Recommended for a balance of flexibility and security. Can be vulnerable to query duration side-channel attacks. Row-level access policies are a good choice when you need to share data with others and you want to provide additional security over views or table slices. For example, you can use row-level access policies to share data with people who all use the same dashboard, even if some people have access to more data than others. Separate tables Recommended for security. Users cannot infer data without access to the table. Separate tables are a good choice when you need to share data with others and you need to keep data isolated. For example, you can use separate tables to share data with third-party partners and vendors, when the total number of rows must be secret. Create and manage row-level access policies

For information about how to create, update (re-create), list, view, and delete row-level access policies on a table, and how to query tables with row-level access policies, see Working with row-level access security.

Quotas

For more information about quotas and limits for row-level security, see BigQuery Quotas and limits.

Pricing

Row-level security is included with BigQuery at no additional cost. However, a row-level access policy can affect the cost of running a query in the following ways:

For more information about BigQuery query pricing, see BigQuery pricing.

Limitations

For information about limits for row-level security, see BigQuery Row-level security limits. The following sections document additional row-level security limitations.

Performance limitations

For more information about how row-level security interacts with some BigQuery features and services, see Using row-level security with other BigQuery features.

Other limitations Audit logging and monitoring

When data in a table with one or more row-level access policies is read, the row-level access policies authorized for the read access and any corresponding tables referenced in subqueries appear in the IAM authorization information for that read request.

Creation and deletion of row-level access policies are audit logged, and can be accessed through Cloud Logging. Audit logs include the name of the row-level access policy. However, the filter_expression and grantee_list definitions of a row-level access policy are omitted from logs, as they may contain user or other sensitive information. Listing and viewing of row-level access policies are not audit logged.

For more information about logging in BigQuery, see Introduction to BigQuery monitoring.

For more information about logging in Google Cloud, see Cloud Logging.

What's next

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4