Hacker News new | past | comments | ask | show | jobs | submit login
Introduction to BigQuery row-level security (cloud.google.com)
75 points by santhoshkumar3 29 days ago | hide | past | favorite | 11 comments

BigQuery have both column and Row level security.


The limitations are significant and, depending on your use case, costly:

1. Row-level security does not participate in query pruning, which is a feature of partitioned tables. Partitioning divides a large table into smaller partitions, improving query performance, and thus controlling costs by reducing the number of bytes read by a query to partitioned and clustered tables.

2. You cannot rename a table with one or more row access policies on it. Attempting to rename a table with a row-level access policy results in an error. As an alternative, you can 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.

3. The time travel feature is disabled on any table which has (or previously had) one or more row-level access policies on it. In the event that you need to recover table data using time travel, contact BigQuery Support. Support can help you with overrides for this protection, so that you can restore your data.

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

It's not immediately clear to me where this is superior to BigQuery authorized views:


The comparison in the source doc (https://cloud.google.com/bigquery/docs/row-level-security-in...) seems to list the same caveats for both methods.

I dont think its superior or inferior. Row level security is a different way to think about access control.

I think of row level security as enforced on the table which is stronger as it will apply for any reads on the table.

However, if you have authorized views then you can still create a different view of the same table without the access controls.

You can probably use both to solve the problem and in some cases authorized views maybe the right choice and in some cases row level security will work.

I'm confused, how was BigQuery achieving multi-tenancy without Row-Level Security?

Table-level and dataset-level security was always a thing. Row-level security acts as a filter for tables and datasets according to your own organization's ACLs.

As sibling mentioned, table and dataset level security.

Another workaround I’ve seen used a lot is to create a view that filters out the rows and columns you’re not sharing with the entire organization.

It’s slow and pricey and in most cases I would advocate to duplicate the filtered table, rather than doing a view.

Can you explain how authorised views reduce performance and increase costs?

Wodenokoto may have different explanation, but I'm guessing it's just down to the raw data processed. If you're making a view that winds up running a query on the origin table with a `WHERE` added, then you're still processing the entire table for each query.

I don't think row-level security will be any better based on this documentation.

Internally, they've always had it at the level of "organization X can only access organization X's rows".

How does this compare to Accumulo?

Applications are open for YC Winter 2022

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact