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

Showing content from http://cloud.google.com/bigquery/docs/using-row-level-security-with-features below:

Using row-level security with other BigQuery features

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

This document describes how to use row-level access security with other BigQuery features.

Before you read this document, familiarize yourself with row-level security by reading Introduction to BigQuery row-level security and Working with row-level security.

Row-level access policies can filter the result data that you see when running queries. To run non-query operations, such as DML, you need full access to all rows in the table. Full access is granted by using a row access policy with the filter expression set to TRUE. This row-level access policy is called the TRUE filter.

Any user can be granted TRUE filter access, including a service account.

Examples of non-query operations are:

• Other BigQuery APIs, such as the BigQuery Storage Read API.
• Some bq command-line tool commands, such as the bq head command.
• Copying a table

CREATE ROW ACCESS POLICY all_access ON project.dataset.table1
GRANT TO ("group:all-rows-access@example.com")
FILTER USING (TRUE);

When you use a DML operation on a table protected by row access policies, you must use a TRUE filter which implies access to the whole table. Any operations that don't alter the table schema maintain any row access policies on the table.

For example, the ALTER TABLE RENAME TO statement copies row access policies from the original table to the new table. As another example, the TRUNCATE TABLE statement removes all of the rows from a table but maintains the table schema as well as any row access policies.

To copy a table with one or more row-level access policies on it, you must first be granted TRUE filter access on the source table. All row-level access policies on the source table are also copied to the new destination table. If you copy a source table without row-level access policies onto a destination table that does have row-level access policies, then the row-level access policies are removed from the destination table, unless the --append_table flag is used or "writeDisposition": "WRITE_APPEND" is set.

Cross-region copies are allowed and all policies are copied. Subsequent queries might be broken after the copy is complete if the queries contain invalid table references in subquery policies.

Row-level access policies on a table must have unique names. A collision in row-level access policy names during the copy results in an invalid input error.

To copy a table with one or more row-level access policies, you must have the following permissions, in addition to the roles to copy tables and partitions.

You need TRUE filter access in order to use the tabledata.list method in the BigQuery API on a table with row-level access policies.

To execute a DML statement that updates a table that has row-level access policies, you need TRUE filter access for the table.

In particular, MERGE statements interact with row-level access policies as follows:

• If a target table contains row-level access policies, then you need TRUE filter access to the target table.
• If a source table contains row-level access policies, then the MERGE statement only acts on the rows that are visible to the user.

Table snapshots support row-level security. The permissions that you need for the base table (source table) and the table snapshot (destination table) are described in Required permissions to copy a table with a row-level access policy.

Row-level access policies cannot be applied on JSON columns. To learn more about the limitations for row-level security, see Limitations.

BigQuery BI Engine does not accelerate queries that are run on tables with one or more row-level access policies; those queries are run as standard queries in BigQuery.

The data in a Looker Studio dashboard is filtered according to the underlying source table's row-level access policies.

Row-level security and column-level security, which includes both column-level access control and dynamic data masking, are fully compatible.

Key points are:

• You can apply a row-level access policy to filter data in any column, even if you don't have access to the data in that column.
  • Attempts to access these columns with the subquery row-level access policy results in an error indicating that access is denied. These columns aren't considered system-referenced columns.
  • Attempts to access these columns with the non-subquery row-level access policy bypass column-level security.
• If the column is restricted due to column-level security, and the column is named in the query's SELECT statement or subquery row-level access policies, you receive an error.
• Column-level security also applies with a SELECT * query statement. The SELECT * is treated the same as a query which explicitly names a restricted column.

This example walks you through the steps for securing a table and then querying it.

Suppose that you have the DataOwner role for a dataset named my_dataset which includes a table with three columns, named my_table. The table contains the data shown in the following table.

In this example, one user is Alice, whose email address is alice@example.com. A second user is Bob, Alice's colleague.

You want Alice to be able to see all the rows that have odd numbers in the rank column, but not even-numbered rows. You don't want Bob to see any rows, even or odd. You don't want anyone to see any data in the fruit column.

• To restrict Alice from seeing the even-numbered rows, you create a row-level access policy which has a filter expression based on the data that appears in the rank column. To prevent Bob from seeing even or odd rows, you don't include him in the grantee list.

  CREATE ROW ACCESS POLICY only_odd ON my_dataset.my_table GRANT
  TO ('user:alice@example.com') FILTER USING (MOD(rank, 2) = 1);
  

• To restrict all users from seeing data in the column named fruit, you create a column-level security policy tag that prohibits all users from accessing any of its data.

Finally, you also restrict access to the column named color in two ways: the column is governed both by a column-level security policy tag prohibiting all access by anyone, and is affected by a row-level access policy, which filters some of the row data in the color column.

• This second row-level access policy only displays rows with the value green in the color column.

  CREATE ROW ACCESS POLICY only_green ON my_dataset.my_table
  GRANT TO ('user:alice@example.com') FILTER USING (color="green");
  

If Alice's coworker Bob tries to query data from my_dataset.my_table, he doesn't see any rows, because Bob isn't in the grantee list for any row-level access policy on the table.

(Some data is affected by the row access policy only_odd)

(All data is secured by a CLS policy tag)

(All data is secured by a CLS policy tag, and some data is affected by the row access policy only_green)

A message is displayed to Bob that says his results may be filtered by the row access policy.

When Alice runs queries to access data from my_dataset.my_table, her results depend on the query she runs and the security, as shown in the following table.

(Some data is affected by the row access policy only_odd)

(All data is secured by a CLS policy tag)

(All data is secured by a CLS policy tag, and some data is affected by the row access policy only_green)

SELECT rank FROM my_dataset.my_table

rank: 3, color: green.

Alice does not see the fruit column because it is restricted by a column-level security policy.

A message is displayed to Alice that says her results may be filtered by the row access policy.

SELECT fruit FROM my_dataset.my_table

access denied

The column-level security applies.

Access is denied.

SELECT color FROM my_dataset.my_table

rank: 3, color: green.

Alice does not see the fruit column because it is restricted by a column-level security policy.

A message is displayed to Alice that says her results may be filtered by the row access policy.

SELECT rank, fruit FROM my_dataset.my_table

access denied

The column-level security applies, before the row-level access policy on data in the rank column is engaged.

Access is denied.

SELECT rank, color FROM my_dataset.my_table

rank: 3, color: green.

Alice does not see the fruit column because it is restricted by a column-level security policy.

A message is displayed to Alice that says her results may be filtered by the row access policy.

SELECT fruit, color FROM my_dataset.my_table

access denied

The column-level security on the fruit column applies, before the row-level access policy on data in the color column is engaged.

Access is denied.

SELECT * FROM my_dataset.my_table

rank: 3, color: green.

Alice does not see the fruit column because it is restricted by a column-level security policy.

A message is displayed to Alice that says her results may be filtered by the row access policy.

Finally, as explained in the section about TRUE filter access, if Alice or Bob has TRUE filter access, then they can see all of the rows in the table, and use it in non-query jobs. However, if the table has column-level security, then it still applies and can affect the results.

You can't use the query execution graph for jobs with row-level access policies.

If a table has row-level access policies, then only the data that you can view is exported to Cloud Storage when you run an extract job.

Row-level access policies are not compatible with Legacy SQL. Queries over tables with row-level access policies must use GoogleSQL. Legacy SQL queries are rejected.

Row-level security does not participate in query pruning, which is a feature of partitioned tables.

While row-level security is compatible with partitioned and clustered tables, the row-level access policies that filter row data aren't applied during partition pruning. You can still use partition pruning on a table that uses row-level security by specifying a WHERE clause that operates on the partition column. Similarly, row-level access policies themselves don't create any performance benefits for queries against clustered tables, but they don't interfere with other filtering that you apply.

Query pruning is performed during the execution of row-level access policies using the filters with the policies.

You don't need TRUE filter access to rename a table with one or more row access policies on it. You can rename a table with a DDL statement.

As an alternative, you can also copy a table and give the destination table a different name. If the source table has a row-level access policy on it, see table copy jobs on this page for more information.

To perform streaming table UPDATE or DELETE operations with change data capture, you must have TRUE filter access.

Only a table administrator can access historical data for a table that has, or has previously had, row-level access policies. Other users get an access denied error if they use a time travel decorator on a table that has had row-level access. For more information, see Time travel and row-level access.

This section describes different types of BigQuery views and how they interact with row-level security.

Logical or materialized views are built from queries against tables. The query results are usually a subset of the table data.

The data displayed in either type of view is filtered according to the underlying source table's row-level access policies. However, you can't reference views or materialized views in row-level access policies.

In addition, when a materialized view is derived from an underlying table that has row-level access policies, then the query performance is the same as when you query the source table directly. In other words, if the source table has row-level security, you don't see the typical performance benefits of querying a materialized view versus querying the source table.

You can also authorize a logical or materialized view, which means sharing the view with specific users or groups (principals). Principals can then query a view, but don't have access to the underlying table. For more information, see Authorized views.

Wildcard queries against tables with row-level access policies fail with an INVALID_INPUT error.

• For information about best practices for row-level access policies, see Best practices for row-level security in BigQuery.

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