Getting roles and principals from bigquery table.

[actmarcos]

Hi,

I'm trying to get all roles and principals in each one of my tables, like when in console access the link share in table properties.

I already tried with INFORMATION_SCHEMA.OBJECT_PRIVILEGES, but didnt appear all the roles and principals(users, services account and groups).

I tried too by API (tables.getIamPolicy) and just get some services accounts and group, not all.

and I'm trying with python, but as well didn't worked, among others methods, I tried get_iam_policy, but, I can't access the necessary information.

Is there one way to do that? Get all principals and roles from table?

Regards

[mars124]

 In BigQuery Studio, you can Share > Manage Permissions of any dataset or table.

[actmarcos]

Hi, thanks for reply. The ideia is not to use console because there are a lot tables to get the information about principals and roles. I want a way to automatize the result to execute with some frequence.

[nikacalupas · Accepted solution]

Hi @actmarcos,

Welcome to the Google Cloud Community!

It seems you want to retrieve all the roles and principals associated with your table, but you're facing challenges accessing the necessary information using different methods.

Here are the potential ways that might help you to resolve your issue:

• Explicit Table-Level Permissions: You may want to use tables.getIamPolicy (or the Python equivalent, get_iam_policy) to retrieve the permissions directly assigned into your table.
• Inherited Dataset-Level Permissions: Identify the dataset associated with your table using datasets.getIamPolicy (or the Python equivalent) to retrieve the permissions assigned to your dataset.
• Inherited Project-Level Permissions: Determine the project associated with your dataset. You may use projects.getIamPolicy (or the Python equivalent) to retrieve the permissions granted at your project level.
• Filter for Principals with Table Access: You may examine each policy to identify which principals have permissions that apply to your table.

By combining the results from all three levels, you might be able to get a complete picture of all principals and their associated roles with access to your table.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[actmarcos]

Hi @nikacalupas , thanks!

I'll try use the methods you mencioned, but that's tha kind of solution I looking for!