How to get all the users/groups who have access to a table and columns they have access.

[dev_0]

Is there a way where I could get all the users/groups/service account who has access to a table and columns they have access to? 

I did get only users for a project level but not for tables. 

from pprint import pprint

from google.oauth2 import service_account
import googleapiclient.discovery

credentials = service_account.Credentials.from_service_account_file(
    filename='.../key.json',
    scopes=['https://www.googleapis.com/auth/cloud-platform'])

service = googleapiclient.discovery.build('cloudresourcemanager', 'v1', credentials=credentials)

response = service.projects().getIamPolicy(resource='test-3-project', body={}).execute()

pprint(response)

[Aris_O]

Hi dev_0,

To configure access to tables and views, you can grant an IAM role to an entity at the following levels, listed in order of range of resources allowed (largest to smallest):

• a high level in the Google Cloud resource hierarchy such as the project, folder, or organization level
• the dataset level
• the table or view level

You can also restrict data access within tables, by using the following methods:

• column-level security
• column data masking
• row-level security
  
  You may use this link for your reference:
  https://cloud.google.com/iam/docs/best-practices-service-accounts 
  https://cloud.google.com/bigquery/docs/tables

[dev_0]

Thanks for the reply, my question is how to get/list the inherited users/groups who has access to a particular table in a dataset.

[bihagkashikar · Accepted solution]

Hi @dev_0 

I think I answered this on stack overflow 🙂 ? anyways here's the post

I think this will meet your requirement.

The approach is basically using google cloud asset inventory api with batchget method. https://cloud.google.com/asset-inventory/docs/reference/rest/v1/effectiveIamPolicies/batchGet

if you navigate to below url, the page should also launch a "try this method pop screen" see screenshot below.

in the scope section enter below value in below format:

scope = projects/ENTERYOURPROJECTNAME
names = //bigquery.googleapis.com/projects/YOURPROJECTNAME/datasets/YOURDATASET

and then "execute", this will ask you to login with your console credentials, once done, you should see a response as in image sample json output

{
  "policyResults": [
    {
      "fullResourceName": "//bigquery.googleapis.com/projects/paidrightintelia/datasets/mydataset",
      "policies": [
        {
          "attachedResource": "//bigquery.googleapis.com/projects/YOURPROJECTNAME/datasets/YOURDATASET",
          "policy": {
            "bindings": [
              {
                "role": "roles/bigquery.dataEditor",
                "members": [
                  "projectEditor:YOURPROJECTNAME"
                ]
              },
              {
                "role": "roles/bigquery.dataOwner",
                "members": [
                  "projectOwner:YOURPROJECTNAME",
                  "user:<PII removed by staff>"
                ]
              },
              {
                "role": "roles/bigquery.dataViewer",
                "members": [
                  "projectViewer:YOURPROJECTNAME"
                ]
              }
            ]
          }
        },
        {
          "attachedResource": "//cloudresourcemanager.googleapis.com/projects/YOURPROJECTNAME",
          "policy": {