Writing policy analysis to BigQuery

This page explains how to analyze Identity and Access Management (IAM) policies asynchronously and write results to BigQuery. The process is similar to analyzing IAM policies except the analysis result is written to BigQuery tables.

Before you begin

  • You must enable the Cloud Asset API for your project.

  • If you are using the API to run these queries, you need to set up your environment and gcurl.

    1. Set up your environment.

    2. To set up a gcurl alias, complete the following steps.

      If you are on a Compute Engine instance, run the following command.

      alias gcurl='curl -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" \
-H "Content-Type: application/json" -X POST'

      If you are not on a Compute Engine instance, run the following command.

      alias gcurl='curl -H "$(oauth2l header --json CREDENTIALS cloud-platform)" \
-H "Content-Type: application/json" -X POST'

      Where CREDENTIALS is your credentials file path, such as ~/credentials.json.

Required permissions

The following permissions are required to run a policy analysis and write the analysis results to the specified BigQuery destination:

Calling AnalyzeIamPolicyLongrunning

The AnalyzeIamPolicyLongrunning method allows you to issue an analysis request and get results in the specified BigQuery destination.

gcloud

You can call AnalyzeIamPolicyLongrunning on your API-enabled project using the asset analyze-iam-policy-longrunning gcloud command. You must be running Cloud SDK version 314.0.0 or later. You can check your version with the gcloud version command.

gcloud asset analyze-iam-policy-longrunning \
    --organization="ORG_ID" \
    --full-resource-name="FULL_RESOURCE_NAME" \
    --permissions="COMMA_SEPARATED_PERMISSIONS" \
    --bigquery-dataset="projects/BQ_PROJECT_ID/datasets/BQ_DATASET_NAME" \
    --bigquery-table-prefix="BQ_TABLE_PREFIX"

For example, to write analysis results of who has compute.instances.get and compute.instances.start permissions on the Compute Engine instance ipa-gce-instance-2 under organiazion 123456789 to the BigQuery dataset projects/project1/datasets/bq-dataset-01 with a table prefix as some_prefix:

gcloud asset analyze-iam-policy-longrunning --organization=123456789 \
    --full-resource-name="//compute.googleapis.com/projects/project1/zones/us-central1-a/instances/ipa-gce-instance-2" \
    --permissions="compute.instances.get,compute.instances.start" \
    --bigquery-dataset="projects/project1/datasets/bq-dataset-01" \
    --bigquery-table-prefix="some_prefix"

Find more on help:

gcloud asset analyze-iam-policy-longrunning --help

REST

Analyze the IAM policies and write results using the gcurl alias.

gcurl -d "$JSON_REQUEST" \
"https://cloudasset.googleapis.com/v1/organizations/${YOUR_ORG_ID}:analyzeIamPolicyLongrunning"

Where:

  • YOUR_ORG_ID is the organization id, such as: 123456789

  • JSON_REQUEST is the analysis request in JSON format. For example, To analyze who has compute.instances.get and compute.instances.start permissions on the Compute Engine instance ipa-gce-instance-2 under organiazion 123456789 to the BigQuery dataset projects/project1/datasets/bq-dataset-01 with a table prefix as some_prefix:

    JSON_REQUEST='{
  "analysisQuery":{
     "scope":"organization/123456789",
     "resourceSelector":{
        "fullResourceName":"//compute.googleapis.com/projects/project1/zones/us-central1-a/instances/ipa-gce-instance-2"
     },
     "accessSelector":{
        "permissions":[
           "compute.instances.get",
           "compute.instances.start"
        ]
     }
  },
  "outputConfig":{
     "bigqueryDestination":{
        "dataset":"projects/project1/datasets/bq-dataset-01",
        "tablePrefix":"some_prefix"
     }
  }
}'

Setting the partition key

BigQuery tables can be partitioned, which can improve query performance and reduce query cost by filtering partitions. The following partition keys are supported:

  • REQUEST_TIME: The time when the request is received.

Setting the write disposition

Specifies the action that occurs if the destination table or partition already exists and is non-empty. Also see BigQuery write disposition. The following values are supported:

  • WRITE_APPEND: Default value. Appends the data to the table or the latest partition.
  • WRITE_TRUNCATE: Overwrites the entire table or all the partitions data.
  • WRITE_EMPTY: Returns an error.

Viewing IAM policy analysis results

To view your IAM policy analysis results:

Console

  1. Go to the BigQuery page in the Cloud Console.
    Go to the BigQuery page

  2. To display the tables and views in the dataset, open the navigation panel. In the Resources section, select your project to expand it, and then select a dataset.

  3. From the list, select the tables with your prefix. The table with analysis suffix contains the query and metadata(ex. operation name, request time, non-critical errors). The table with analysis_result suffix is the result listing tuples of {identity, role(s)/permission(s), resource} together with IAM policies that generate those tuples.

  4. To view a sample set of data, select Preview.

API

To browse your table's data, call tabledata.list. In the tableId parameter, specify the name of your table.

You can configure the following optional parameters to control the output.

  • maxResults is the maximum number of results to return.
  • selectedFields is a comma-separated list of columns to return; If unspecified, all columns are returned.
  • startIndex is the zero-based index of the starting row to read.

Values are returned wrapped in a JSON object that you must parse, as described in the tabledata.list reference documentation.

Constructing a query

To learn about other use cases and options for queries, see analyzing IAM policy query samples.

Querying BigQuery

This section provides example SQL queries to show you how to use BigQuery tables written by AnalyzeIamPolicyLongrunning. For more information on BigQuery syntax, see Standard SQL Query Syntax.

OP_ID is needed for most queries, you can get it from AnalyzeIamPolicyLongrunning response. For example, in gcloud, You will find OP_ID is 123456 in "Use [gcloud asset operations describe organizations/123456789/operations/AnalyzeIamPolicyLongrunning/123456] to check the status of the operation.".

List Operations

Your table could store results of multiple AnalyzeIamPolicyLongrunning operations. You can use the following query to list all of them:

SELECT DISTINCT
  requestTime,
  opName
FROM `BQ_PROJECT_ID.BQ_DATASET_NAME.BQ_TABLE_PREFIX_analysis`
ORDER BY 1 DESC
;

List Analyses in one Operation

In one AnalyzeIamPolicyLongrunning operation, there could be multiple analysis records generated. For example, when you enable the analyze_service_account_impersonation option in your request, the result could contain one main analysis (with analysisId 0) and several service account impersonation analysis.

You can use the following query to find out all analysis by giving an operation name.

DECLARE _opName STRING DEFAULT "organizations/ORG_ID/operations/AnalyzeIamPolicyLongrunning/OP_ID";

SELECT
  analysisId,
  requestTime,
  TO_JSON_STRING(analysis.analysisQuery, true) as analysisQuery,
  analysis.fullyExplored,
  TO_JSON_STRING(analysis.nonCriticalErrors, true) as nonCriticalErrors
FROM `BQ_PROJECT_ID.BQ_DATASET_NAME.BQ_TABLE_PREFIX_analysis`
WHERE opName=_opName
ORDER BY 1
;

List ACEs(Access Control Entries) in one Analysis

An ACE is an Access Control Entry {identity, role(s)/permission(s), resource}. You can use the following query to list ACE in one analysis.

DECLARE _opName STRING DEFAULT "organizations/ORG_ID/operations/AnalyzeIamPolicyLongrunning/OP_ID";

SELECT DISTINCT
  ids.name AS identity,
  resources.fullResourceName AS resource,
  accesses.role AS role,
  accesses.permission AS permission
FROM `BQ_PROJECT_ID.BQ_DATASET_NAME.BQ_TABLE_PREFIX_analysis_result`,
  UNNEST(analysisResult.identityList.identities) AS ids,
  UNNEST(analysisResult.accessControlLists) AS acls,
  UNNEST(acls.accesses) AS accesses,
  UNNEST(acls.resources) AS resources
WHERE opName=_opName
AND analysisId = 0
ORDER BY 1,2,3,4
;

List ACEs(Access Control Entries) with IAM policy binding in one Analysis

In this query, we list both ACE and the IAM policy binding that generates this ACE for one analysis.

DECLARE _opName STRING DEFAULT "organizations/ORG_ID/operations/AnalyzeIamPolicyLongrunning/OP_ID";

SELECT
  ids.name AS identity,
  resources.fullResourceName AS resource,
  accesses.role AS role,
  accesses.permission AS permission,
  analysisResult.attachedResourceFullName as iam_policy_attached_resource,
  TO_JSON_STRING(analysisResult.iamBinding, true) as iam_policy_binding
FROM `BQ_PROJECT_ID.BQ_DATASET_NAME.BQ_TABLE_PREFIX_analysis_result`,
  UNNEST(analysisResult.identityList.identities) AS ids,
  UNNEST(analysisResult.accessControlLists) AS acls,
  UNNEST(acls.accesses) AS accesses,
  UNNEST(acls.resources) AS resources
WHERE opName=_opName AND analysisId = 0
ORDER BY 1,2,3,4
;

List IAM policy bindings in one Analysis

In this query, we list the IAM policy bindings appeared in one analysis.

DECLARE _opName STRING DEFAULT "organizations/ORG_ID/operations/AnalyzeIamPolicyLongrunning/OP_ID";

SELECT DISTINCT
  analysisResult.attachedResourceFullName as iam_policy_attached_resource,
  TO_JSON_STRING(analysisResult.iamBinding, true) as iam_policy_binding
FROM `BQ_PROJECT_ID.BQ_DATASET_NAME.BQ_TABLE_PREFIX_analysis_result`
WHERE opName=_opName AND analysisId = 0
ORDER BY 1, 2
;

List IAM policy bindings with ACE(Access Control Entry) in one Analysis

In this query, we list the IAM policy bindings with their derived ACEs in one analysis

DECLARE _opName STRING DEFAULT "organizations/ORG_ID/operations/AnalyzeIamPolicyLongrunning/OP_ID";

SELECT
  analysisResult.attachedResourceFullName as iam_policy_attached_resource,
  TO_JSON_STRING(analysisResult.iamBinding, true) as iam_policy_binding,
  TO_JSON_STRING(analysisResult.identityList.identities, true) as identities,
  TO_JSON_STRING(acls.accesses, true) as accesses,
  TO_JSON_STRING(acls.resources, true) as resources
FROM `BQ_PROJECT_ID.BQ_DATASET_NAME.BQ_TABLE_PREFIX_analysis_result`,
  UNNEST(analysisResult.accessControlLists) AS acls
WHERE opName=_opName AND analysisId = 0
ORDER BY 1,2
;