turbot/gcp_compliance

audit_logging_configured_for_all_servicebigquery_dataset_encrypted_with_cmkbigquery_dataset_not_publicly_accessiblebigquery_table_encrypted_with_cmkcompute_disk_encrypted_with_cskcompute_firewall_allow_connections_proxied_by_iapcompute_firewall_rule_rdp_access_restrictedcompute_firewall_rule_ssh_access_restrictedcompute_instance_block_project_wide_ssh_enabledcompute_instance_confidential_computing_enabledcompute_instance_ip_forwarding_disabledcompute_instance_oslogin_enabledcompute_instance_serial_port_connection_disabledcompute_instance_shielded_vm_enabledcompute_instance_with_no_default_service_accountcompute_instance_with_no_default_service_account_with_full_accesscompute_instance_with_no_public_ip_addressescompute_network_contains_no_default_networkcompute_network_contains_no_legacy_networkcompute_network_dns_logging_enabledcompute_ssl_policy_with_no_weak_ciphercompute_subnetwork_flow_log_enableddns_managed_zone_dnssec_enableddns_managed_zone_key_signing_not_using_rsasha1dns_managed_zone_zone_signing_not_using_rsasha1iam_service_account_gcp_managed_keyiam_service_account_key_age_90iam_service_account_without_admin_privilegeiam_user_not_assigned_service_account_user_role_project_leveliam_user_separation_of_duty_enforcedkms_key_not_publicly_accessiblekms_key_rotated_within_90_daykms_key_separation_of_duties_enforcedlogging_bucket_retention_policy_enabledlogging_metric_alert_audit_configuration_changeslogging_metric_alert_custom_role_changeslogging_metric_alert_firewall_rule_changeslogging_metric_alert_network_changeslogging_metric_alert_network_route_changeslogging_metric_alert_project_ownership_assignmentlogging_metric_alert_sql_instance_configuration_changeslogging_metric_alert_storage_iam_permission_changeslogging_sink_configured_for_all_resourcemanual_controlsql_instance_automated_backups_enabledsql_instance_mysql_local_infile_database_flag_offsql_instance_mysql_skip_show_database_flag_onsql_instance_not_open_to_internetsql_instance_postgresql_log_checkpoints_database_flag_onsql_instance_postgresql_log_connections_database_flag_onsql_instance_postgresql_log_disconnections_database_flag_onsql_instance_postgresql_log_duration_database_flag_onsql_instance_postgresql_log_executor_stats_database_flag_offsql_instance_postgresql_log_hostname_database_flag_configuredsql_instance_postgresql_log_lock_waits_database_flag_onsql_instance_postgresql_log_min_duration_statement_database_flag_disabledsql_instance_postgresql_log_min_error_statement_database_flag_configuredsql_instance_postgresql_log_parser_stats_database_flag_offsql_instance_postgresql_log_planner_stats_database_flag_offsql_instance_postgresql_log_statement_stats_database_flag_offsql_instance_postgresql_log_temp_files_database_flag_0sql_instance_require_ssl_enabledsql_instance_sql_3625_trace_database_flag_offsql_instance_sql_contained_database_authentication_database_flag_offsql_instance_sql_cross_db_ownership_chaining_database_flag_offsql_instance_sql_external_scripts_enabled_database_flag_offsql_instance_sql_remote_access_database_flag_offsql_instance_sql_user_connections_database_flag_configuredsql_instance_sql_user_options_database_flag_not_configuredsql_instance_with_no_public_ipsstorage_bucket_not_publicly_accessiblestorage_bucket_uniform_access_enabled

Query: iam_user_not_assigned_service_account_user_role_project_level

Usage

steampipe query gcp_compliance.query.iam_user_not_assigned_service_account_user_role_project_level

Plugins & Tables

SQL

with unapproved_bindings as (
select
project,
p,
entity
from
gcp_iam_policy,
jsonb_array_elements(bindings) as p,
jsonb_array_elements_text(p -> 'members') as entity
where
p ->> 'role' in ('roles/iam.serviceAccountTokenCreator','roles/iam.serviceAccountUser')
and entity not like '%iam.gserviceaccount.com'
)
select
p.project as resource,
case
when entity is not null then 'alarm'
else 'ok'
end status,
case
when entity is not null
then 'IAM users associated with iam.serviceAccountTokenCreator or iam.serviceAccountUser role.'
else 'No IAM users associated with iam.serviceAccountTokenCreator or iam.serviceAccountUser role.'
end reason,
p.project
from
gcp_iam_policy as p
left join unapproved_bindings as b on p.project = b.project