Account Access & Security Flashcards
Role-based Access Control (RBAC)
an access control framework in which access privileges are assigned to roles and in turn assigned to users.
Discretionary Access Control (DAC)
Snowflake combines RBAC with Discretionary Access Control (DAC) in which each object has an owner, who can in turn grant access to that object.
What command shows the role of the securable object?
SHOW <object></object>
Owning Role
- Has all privileges on the object by default
- Can grant or revoke privileges on the object to other roles
- Transfer ownership to another role
- Share control of an object if the owning role is shared
What privilege access is granted to roles?
- Ability to create a Warehouse
- Ability to list tables contained in a schema
- Ability to add data to a table
What objects are securable?
Unless allowed by a grant, access to a securable object will be __________.
denied
Role
an entity to which privileges on securable objects can be granted or revoked
User
- Roles are assigned to users to give them the authorization to perform actions
- A user can have multiple roles and switch between them within a Snowflake session
- Roles can be granted to other roles creating a role hierarchy
- Privileges of child roles are inherited by parent roles
System-defined Roles type
- ORGADMIN
- ACCOUNTADMIN
- SECURITYADMIN
- SYSADMIN
- USERADMIN
- PUBLIC
ORGADMIN
- Manages operations at organization level
- Can create account in an organization
- Can view all accounts in an organization
- Can view usage information across an organization
ACCOUNTADMIN
- Top-level and most powerful role for an account
- Encapsulates SYSADMIN & SECURITYADMIN
- Responsible for configuring account-level parameters
- View and operate on all objects in an account
- View and manage Snowflake billing and credit data
- Stop any running SQL statements
SYSADMIN
- Can create warehouses, databases, schemas and other objects in an account
SECURITYADMIN
- Manage grants globally via the MANAGE GRANTS privilege
- Create, monitor and manage users and roles
USERADMIN
- User and Role management via CREATE USER and CREATE ROLE security privileges
- Can create users and roles in an account
PUBLIC
- Automatically granted to every user and every role in an account
- Can own securable objects, however objects owned by PUBLIC role are available to every other user and role in an account
Customer Role
- allows you to create a role with custom and fine-grained security privileges defined
- allow administrators working with the system-defined roles to exercise the security principle of least privilege
- can be created by the SECURITYADMIN & USERADMIN roles as well as by any role to which the CREATE ROLE privilege has been granted
- it is recommend to create a hierarch of custom roles with the top-most custom role assigned to the SYSADMIN role
- if custom roles are not assigned to the SYSADMIN role, system admins will not be able to manage the objects owned by the custom role
Privilege
A security privilege defines a level of access to an object
For each object there is a set of security privileges that can be granted on it
What are the 4 categories of security privileges?
- Global privileges
- Privileges for account objects
- Privileges for schemas
- Privileges for schema objects
Global Privileges–>Account Objects–>Schemas–>Schema Objects
Privileges are managed using the ___________ and __________ commands.
GRANT; REVOKE
GRANT USAGE ON DATABASE MY_DB TO ROLE MY_ROLE;
REVOKE USAGE ON DATABASE MY_DB TO ROLE MY_ROLE;
Statement to grant future privileges for objects not yet created.
GRANT SELECT ON FUTURE TABLES IN SCHEMA MY_SCHEMA TO ROLE MY_ROLE;
User Authentication
the process of authenticating with Snowflake via user provided username and password credentials
the default method of authentication
Users with the ______________ role can create additional Snowflake users, which makes use of the ______________ privilege.
USERADMIN; CREATE USER
CREATE USER USER1
PASSWORD=’ABC123’
DEFAULT_ROLE = MYROLE
MUST_CHANGE_PASSWORD = TRUE;
Password Requirements
- can be any case-sensitive string up to 256 characters
- must be at least 8 characters long
- must contain at least 1 digit
- must contain at least 1 uppercase letter and 1 lowercase letter
Multi-factor Authentication (MFA)
an additional layer of security, requiring the user to prove their identity not only with a password but with an additional piece of information (or factor)
MFA in Snowflake is powered by a service called __________.
Duo Security
MFA is enabled on a ______________ basis & only via the _______.
per-user; UI
Snowflake recommends that all users with the _______________ role be required to use ____________.
ACCOUNTADMIN; MFA
Multi-Authentication Flow
MINS_TO_BYPASS_MFA
ALTER USER USER1 SET
MINS_TO_BYPASS_MFA=10;
Specifies the number of minutes to temporarily disable MFA for the user so that they can log in.
DISABLE_MFA
ALTER USER USER1 SET
DISABLE_MFA=TRUE;
Disables MFA for the user, effectively cancelling their enrollment. To use MFA again, the user must re-enroll.
ALLOWS_CLIENT_MFA_CACHING
ALTER USER USER1 SET
ALLOWS_CLIENT_MFA_CACHING=TRUE;
MFA token caching reduces the number of prompts that must be acknowledged while the connecting and authenticating to Snowflake.
Federated Authentication
- enables users to connect to Snowflake using secure SSO (single sign-on)
- Snowflake can delegate authentication responsibility to an SAML 2.0 compliant external identity provider (IdP) with native support for Okta and ADFS IdPs.
IdP
An independent service responsible for creating and maintain user credentials as well as authenticating users for SSO access to Snowflake.
In a federated environment Snowflake is referred to as a _______________.
Service Provider (SP)
Federated Authentication Login Flow
Key Pair Authentication Steps
- Generate Key-Pair using OpenSSL
- Assign Public Key to Snowflake User
- Configure Snowflake Client
- Configure Kye-Pair Rotation
Oauth
- Snowflake supports the OAuth 2.0 protocol.
- OAuth is an open-standard protocol that allows supported clients authorized acess to Snowflake without sharing or stronger user login credentials
- Snowflake offers two OAuth pathways; Snowflake OAuth and External OAuth.
SCIM
- System for Cross-domain Identity Management (SCIM) can be used to manage users and groups (Snowflake roles) in cloud applications using RESTful APIs.
Network Policies
- provide the user with the ability to allow or deny access to their Snowflake account based on a single IP address or list of addresses.
- composed of an allowed IP range and optionally a blocked IP range. Blocked IP ranges are applied first.
- currently support only IPv4 addresses
- use CIDR notation to express an IP subnet range
- can be applied on the account level or to individual users
- if a user is associated to both an account-level and user-level network policy, the user-level policy takes precedence
Only _ Network Policy can be associated with an account at any one time.
1
________________ or ______________ system roles can apply policies. Or custom role with the ____________________ global privilege.
SECURITYADMIN, ACCOUNTADMIN, ATTACH POLICY
Network policies can be applied to __________ or ______________.
ACCOUNT; USER
Data Encryption States
- Encryption At Rest
- Encryption In Transit
Encryption At Rest Security Protocol
AES-256 strong encryption
Encryption In Transit Security Protocol
HTTPS TLS 1.2
E2EE Encryption Flows - Internal
User –PUT–> Internal Stage – COPY INTO <table>–>Table
E2EE Encryption Flows - Internal
User–Cloud Utils–>External Stage–COPY INTO<table>–>Table
Hierarchical Key Model
Root Key–>Account Master Keys–>Table Master Keys–>File Keys
Key Rotation
Key rotation is the practice of transparently replacing existing account and table encryption keys every 30 days with a new key.
Re-Keying
- Once retired key exceeds 1 year, Snowflake automatically creates a new encryption key and re-encrypts all data previously protected by the retired key using the new key.
- Enterprise edition feature
- Comes at a charge
- Requires ACCOUNTADMIN role
- ALTER ACCOUNT SET PERIODIC_DATA_REKEYING = TRUE;
Tri-secret Secure and Customer Managed Keys
KMS is Key Management Service
HSM is Microsoft Azure Key Vault
Dynamic Data Masking
Sensitive data in plain text is loaded into Snowflake, and it is dynamically masked at the time of query for unauthorized users.
Masking Policies STATEMENT
CREATE MASKING POLICY EMAIL_MASK AS (VAL STRING) RETURNS STRING->
CASE
WHEN CURRENT_ROLE() IN (‘SUPPORT’) THEN VAL
ELSE ‘***’
END;
Masking Policies
- Data masking policies are schema-level objects, like tables & views.
- Creating and applying data masking policies can be done independently of object owners
- Masking policies can be nested, existing in tables and views that reference those tables.
- A masking policy is applied no matter where the column is referenced in a SQL statement.
- A data masking policy can be applied either when the object is created or after the object is created.
External Tokenization
Tokenized data is loaded into Snowflake, which is detokenized at query run-time for authorized users via masking policies that call an external tokenization service using external functions.
Row Access Policies
Row access policies enable a security team to restrict which rows are returned in a query.
CREATE ROW ACCESS POLICY Statement
CREATE OR REPLACE ROW ACCESS POLICY RAP_ID AS (ACC_ID VARCHAR) RETURNS BOOLEAN->
CASE
WHEN ‘ADMIN’=CURRENT_ROLE() THEN TRUE
ELSE
END;
Row Access Policies similarities with Column Masking Policies
- Schema level object
- Segregation of duties
- Creation and applying workflow
- Nesting policies
Row Access Policies Important Points
- Adding a masking policy to a column fails if the column is referenced by a row access policy
- Row access policies are evaluated before data masking policies.
Secure views
- are a type of view designed to limit access to the underlying tables or internal structural details of a view
- both standard and materialized views can be designated as secure
- created by adding the keyword SECURE in the view DDL
- definition of a secure view is only available to the object owner
- secure views bypass query optimization which may inadvertently expose data in the underlying table
Account Usage
- Snowflake proves a shared read-only databased called SNOWFLAKE, imported using a Share object called ACCOUNT_USAGE
- It is comprised of 6 schemas, which contain many views providing fine-grained usage metrics at the account and object level
- By default, only users with the ACCOUNTADMIN role can access the SNOWFLAKE database
- Account usage views record dropped objects, not just those that are currently active.
- There is a latency between an event and when that event is recorded in an account usage view
- Certain account usage views provide historical usage metrics. The retention period for these views is 1 year.
Each data created in an account automatically includes a built-in, read-only schema named _________________ based on the ______________ Information Schema
INFORMATION_SCHEMA; SQL-92 ANSI
Each INFORMATION_SCHEMA contains:
- views displaying metadata for all objects contained in the database
- views displaying metadata for account-level objects (non-database objects such as roles, warehouses and databases)
- Table functions displaying metadata for historical and usage data across an account
The output of a view or table function depends on the:
privileges granted to the user’s current role
Account Usage vs Information Schema
