turbot/gcp_compliance

Query: bigquery_dataset_restrict_googlegroups

Usage

powerpipe query gcp_compliance.query.bigquery_dataset_restrict_googlegroups

Steampipe Tables

SQL

with dataset_access as (
select
distinct dataset_id
from
gcp_bigquery_dataset,
jsonb_array_elements(access) as a
where
a ->> 'userByEmail' like '%googlegroups.com'
)
select
a.dataset_id as resource,
case
when b.dataset_id is null then 'ok'
else 'alarm'
end as status,
case
when b.dataset_id is null
then a.dataset_id || ' enforces corporate domain by banning googlegroups.com addresses access.'
else
a.dataset_id || ' does not enforce corporate domain by banning googlegroups.com addresses access.'
end as reason
, a.location as location, a.project as project
from
gcp_bigquery_dataset as a
left join dataset_access as b on a.dataset_id = b.dataset_id;

Controls

The query is being used by the following controls: