SnowPro Core Certification: Account Access and Security （2/8）

SnowPro Core Certification: Account Access and Security （2/8）

#Account

You will need to create two different accounts, one for each region. 

Each Snowflake account is hosted in a particular Snowflake region. 

To use Snowflake in multiple regions, a Snowflake customer needs to maintain multiple ✅Snowflake accounts, at least one for each region.

 https://docs.snowflake.com/en/user-guide/intro-regions.html

#reader account

A reader account can only consume data from the data provider account that created it. 

https://docs.snowflake.com/en/user-guide/data-sharing-reader-create#what-is-restricted-allowed-in-a-reader-account

Sharing with a non-Snowflake user requires the creation of a reader account. The reader account provides the non-Snowflake user with a Snowflake account through which they can consume the shared data. The data providers own the reader account, and ❗all costs (including query costs) are charged to the data provider. 

https://docs.snowflake.com/en/user-guide/data-sharing-reader-create

#different cloud platform 

It is possible to share data with Snowflake accounts in another cloud platform, but the provider must enable replication and replicate your existing database to the other cloud platform. 

https://docs.snowflake.com/en/user-guide/secure-data-sharing-across-regions-plaforms

#user and #role

👍Yes, it is possible to create a user in Snowflake without a password. We cannot use the Snowflake web interface to create users with no passwords or remove passwords from existing users, and we must use CREATE USER or ALTER USER. 

❗Without a password in Snowflake, a user cannot log in using Snowflake authentication and must use federated authentication instead.

#ACCOUNTADMIN

 is the most powerful role in a Snowflake account. Due to the role hierarchy and privileges inheritance, the ACCOUNTADMIN inherits all the privileges that SECURITYADMIN & USERAMDIN has. 

## resource monitor

From a privilege perspective, only Account Administrators (users with ACCOUNTADMIN role) can create new resource monitors. However, account administrators can grant privileges to an existing resource monitor to allow other users to view and modify the resource monitor configuration. The MONITOR and MODIFY privileges on a resource monitor allow other users to view and modify a specific resource monitor. 

https://docs.snowflake.com/en/user-guide/resource-monitors#access-control-privileges-for-resource-monitors

#SYSADMIN

 

has privileges to create warehouses and databases (and other objects) in an account. This role also has the ability to grant privileges on warehouses, databases, and other objects to other roles.

https://docs.snowflake.com/en/user-guide/security-access-control-overview#system-defined-roles.

Snowflake's access control system

Snowflake's access control system is built on the 🔒 RBAC idea, which means that privileges are issued to roles and roles to users. The privileges associated with a role are given to all users assigned to it. Snowflake also supports🔒discretionary access control (DAC), which means that the role that created an object owns it and can provide access to other roles to that item. 

https://docs.snowflake.com/en/user-guide/security-access-control-overview

#SECURITYADMIN  (aka Security Administrator)

Role that can manage any object grant globally, as well as create, monitor, and manage users and roles. More specifically, this role:

• Is granted the MANAGE GRANTS security privilege to be able to modify any grant, including revoking it.

• Inherits the privileges of the USERADMIN role via the system role hierarchy (i.e. USERADMIN role is granted to SECURITYADMIN).

#useradmin

Role that is dedicated to user and role management only. More specifically, this role:

• Is granted the CREATE USER and CREATE ROLE security privileges.

• Can create users and roles in the account.

  This role can also manage users and roles that it owns. Only the role with the OWNERSHIP privilege on an object (i.e. user or role), or a higher role, can modify the object properties.

The USERADMIN role is typically meant for creating and managing users. However, the privileges of the USERADMIN role are inherited by SECURITYADMIN and ACCOUNTADMIN; therefore, they also get the privileges to create users. ACCOUNTADMIN is the most powerful role anyway and can do anything in a Snowflake account.

#Private connectivity

Private connectivity enables you to ensure that access to your Snowflake instance is via a secure connection and, potentially, to block internet-based access completely. Private connectivity to Snowflake requires the Business-Critical edition as a minimum.

#Replication

Database and share replication are available in all editions, including the Standard edition. 

Replication of all other objects is only available for Business Critical Edition (or higher).

https://docs.snowflake.com/en/user-guide/account-replication-intro

Security, Governance, and Data Protection

Choose the geographical location where your data is stored, based on your region.

User authentication through standard user/password credentials.

Enhanced authentication:

• Multi-factor authentication (MFA).
• Federated authentication and single sign-on (SSO).
• Snowflake OAuth.
• External OAuth.

All communication (in transit) between clients and the server protected through TLS.

All static data

Deployment inside a cloud platform VPC (AWS or GCP) or VNet (Azure).

Isolation of data (for loading and unloading) using:

• Amazon S3 policy controls.
• Azure storage access controls.
• Google Cloud Storage access permissions.

Support for PHI data (in compliance with HIPAA and HITRUST CSF regulations) — requires Business Critical Edition (or higher).
Automatic data encryption by Snowflake using Snowflake-managed keys.
Object-level access control.
Snowflake Time Travel (1 day standard for all accounts; additional days, up to 90, allowed with Snowflake Enterprise) for:

• Querying historical data in tables.
• Restoring and cloning historical data in databases, schemas, and tables.

Snowflake Fail-safe (7 days standard for all accounts) for disaster recovery of historical data.
Column-level Security to apply masking policies to columns in tables or views — requires Enterprise Edition (or higher).
Row-level Security to apply row access policies to tables and views — requires Enterprise Edition (or higher).
Object Tagging to apply tags to Snowflake objects to facilitate tracking sensitive data and resource usage — requires Enterprise Edition (or higher).

Important

Each Snowflake account is hosted in a single region. If you wish to use Snowflake across multiple regions, you must maintain a Snowflake account in each of the desired regions.

Connecting to Snowflake

Snowflake supports multiple ways of connecting to the service:

• A web-based user interface from which all aspects of managing and using Snowflake can be accessed.

• Command line clients (e.g. SnowSQL) which can also access all aspects of managing and using Snowflake.

• ODBC and JDBC drivers that can be used by other applications (e.g. Tableau) to connect to Snowflake.

• Native connectors (e.g. Python, Spark) that can be used to develop applications for connecting to Snowflake.

• Third-party connectors that can be used to connect applications such as ETL tools (e.g. Informatica) and BI tools (e.g. ThoughtSpot) to Snowflake.

Snowflake-provided clients, including SnowSQL (command line interface, for linux, windows, macOS), connectors for Python, Kafka, and Spark, and drivers for Node.js, JDBC, ODBC, and more.

https://developers.snowflake.com/snowsql/

The Snowflake SQL API is a REST API that you can use to access and update data in a Snowflake database.

Snowflake SQL API provides operations that we can use to:

• Submit SQL statements for execution.
• Check the status of the execution of a statement.
• Cancel the execution of a statement.
• Fetch query results concurrently. 

#secure view

You may create a secure view if you need to share data from many tables in separate databases. Because several databases cannot be added to a single share, Snowflake suggests creating secure views within a single database and sharing that database. 

https://docs.snowflake.com/en/user-guide/data-sharing-mutiple-db

authentication methods

Snowflake supports the following authentication methods: Username and password, MFA, SAML, and other authentication methods such as OAuth, Key-pair, and SCIM.

it is possible to create a user in Snowflake without a password. We cannot use the Snowflake web interface to create users with no passwords or remove passwords from existing users, and we must use CREATE USER or ALTER USER. Without a password in Snowflake, a user cannot log in using Snowflake authentication and must use federated authentication instead.

Snowflake supports SCIM 2.0 and is compatible with Okta and Azure Active Directory. SCIM is an open standard that provides automatic user provisioning and role synchronization based on identity provider information. When a new user is created in the identity provider, the SCIM automatically provisions the user in Snowflake. Additionally, SCIM can sync groups defined in an identity provider with Snowflake roles. https://docs.snowflake.com/en/user-guide/scim

#Securable Object

Securable Object is an entity to which access can be granted. Unless allowed by a grant, access will be denied.

#NETWORK POLICY

Only security administrators (i.e., users with the SECURITYADMIN role) or higher or a role with the global CREATE NETWORK POLICY privilege can create network policies using Snowsight, Classic Web Interface, and SQL.

The SHOW PARAMETERS command determines whether a network policy is set on the account or for a specific user.

For Account level: SHOW PARAMETERS LIKE 'network_policy' IN ACCOUNT;

For User level : SHOW PARAMETERS LIKE 'network_policy' IN USER <username>; 

        Example - SHOW PARAMETERS LIKE 'network_policy' IN USER john;

Network policies currently support only Internet Protocol version 4 (i.e. IPv4) addresses.

##Tri-Secret Secure 

Tri-Secret Secure refers to the combination of a Snowflake-managed key and a customer-managed key, which results in the creation of a composite master key to protect your data. Tri-Secret Secure requires the Business Critical edition as a minimum and can be activated by contacting Snowflake support. 

https://docs.snowflake.com/en/user-guide/security-encryption-manage

encryption keys

By default, Snowflake manages encryption keys automatically, requiring no customer intervention. Snowflake-managed keys are 

--> rotated regularly (at 30-day intervals), and 

--> an annual rekeying process re-encrypts data with new keys. The data encryption and key management processes are entirely transparent to the users. Snowflake uses 

--> AES 256-bit encryption to encrypt data at rest. 

https://docs.snowflake.com/en/user-guide/security-encryption-manage

Snowflake encrypts all data in transit using Transport Layer Security (TLS) 1.2. This applies to all Snowflake connections, including those made through the Snowflake Web interface, JDBC, ODBC, and the Python connector. 

Snowflake-managed keys

All Snowflake-managed keys are automatically rotated by Snowflake when they are more than 30 days old. Active keys are retired, and new keys are created. When Snowflake determines the retired key is no longer needed, the key is automatically destroyed. When active, a key is used to encrypt data and is available for usage by the customer. When retired, the key is used solely to decrypt data and is only available for accessing the data.

https://docs.snowflake.com/en/user-guide/security-encryption-end-to-end

##Multi-factor authentication

MFA is enabled by default for all Snowflake accounts and is available in all Snowflake editions. 

All Snowflake client tools, including the web interface, SnowSQL, and the various connectors and drivers, support MFA. 

Snowpipe is a snowflake-managed serverless service. A Snowflake user can not log into it; therefore, it doesn't require MFA. https://docs.snowflake.com/en/user-guide/security-mfa

Multi-factor authentication adds additional protection to the login process in Snowflake. Snowflake provides key pair authentication as a more secure alternative to the traditional username/password authentication approach. Additionally, Snowflake offers federated authentication, enabling users to access their accounts via a single sign-on (SSO). Users authenticate using SAML 2.0-compliant single sign-on (SSO) via an external identity provider (IdP).

Snowflake strongly recommends that all users with the ACCOUNTADMIN role be required to use MFA.

Okta and Microsoft ADFS provide native Snowflake support for federated authentication and SSO.

After a specified period of time (defined by the IdP), a user’s session in the IdP automatically times out, but this does not affect their Snowflake sessions. Any Snowflake sessions that are active at the time remain open and do not require re-authentication. However, to initiate any new Snowflake sessions, the user must log into the IdP again.

Snowflake SQL API supports Oauth, and Key Pair authentication.

external Tokenization

Snowflake supports masking policies that may be applied to columns and enforced at the column level to provide column-level security. Column-level security is achieved by dynamic data masking or external Tokenization.

https://docs.snowflake.com/en/user-guide/security-column

Database failover and failback

Database failover and failback between Snowflake accounts are provided first in the Business Critical edition and are also available in the virtual private Snowflake (VPS) edition. 

https://docs.snowflake.com/en/user-guide/intro-editions.html

#IP Addresses 

If you provide both Allowed IP Addresses and Blocked IP Addresses, Snowflake applies the Blocked List first. This would block your own access. Additionally, in order to block all IP addresses except a select list, you only need to add IP addresses to ALLOWED_IP_LIST. Snowflake automatically blocks all IP addresses not included in the allowed list.