How can I get a well-formatted list of bigquery dataset members on the command-line?

Viewed 32

I would like to get an easy to read list, on the command-line, of role members that have been granted upon BigQuery datasets. gcloud bq is still at the alpha stage (so its actually gcloud alpha bq) and hence does not (yet) have gcloud alpha bq datasets get-iam-policy.

Instead I've used gcloud asset search-all-iam-policies --scope=projects/my-project --query='resource://bigquery.googleapis.com/projects/my-project/datasets/my-dataset' which returns something like this:

assetType: bigquery.googleapis.com/Dataset
folders:
- folders/12345
- folders/23456
- folders/34567
organization: organizations/7355984939487
policy:
  bindings:
  - members:
    - serviceAccount:sa-id1@my-project.iam.gserviceaccount.com
    role: projects/my-project/roles/myCustomRole1
  - members:
    - serviceAccount:sa-id1@my-project.iam.gserviceaccount.com
    role: projects/my-project/roles/myCustomRole2
  - members:
    - projectEditor:my-project
    - serviceAccount:sa-id3@my-project.iam.gserviceaccount.com
    role: roles/bigquery.dataEditor
  - members:
    - projectOwner:my-project
    role: roles/bigquery.dataOwner
  - members:
    - group:somegroup@mydomain.com
    - projectViewer:my-project
    - serviceAccount:sa-id4@my-project.iam.gserviceaccount.com
    - serviceAccount:sa-id5@my-project.iam.gserviceaccount.com
    - user:user1@mydomain.com
    - user:user2@mydomain.com
    role: roles/bigquery.dataViewer
project: projects/640924302087
resource: //bigquery.googleapis.com/projects/my-project/datasets/my-dataset

I'd like to present that as:

ROLE                                           MEMBER
projects/my-project/roles/myCustomRole1        serviceAccount:sa-id1@my-project.iam.gserviceaccount.com
projects/my-project/roles/myCustomRole2        serviceAccount:sa-id1@my-project.iam.gserviceaccount.com
roles/bigquery.dataEditor                      projectEditor:my-project
roles/bigquery.dataEditor                      serviceAccount:sa-id3@my-project.iam.gserviceaccount.com
roles/bigquery.dataOwner                       projectOwner:my-project
roles/bigquery.dataViewer                      group:somegroup@mydomain.com
roles/bigquery.dataViewer                      projectViewer:my-project
roles/bigquery.dataViewer                      serviceAccount:sa-id4@my-project.iam.gserviceaccount.com
roles/bigquery.dataViewer                      serviceAccount:sa-id5@my-project.iam.gserviceaccount.com
roles/bigquery.dataViewer                      user:user1@mydomain.com
roles/bigquery.dataViewer                      user:user2@mydomain.com

but I can't figure out how to do it. I've played around with --format & --flatten, but no luck so far.

If anyone can tell me how to do it I'd be very grateful.

0 Answers
Related