IssueTracker

According to some documentation that I have just read this is now supported:

Does BigQuery column-level security work for views?
Yes. Views are derived from an underlying table. The same column-level security on the table applies when the protected columns are accessed through a view.

Note that there are two kinds of views in BigQuery, logical views and authorized views. Both types of views are derived from a source table, and both are consistent with the table's column-level security.

https://cloud.google.com/bigquery/docs/column-level-security#work_for_views

That help page seems to suggest that if there a table A is referenced by view B, the policy tags set on A are enforced also when users query B. This is expected.

However, it is not possible to set additional policy tags on B fields independently, AFAIK.

Ignore my previous comment above. On reflection the documentation I referred to does not solve the problem that the original poster outlined. Moreover, we've recently discovered limitations of policy tags that makes me realise how vital this request is.

Take this scenario:

We have a table t containing emailAddress which is considered PII and hence we do not want to expose that to everyone, so we put a policy tag upon it to limit who can access it. However, people still want to be able to answer question like "How many distinct users visited our site?" which could be answered by issuing

select count(distinct emailAddress) from t

however, the policy tag prevents that.

We'd like to get around this problem by presenting a view which does this:

create view v
as
select SHA256("some-pepper-value" || emailAddress) as emailAddressHash

so that users can query it like so:

select count(distinct emailAddressHash) from v

however, the policy tag still prevents end users from accessing emailAddressHash. This is our problem, we know the hashing mechanism protects the PII data but still people cannot access the data.

Perhaps the original poster's suggestion solves this problem, perhaps it doesn't. But nevertheless, this is a problem that we need a solution for.

Hello,

We understand the importance of this feature and the product team is already aware as well. I invite you to subscribe to this thread, if you haven't already done so, by clicking on the star at the top left, this way, you also help to bring more visibility to this feature.

Thanks.

My use case is similar, but not exactly identical to the scenario described in #4.

My use case is the following:

• I have a source table T1 containing sensitive personal data. It is in a dataset with restricted access. Analysts can't query that table directly.
• I define an authorized view V1 over T1. This view does some data transformation and drops some columns.
• I still want to expose some sensitive columns (like a user ID) through V1, but only to a selected subset of users and service accounts.

My goal is to be able to do so without resorting to SESSION_USER(), which feels like a hack and requires to hardcode usernames in the view query of V1.