How to get the list of columns that the user has access to

Question

Is there any way(with query/python method) to get all the columns a user has in bigquery table?

my requirement is userA: TableA.column1, TableA.column2,TableA.column4
userB: TableA.column1, TableA.column3

Is your use case about column level security per user applied to big query table? — Bihag Kashikar, Mar 28 '23 at 11:55
Yes. (which all columns the user can view/access in a table). — develop, Mar 28 '23 at 11:56

score 0 · Answer 1 · answered Mar 28 '23 at 17:15

I did not see any documentation for this query, but I stumbled upon the usage of Cloud Asset API to query users using gcloud:

gcloud asset search-all-iam-policies --scope=projects/<project-id> --query="policy : bigquery" | egrep "role:|user:"

Visit the link above on what other options you can try when querying in Cloud Asset API.

score 0 · Answer 2 · answered Mar 28 '23 at 18:51

To retrieve the column level security access, you will have to search for The "Data Catalog Fine-Grained Reader role" assigned to the users, any user who has this role will have access to the column. this is however row level security role and not individual column level.

with regards to then identifying differentiation in users on columns, as per you example: userA: TableA.column1, TableA.column2,TableA.column4 userB: TableA.column1, TableA.column3

This can be identified if there's view with selected columns shared as authorised views to userA / userB or to a group

see this https://cloud.google.com/bigquery/docs/authorized-views#row-level-permissions . i hope this helped

Hi @develop did above helped? if yes can you please marked answered or upvote — Bihag Kashikar, Mar 30 '23 at 04:09

How to get the list of columns that the user has access to

2 Answers2