Search code examples
permissionsgoogle-bigqueryinformation-schema

How can I give others permission to read my INFORMATION_SCHEMA.SCHEMATA in BigQuery?

What privileges can I grant to let everyone in the world query my information schema? i.e. I want everyone to be able to run:

select * from `projectid`.INFORMATION_SCHEMA.SCHEMATA

Currently I get back:

Access Denied: Table projectid:INFORMATION_SCHEMA.SCHEMATA: User does not have permission to query table projectid:INFORMATION_SCHEMA.SCHEMATA
Solution

  • Usually in BigQuery you set permissions at the dataset level. For example, this query will run for anyone, as the dataset is public for everyone:

    SELECT * 
FROM `fh-bigquery.flights.INFORMATION_SCHEMA.TABLES`

    But you can't do this:

    SELECT * 
FROM `fh-bigquery.INFORMATION_SCHEMA.SCHEMATA`

    This because you need project level permissions to see all my datasets, even the ones I haven't made public.

    If you really want to share the schemas of all your datasets with the world, then you could create a custom role just for this, with the bigquery.datasets.get permission:

    Then you need to assign this role to all users - but that's not an option.

    At the project level, you can assign this role to one of these:

    Google Account email: user@gmail.com
Google Group: admins@googlegroups.com
Service account: server@example.gserviceaccount.com
G Suite domain: example.com

    One option in this case:

    • Create a Google Group.
    • Give this new role to this new Google Group.
    • Make this Google Group free to join.
    • Tell people "hey, if you want to see my project SCHEMATA, join this group".

    Then all will work.