Account Access & Security Flashcards

1
Q

Role-based Access Control (RBAC)

A

an access control framework in which access privileges are assigned to roles and in turn assigned to users.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Discretionary Access Control (DAC)

A

Snowflake combines RBAC with Discretionary Access Control (DAC) in which each object has an owner, who can in turn grant access to that object.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What command shows the role of the securable object?

A

SHOW <object></object>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Owning Role

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What privilege access is granted to roles?

A
  • Ability to create a Warehouse
  • Ability to list tables contained in a schema
  • Ability to add data to a table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What objects are securable?

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Unless allowed by a grant, access to a securable object will be __________.

A

denied

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Role

A

an entity to which privileges on securable objects can be granted or revoked

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

User

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

System-defined Roles type

A
  • ORGADMIN
  • ACCOUNTADMIN
  • SECURITYADMIN
  • SYSADMIN
  • USERADMIN
  • PUBLIC
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

ORGADMIN

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

ACCOUNTADMIN

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

SYSADMIN

A
  • Can create warehouses, databases, schemas and other objects in an account
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

SECURITYADMIN

A
  • Manage grants globally via the MANAGE GRANTS privilege
  • Create, monitor and manage users and roles
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

USERADMIN

A
  • User and Role management via CREATE USER and CREATE ROLE security privileges
  • Can create users and roles in an account
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

PUBLIC

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Customer Role

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Privilege

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What are the 4 categories of security privileges?

A
  1. Global privileges
  2. Privileges for account objects
  3. Privileges for schemas
  4. Privileges for schema objects

Global Privileges–>Account Objects–>Schemas–>Schema Objects

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Privileges are managed using the ___________ and __________ commands.

A

GRANT; REVOKE

GRANT USAGE ON DATABASE MY_DB TO ROLE MY_ROLE;
REVOKE USAGE ON DATABASE MY_DB TO ROLE MY_ROLE;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Statement to grant future privileges for objects not yet created.

A

GRANT SELECT ON FUTURE TABLES IN SCHEMA MY_SCHEMA TO ROLE MY_ROLE;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

User Authentication

A

the process of authenticating with Snowflake via user provided username and password credentials

the default method of authentication

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Users with the ______________ role can create additional Snowflake users, which makes use of the ______________ privilege.

A

USERADMIN; CREATE USER

CREATE USER USER1
PASSWORD=’ABC123’
DEFAULT_ROLE = MYROLE
MUST_CHANGE_PASSWORD = TRUE;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Password Requirements

A
  • 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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Multi-factor Authentication (MFA)

A

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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

MFA in Snowflake is powered by a service called __________.

A

Duo Security

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

MFA is enabled on a ______________ basis & only via the _______.

A

per-user; UI

28
Q

Snowflake recommends that all users with the _______________ role be required to use ____________.

A

ACCOUNTADMIN; MFA

29
Q

Multi-Authentication Flow

A
30
Q

MINS_TO_BYPASS_MFA

A

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.

31
Q

DISABLE_MFA

A

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.

32
Q

ALLOWS_CLIENT_MFA_CACHING

A

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.

33
Q

Federated Authentication

A
  • 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.
34
Q

IdP

A

An independent service responsible for creating and maintain user credentials as well as authenticating users for SSO access to Snowflake.

35
Q

In a federated environment Snowflake is referred to as a _______________.

A

Service Provider (SP)

36
Q

Federated Authentication Login Flow

A
37
Q

Key Pair Authentication Steps

A
  1. Generate Key-Pair using OpenSSL
  2. Assign Public Key to Snowflake User
  3. Configure Snowflake Client
  4. Configure Kye-Pair Rotation
38
Q

Oauth

A
  • 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.
39
Q

SCIM

A
  • System for Cross-domain Identity Management (SCIM) can be used to manage users and groups (Snowflake roles) in cloud applications using RESTful APIs.
40
Q

Network Policies

A
  • 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
41
Q

Only _ Network Policy can be associated with an account at any one time.

A

1

42
Q

________________ or ______________ system roles can apply policies. Or custom role with the ____________________ global privilege.

A

SECURITYADMIN, ACCOUNTADMIN, ATTACH POLICY

43
Q

Network policies can be applied to __________ or ______________.

A

ACCOUNT; USER

44
Q

Data Encryption States

A
  • Encryption At Rest
  • Encryption In Transit
45
Q

Encryption At Rest Security Protocol

A

AES-256 strong encryption

46
Q

Encryption In Transit Security Protocol

A

HTTPS TLS 1.2

47
Q

E2EE Encryption Flows - Internal

A

User –PUT–> Internal Stage – COPY INTO <table>–>Table

48
Q

E2EE Encryption Flows - Internal

A

User–Cloud Utils–>External Stage–COPY INTO<table>–>Table

49
Q

Hierarchical Key Model

A

Root Key–>Account Master Keys–>Table Master Keys–>File Keys

50
Q

Key Rotation

A

Key rotation is the practice of transparently replacing existing account and table encryption keys every 30 days with a new key.

51
Q

Re-Keying

A
  • 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;
52
Q

Tri-secret Secure and Customer Managed Keys

A

KMS is Key Management Service
HSM is Microsoft Azure Key Vault

53
Q

Dynamic Data Masking

A

Sensitive data in plain text is loaded into Snowflake, and it is dynamically masked at the time of query for unauthorized users.

54
Q

Masking Policies STATEMENT

A

CREATE MASKING POLICY EMAIL_MASK AS (VAL STRING) RETURNS STRING->
CASE
WHEN CURRENT_ROLE() IN (‘SUPPORT’) THEN VAL
ELSE ‘***’
END;

55
Q

Masking Policies

A
  1. Data masking policies are schema-level objects, like tables & views.
  2. Creating and applying data masking policies can be done independently of object owners
  3. Masking policies can be nested, existing in tables and views that reference those tables.
  4. A masking policy is applied no matter where the column is referenced in a SQL statement.
  5. A data masking policy can be applied either when the object is created or after the object is created.
56
Q

External Tokenization

A

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.

57
Q

Row Access Policies

A

Row access policies enable a security team to restrict which rows are returned in a query.

58
Q

CREATE ROW ACCESS POLICY Statement

A

CREATE OR REPLACE ROW ACCESS POLICY RAP_ID AS (ACC_ID VARCHAR) RETURNS BOOLEAN->
CASE
WHEN ‘ADMIN’=CURRENT_ROLE() THEN TRUE
ELSE
END;

59
Q

Row Access Policies similarities with Column Masking Policies

A
  • Schema level object
  • Segregation of duties
  • Creation and applying workflow
  • Nesting policies
60
Q

Row Access Policies Important Points

A
  • 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.
61
Q

Secure views

A
  • 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
62
Q

Account Usage

A
  • 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.
63
Q

Each data created in an account automatically includes a built-in, read-only schema named _________________ based on the ______________ Information Schema

A

INFORMATION_SCHEMA; SQL-92 ANSI

64
Q

Each INFORMATION_SCHEMA contains:

A
  • 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
65
Q

The output of a view or table function depends on the:

A

privileges granted to the user’s current role

66
Q

Account Usage vs Information Schema

A