Securing the Snowflake Account with Network Policies

Reference Documents:
-------------------------------------
aws.amazon.com...
docs.snowflake...
Code:
----------
DROP database if exists ramu;
CREATE or replace database RAMU;
create or replace table ramu.PUBLIC.employee_info(employee_id number , employee_name varchar(200),
empl_password varchar(200),
dept varchar(10));
insert into ramu.PUBLIC.employee_info values(1,'Soham','Soham123','HR'),
(2,'Rajdeep','Raja456@','Marketing'),
(3,'Ranjith','Jiohi12','HR'),
(4,'Sudeshna','helloworld12','Support'),
(5,'Surojit','bmps123','BI'),
(6,'Sayan','jalo1i123','Management');

select * from ramu.PUBLIC.employee_info;
USE ROLE securityadmin;
-- replace with your IP
CREATE NETWORK POLICY awspolicy allowed_ip_list=(''),blocked_ip_list=('');
DESC NETWORK POLICY awspolicy;
ALTER ACCOUNT SET network_policy = awspolicy;
DROP NETWORK POLICY "AWSPOLICY";
-- view might lag by up to 2 hours
SELECT
EVENT_TIMESTAMP,
EVENT_TYPE,
CLIENT_IP,
USER_NAME,
ERROR_MESSAGE,
ERROR_CODE,
REPORTED_CLIENT_TYPE,
FIRST_AUTHENTICATION_FACTOR,
SECOND_AUTHENTICATION_FACTOR
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE
IS_SUCCESS = 'NO'
AND ERROR_MESSAGE = 'INCOMING_IP_BLOCKED';
VVI Note:
---------------
We mentioned in the video that to track those IPs which are actually blocked but someone tried to login from those IPs , you can use SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY.
Now to feel this concept , what you can do is , first apply some network policy and block some IPs.
Then try to log in from any of the blocked IPs.
Snowflake will throw this kind of error message -- "IP {The Bloacked IP} is not allowed to access Snowflake. Contact your local security administrator."
Now go back to accountadmin role via the allowed ip and wait for few minutes and then execute the query shared above , you will able to see the LOGI_HISTORY with IP address from where attempt to login was tried with username -- this way you can strictly monitor your snowflake account.
Check this playlist for more AWS Projects in Big Data domain:
• Demystifying Data Engi...

[@vamsireddy5051]

💯 superb explanation and very useful, Thanks lot

[@KnowledgeAmplifier1]

You are welcome vamsi reddy! Happy Learning

[@moizdewaswala6717]

Perhaps the only video to explain this with such a detailed example.Very informative!

[@KnowledgeAmplifier1]

Glad it was helpful MOIZ DEWASWALA! Happy Learning :-)

[@BusinessAutomatedTutorials]

Very good! Thank you!

[@KnowledgeAmplifier1]

You are Welcome!

[@lawrence6559]

ᑭяỖｍｏ??