Cloud 애셋 인벤토리로 보안 비밀 분석

$ gcloud asset export \
   --content-type CONTENT_TYPE \
   --project PROJECT_ID \
   --snapshot-time SNAPSHOT_TIME \
   --bigquery-table BIGQUERY_TABLE \
   --output-bigquery-force

SELECT name, FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE asset_type='secretmanager.googleapis.com/Secret' AND
DATE(JSON_VALUE(resource.data, '$.createTime')) > DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK);

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/Secret' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="resource.data.createTime>-P2W"

SELECT * FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE asset_type='secretmanager.googleapis.com/Secret' AND
JSON_EXTRACT(resource.data, '$.replication.automatic') IS NOT NULL;

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/Secret' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="resource.data.replication.automatic != NULL"

SELECT * FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE
(
  SELECT * FROM
  UNNEST(JSON_EXTRACT_ARRAY(resource.data, '$.replication.userManaged.replicas')) AS location
  WHERE JSON_VALUE(JSON_EXTRACT(location, '$.location')) = "us-central1"
)
IS NOT NULL;

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/Secret' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="resource.data.replication.userManaged.replicas.location=us-central1"

SELECT * FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE asset_type='secretmanager.googleapis.com/SecretVersion' AND
DATE(JSON_VALUE(resource.data, '$.createTime')) < DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY) AND
JSON_VALUE(resource.data, '$.state') = "ENABLED";

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/SecretVersion' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="resource.data.createTime < P6M AND resource.data.state=ENABLED"

SELECT * FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE asset_type='secretmanager.googleapis.com/Secret'
  AND (
    JSON_VALUE(resource.data, "$.replication.automatic.customerManagedEncryption.kmsKeyName") IS NULL
    AND JSON_VALUE(resource.data, "$.replication.userManaged.replicas[0].customerManagedEncryption.kmsKeyName") IS NULL
  );

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/Secret' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="resource.data.replication.userManaged.replicas.customerManagedEncryption = NULL OR resource.data.replication.automatic.customerManagedEncryption=NULL"

SELECT * FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE asset_type='secretmanager.googleapis.com/Secret'
AND (
  JSON_VALUE(resource.data, "$.replication.automatic.customerManagedEncryption.kmsKeyName") IS NOT NULL
  OR JSON_VALUE(resource.data, "$.replication.userManaged.replicas[0].customerManagedEncryption.kmsKeyName") IS NOT NULL
);

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/Secret' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="resource.data.replication.userManaged.replicas.customerManagedEncryption != NULL OR resource.data.replication.automatic.customerManagedEncryption!=NULL"

SELECT * FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE asset_type='secretmanager.googleapis.com/Secret'
  AND (
    JSON_VALUE(resource.data, "$.replication.automatic.customerManagedEncryption.kmsKeyName") = KMS_KEY_NAME
    OR JSON_VALUE(resource.data, "$.replication.userManaged.replicas[0].customerManagedEncryption.kmsKeyName") = KMS_KEY_NAME
  );

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/Secret' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="resource.data.replication.userManaged.replicas.customerManagedEncryption.kmsKeyName=KMS_KEY_NAME"

SELECT * FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE asset_type='secretmanager.googleapis.com/SecretVersion'
AND (
  JSON_VALUE(resource.data, "$.replicationStatus.automatic.customerManagedEncryption.kmsKeyVersionName") IS NULL
  AND JSON_VALUE(resource.data, "$.replicationStatus.userManaged.replicas[0].customerManagedEncryption.kmsKeyVersionName") IS NULL
)
AND JSON_VALUE(resource.data, "$.state") = "ENABLED";

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/SecretVersion' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="(resource.data.replicationStatus.userManaged.replicas.customerManagedEncryption = NULL OR resource.data.replicationStatus.automatic.customerManagedEncryption=NULL) AND resource.data.state=ENABLED"

SELECT * FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE asset_type='secretmanager.googleapis.com/SecretVersion'
AND (
  JSON_VALUE(resource.data, "$.replicationStatus.automatic.customerManagedEncryption.kmsKeyVersionName") = KMS_KEY_VERSION_NAME
  OR JSON_VALUE(resource.data, "$.replicationStatus.userManaged.replicas[0].customerManagedEncryption.kmsKeyVersionName") = KMS_KEY_VERSION_NAME
)
AND JSON_VALUE(resource.data,"$.state")="ENABLED";

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/SecretVersion' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="resource.data.replicationStatus.userManaged.replicas.customerManagedEncryption.kmsKeyVersionName=$FULL_KMS_KEY_VERSION_RESOURCE_NAME AND resource.data.status=ENABLED"

SELECT name FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE asset_type='secretmanager.googleapis.com/Secret' AND
JSON_EXTRACT(resource.data, '$.rotation') IS NULL;

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/Secret' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="resource.data.rotation=NULL"

SELECT *
FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE
  CAST(
    TRIM(
      JSON_VALUE(JSON_EXTRACT(resource.data, "$.rotation.rotationPeriod")),"s")
    AS INT64)
< 86400 * 90 #Rotation period in seconds (86400s in 1 day * 90 days)

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ ROTATION_PERIOD_SECONDS=$((90 * 24 * 60 * 60))
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/Secret' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="resource.data.rotation != null AND resource.data.rotation.rotationPeriod < ${ROTATION_PERIOD_SECONDS}s"

SELECT * FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE asset_type='secretmanager.googleapis.com/Secret' AND
DATE(JSON_VALUE(resource.data, '$.expireTime')) < DATE_ADD(CURRENT_DATE(), INTERVAL 30 DAY);

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/Secret' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="resource.data.expireTime < PD30"

SELECT name, ARRAY_LENGTH(JSON_EXTRACT_ARRAY(resource.data, '$.topics')) AS topics_count,
FROM PROJECT_ID.DATASET_ID.TABLE_NAME
WHERE asset_type='secretmanager.googleapis.com/Secret' AND
ARRAY_LENGTH(JSON_EXTRACT_ARRAY(resource.data, '$.topics')) > 0

$ NOW=$(TZ=GMT date +"%Y-%m-%dT%H:%M:%SZ")
$ gcloud asset list --project=PROJECT_ID \
    --asset-types='secretmanager.googleapis.com/Secret' \
    --snapshot-time=$NOW \
    --content-type='resource' \
    --filter="resource.data.topics !=NULL"