turbot/snowflake_compliance

Query: network_policy_blocked_list_set

Usage

powerpipe query snowflake_compliance.query.network_policy_blocked_list_set

SQL

with applied_network_policy as (
select
name,
coalesce(blocked_ip_list, '') as blocked_ip_list, -- blocked_ip_list is optional therefore can be null
account
from
snowflake_network_policy
where
name = (
select
value
from
snowflake_account_parameter
where
key = 'NETWORK_POLICY')
),
analysis as (
select
name,
to_jsonb ($1::text[]) <@ array_to_json(string_to_array(blocked_ip_list, ','))::jsonb as has_blocked_ips,
to_jsonb ($1) - string_to_array(blocked_ip_list, ',', '') as missing_ips,
account
from
applied_network_policy
)
select
coalesce(analysis.name, sap.account) as resource,
case when (
select
count(*)
from
applied_network_policy
group by
name) is null then
'alarm'
when has_blocked_ips then
'ok'
else
'alarm'
end as status,
case when (
select
count(*)
from
applied_network_policy
group by
name) is null then
'No network policy activated in the account.'
when has_blocked_ips then
name || ' has all blocked IPs.'
else
name || ' is missing blocked IPs: ' || array_to_string(array (
select
jsonb_array_elements_text(missing_ips)), ', ') || '.'
end as reason,
sap.account
from
snowflake_account_parameter as sap
left join analysis on sap.account = analysis.account
where
key = 'NETWORK_POLICY';

Controls

The query is being used by the following controls: