Using network policy to secure Snowflake — a practice of separation of duties (SoD)
Network policy defines which network accesses to Snowflake will be allowed or blocked based on source IP ranges and private link IDs. To do so, a policy includes allowed list or blocked list or both. Similarly network policy can be used for out going traffic as well.
Multiple network policies can be created in an Snowflake account — they can either be applied to account level or user level. Network policy applied at account level controls all access traffic and only one can be activated at a time. While multiple network policies can be applied to different users to control user specific requests. Similarly only one policy can be activated for one user at a time.
Network policy management can be in two categories:
- create and maintain network policies
- activate network policies for account or user
It’s recommended to use different roles for above two tasks — so called separation of duties (SoD).
In following example, we’re using “network_admin” role to maintain network policies. “network_admin” role decides what access is allowed and what access is blocked.
And we’re using accountadmin role to activate/deactivate network policy at account level and using department admin to activate/deactivate network policy at user level.
1 Creating network policies
-- create network_admin role
use role useradmin;
create role network_admin;
grant role network_admin to role securityadmin;
-- grant network_admin role proper priviledges
use role accountadmin;
create database securitydb;
create schema securitydb.rules;
-- access to schema for creating network rules
GRANT USAGE ON DATABASE securitydb TO ROLE network_admin;
GRANT USAGE ON SCHEMA securitydb.rules TO ROLE network_admin;
GRANT CREATE NETWORK RULE ON SCHEMA securitydb.rules TO ROLE network_admin;
-- create network policy
GRANT CREATE NETWORK POLICY on account TO ROLE network_admin;
grant role network_admin to user "FENGLIPLATFORM";
-- use role network admin to create network rule and network policy
USE ROLE network_admin;
-- local IP from where I'm login to Snowsight has to be in allow list
select current_ip_address(); -- 172.x.x.x
-- create network rule for account
CREATE NETWORK RULE securitydb.rules.cloud_network_rule
TYPE = IPV4
MODE = INGRESS
VALUE_LIST = ('172.x.x.x','y.y.y.y/27');
-- create network policy using above network rule
create network policy cloud_network_policy
ALLOWED_NETWORK_RULE_LIST = ('securitydb.rules.cloud_network_rule');Network admin is responsible to create network policy for account level and user level. The policies may have different IP ranges, inbound and outbound rules etc.
Create network policy for account as whole for example
-- use role network admin to create network rule and network policy
USE ROLE network_admin;
-- local IP from where I'm login to Snowsight has to be in allow list
select current_ip_address(); -- 172.x.x.x
-- create network rule for account
CREATE NETWORK RULE securitydb.rules.cloud_network_rule
TYPE = IPV4
MODE = INGRESS
VALUE_LIST = ('172.x.x.x','192.168.1.0/24');
-- create network policy using above network rule
create network policy cloud_network_policy
ALLOWED_NETWORK_RULE_LIST = ('securitydb.rules.cloud_network_rule');Similarly, create network policy for specific users.
-- use role network admin to create network rule and network policy
USE ROLE network_admin;
-- local IP from where I'm login to Snowsight has to be in allow list
select current_ip_address(); -- 172.x.x.x
-- create network rule for account
CREATE NETWORK RULE securitydb.rules.app1_users_network_rule
TYPE = IPV4
MODE = INGRESS
VALUE_LIST = ('172.x.x.x','192.168.1.0/24');
-- create network policy using above network rule
create network policy cloud_network_policy
ALLOWED_NETWORK_RULE_LIST = ('securitydb.rules.app1_users_network_rule');2 Activate/deactivate network policy at account level
To apply network policy at account level, we’ll use accountadmin role to activate it.
use role accountadmin;
-- use alter account to set activate policy at account level
alter account set network_policy = cloud_network_policy;
-- unset/deactivate
alter account unset network_policy;3 Activate/deactivate network policy at user level
3.1 A little gap
To apply network policy at user, the role has to be securityadmin above role and the role has ownership of target user.
So we’re suggested to use “useradmin” to create user as ownership role. But this role won’t be able to set network policy at user level — this part can be enhanced to allow useradmin to enable network policy to users.
In this example, we can just use accountadmin to activate network policy for user as well.
use role accountadmin;
-- set/activate
alter user "FENGLIPLATFORM" set network_policy = cloud_network_policy;
-- unset/deactivate
alter user "FENGLIPLATFORM" unset network_policy;3.2 “execute as owner” procedure to rescue
Ideally, to apply network policy at user level we want to delegate to department admin roles to do set/unset policy for their department users. How can we do that securely?
We can let department admin role to call a procedure to activate network policy for user.
- This procedure will be created by role accountadmin and define as “execute as owner”.
- Then accountadmin will grant usage of this procedure to department admin.
When department admin role calls this procedure, it’s getting elevated privilege as accountadmin to be able to activate network policy. “execute as owner” procedure is the key in this case. In the mean while, what department admin role can do is limited by the procedure:
- only roles that being granted of procedure usage can call this procedure
- and only action of “activate network policy for user” can be done which is defined by the procedure
So we can securely open only one specific command of accountadmin role for department admin to do this job.
-- accountadmin creates "execute as owner" procedure
-- this procedure can only do one thing:
-- activate given network policy for given user
use role accountadmin;
create or replace procedure SYSDB.PUBLIC.active_network_policy_user(user_name varchar, network_policy_name varchar)
RETURNS STRING
LANGUAGE python
RUNTIME_VERSION = '3.10'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'run'
EXECUTE AS OWNER
AS
$$
def run(session, user_name, network_policy_name):
sql_statement = "alter user \"" + user_name + "\" set network_policy=" + network_policy_name;
session.sql(sql_statement).collect()
return "Succeed!"
$$;
-- create department role
use role useradmin;
create role department1_admin;
grant role department1_admin to role sysadmin;
-- accountadmin gives only "usage" of this procedure to target role
use role accountadmin;
grant usage on database sysdb to role department1_admin;
grant usage on schema sysdb.public to role department1_admin;
grant usage on procedure SYSDB.PUBLIC.active_network_policy_user(varchar, varchar) to role department1_admin;
-- department admin can call this procedure to activate network policy
use role department1_admin;
call SYSDB.PUBLIC.active_network_policy_user('FENGLIPLATFORM', 'cloud_network_policy');
Similarly, we can do a accountadmin owned procedure for department admin to deactivate user network policies.
And similarly we can securely open specific accountadmin or security admin capabilities to department admin or other custom roles. IMPORTANT security point, the procedure has to be one single action for a very specific use case— never a general one. Just like suid and sgid commands in Linux world.
Summary, we have network_admin role to maintain network policies. we have accountadmin to activate/deactivate network policy at account level. And we have department admin role to only be able to activate/deactivate network policy to users using an accountadmin owned procedure.
Happy Reading!
