[Snowflake] Access Management

1. Key concepts

• Snowflake의 객체에 액세스하고 작업을 수행할 수 있는 사람을 정의함
• 액세스 제어의 두 가지 측면이 결합됨 

DAC(임의 접근 제어) : 각 개체에는 해당 개체에 대한 액세스 권한을 부여할 수 있는 소유자가 있음
RBAC(역할 기반 액세스 제어) : 액세스 권한은 역할에 할당되고, 역할은 사용자에게 할당됨

• Securable object

 

• Snowflake roles

 

• Key concepts

USER : 사람 또는 시스템
ROLE : 권한이 부여되는 엔터티(역할 계층)
PRIVILEGE : 개체에 대한 액세스 레벨 (SELECT, DROP, CREATE 등)
SECURABLE OBJCET : 권한을 부여할 수 있는 개체(데이터베이스, 테이블, 웨어하우스 등)

 

2. Roles overview

• ACCOUNTADMIN 

SYSADMIN과 SECURYTYADMIN 역할이 부여됨
시스템의 최상위 역할
제한된 수의 사용자에게만 부여해야 함

 

• SECURITYADMIN 

USERADMIN 역할이 부여됨
사용자 및 역할을 관리할 수 있음
개체 부여를 전체적으로 관리할 수 있음

 

• SYSADMIN 

웨어하우스 및 데이터베이스 등을 만들 수 있음
모든 사용자 지정 역할을 할당하는 것이 좋음

 

• USERADMIN

사용자 및 역할 관리 전용
사용자 및 역할을 생성할 수 있음

 

• PUBLIC

모든 사용자에게 자동으로 부여됨
다른 모든 역할과 마찬가지로 자체 개체를 생성할 수 있음 

 

3. ACOUNTADMIN

• 모든 개체를 관리함
• 첫 번째 사용자에게 이 역할이 할당됨
• 계정 레벨 개체를 초기 설정 및 관리함
• 다중 요소 인증(MFA) 사용 가능
• 해당 역할에 두 명 이상의 사용자가 할당되어야 함
• 꼭 필요한 경우가 아니면 역할이 있는 개체를 생성하지 않는 것이 좋음

 

 

4. ACCOUNTADMIN in practice

• maria 사용자 생성(default = ACCOUNTADMIN)

CREATE USER maria PASSWORD = '123' 
DEFAULT_ROLE = ACCOUNTADMIN 
MUST_CHANGE_PASSWORD = TRUE;

 

• maria 사용자에게 ACCOUNTADMIN 역할 부여

GRANT ROLE ACCOUNTADMIN TO USER maria;

 

• frank 사용자 생성(default = SECURITYADMIN)

CREATE USER frank PASSWORD = '123' 
DEFAULT_ROLE = SECURITYADMIN 
MUST_CHANGE_PASSWORD = TRUE;

 

• frank 사용자에게 SECURITYADMIN 역할 부여

GRANT ROLE SECURITYADMIN TO USER frank;

 

• adam 사용자 생성(default = SYSADMIN)

CREATE USER adam PASSWORD = '123' 
DEFAULT_ROLE = SYSADMIN 
MUST_CHANGE_PASSWORD = TRUE;

 

• adam 사용자에게 SYSADMIN 역할 부여

GRANT ROLE SYSADMIN TO USER adam;

 

5. SECURITYADMIN

• ACCOUNTADMIN 역할에 속해 있음
• 사용자 및 역할을 생성하고 관리함
• 역할에 대한 권한을 부여하거나 취소함

 

 

 

6. SECURITYADMIN  in practice

• sales용 커스텀 역할 생성

create role sales_admin;
create role sales_users;

 

• 역할 계층 생성(sales_users → sales_admin)

grant role sales_users to role sales_admin;

 

• 역할 계층 생성(sale_admin → SYSADMIN)

grant role sales_admin to role SYSADMIN;

 

• simon_sales 유저 생성

CREATE USER simon_sales PASSWORD = '123' DEFAULT_ROLE =  sales_users 
MUST_CHANGE_PASSWORD = TRUE;

 

• simon_sales 유저에게 sales_users 역할 부여

GRANT ROLE sales_users TO USER simon_sales;

 

• olivia_sales_admin 유저 생성

CREATE USER olivia_sales_admin PASSWORD = '123' DEFAULT_ROLE =  sales_admin
MUST_CHANGE_PASSWORD = TRUE;

 

• olivia_sales_admin 유저에게 sales_admin 역할 부여

GRANT ROLE sales_admin TO USER  olivia_sales_admin;

 

• hr용 커스텀 역할 생성

create role hr_admin;
create role hr_users;

 

• 역할 계층 생성(hr_users → hr_admin)

grant role hr_users to role hr_admin;

 

• oliver_hr 유저 생성

CREATE USER oliver_hr PASSWORD = '123' DEFAULT_ROLE =  hr_users 
MUST_CHANGE_PASSWORD = TRUE;

 

• oliver_hr 유저에게 hr_users 역할 부여

GRANT ROLE hr_users TO USER oliver_hr;

 

• mike_hr_admin 유저 생성

CREATE USER mike_hr_admin PASSWORD = '123' DEFAULT_ROLE =  hr_admin
MUST_CHANGE_PASSWORD = TRUE;

 

• mike_hr_admin 유저에게 hr_admin 역할 부여

GRANT ROLE hr_admin TO USER mike_hr_admin;

 

 

 

 

7. SYSADMIN

• 웨어하우스, 데이터베이스, 테이블 등과 같은 개체를 만들고 관리함
• 커스텀 역할은 SYSADMIN 역할에 속해야 함(커스텀 역할의 웨어하우스, 데이터베이스 및 기타 개체에 대한 권한 부여 가능)
  
  

 

 

8. SYSADMIN in practice

• 웨어하우스 생성

create warehouse public_wh with
warehouse_size='X-SMALL'
auto_suspend=300 
auto_resume= true;

 

• public 역할에게 public_wh 웨어하우스 사용 권한 부여

grant usage on warehouse public_wh 
to role public;

 

• 데이터베이스 생성

create database common_db;

 

• public 역할에게 common_db 데이터베이스 사용 권한 부여

grant usage on database common_db to role public;

 

• 데이터베이스 생성

create database sales_database;

 

• sales_admin 역할에게 sales_database 데이터베이스 소유권 부여

grant ownership on database sales_database to role sales_admin;

 

• sales_admin 역할에게 sales_database.public 스키마 소유권 부여

grant ownership on schema sales_database.public to role sales_admin;

 

• 데이터베이스 개체 확인

SHOW DATABASES;

 

• 데이터베이스 삭제

drop database hr_db;

 

• hr_admin 역할에게 hr_db 데이터베이스 소유권 부여

grant ownership on database hr_db to role hr_admin;

 

• hr_admin 역할에게 hr_db.public 스키마 소유권 부여

grant ownership on schema hr_db.public to role hr_admin;

 

 


9. Custom roles

• 필요에 따라 커스텀 역할을 생성하고, 자체 계층을 만듦
• 커스텀 역할은 일반적으로 SECURITYADMIN 역할에 의해서 생성됨
• SYSADMIN 역할에 속해야 함

 

 

10. Custom roles in practice

• sales_admin으로 역할 변경

USE ROLE SALES_ADMIN;

 

• sales_database 데이터베이스 사용

USE SALES_DATABASE;

 

• 테이블 생성

create or replace table customers(
  id number,
  full_name varchar, 
  email varchar,
  phone varchar,
  spent number,
  create_date DATE DEFAULT CURRENT_DATE);

 

• 데이터 삽입

insert into customers (id, full_name, email,phone,spent)
values
  (1,'Lewiss MacDwyer','lmacdwyer0@un.org','262-665-9168',140),
  (2,'Ty Pettingall','tpettingall1@mayoclinic.com','734-987-7120',254),
  (3,'Marlee Spadazzi','mspadazzi2@txnews.com','867-946-3659',120),
  (4,'Heywood Tearney','htearney3@patch.com','563-853-8192',1230),
  (5,'Odilia Seti','oseti4@globo.com','730-451-8637',143),
  (6,'Meggie Washtell','mwashtell5@rediff.com','568-896-6138',600);

 

• 테이블 개체 확인 

SHOW TABLES;

 

• 테이블 확인 - sales_admin 역할이기 때문에 테이블 쿼리 성공

SELECT* FROM CUSTOMERS;

 

• sales_users로 역할 변경

USE ROLE SALES_USERS;

 

• 테이블 확인 - sales_users 역할은 데이터베이스와 스키마에 대한 사용 권한 및 테이블에 대한 조회 권한이 없기 때문에 쿼리 실패

SELECT* FROM CUSTOMERS;

 

• sales_admin으로 역할 변경

USE ROLE SALES_ADMIN;

 

• sales_users 역할에게 sales_database 사용 권한 부여

GRANT USAGE ON DATABASE SALES_DATABASE TO ROLE SALES_USERS;

 

• sales_users 역할에게 sales_database.public 스키마 사용 권한 부여

GRANT USAGE ON SCHEMA SALES_DATABASE.PUBLIC TO ROLE SALES_USERS;

 

• sales_users 역할에게 sales_database.public.customers 테이블 조회 권한 부여

GRANT SELECT ON TABLE SALES_DATABASE.PUBLIC.CUSTOMERS TO ROLE SALES_USERS;

 

• sales_users로 역할 변경

USE ROLE SALES_USERS;

 

• 테이블 확인 - 데이터베이스와 스키마에 대한 사용 권한 및 테이블에 대한 조회 권한을 부여 받았기 때문에 쿼리 성공

SELECT* FROM CUSTOMERS;

 

• 테이블 삭제 - 테이블에 대한 삭제 권한이 없기 때문에 실행 오류

DROP TABLE CUSTOMERS;

 

• 테이블 데이터 삭제 - 테이블 데이터에 대한 삭제 권한이 없기 때문에 실행 오류

DELETE FROM CUSTOMERS;

 

• sales_admin으로 역할 변경

USE ROLE SALES_ADMIN;

 

• sales_users 역할에게 sales_database.public.customers 테이블 데이터에 대한 삭제 권한 부여

GRANT DELETE ON TABLE SALES_DATABASE.PUBLIC.CUSTOMERS TO ROLE SALES_USERS;

 

• sales_users로 역할 변경

USE ROLE SALES_USERS;

 

• 테이블 데이터 삭제 - 테이블 데이터에 대한 삭제 권한을 부여 받았기 때문에 실행 성공

DELETE FROM CUSTOMERS;

 

 

11. USERADMIN

• 사용자 및 역할을 생성함
• 권한을 부여하기 위한 역할은 아님

 

 

 

12. USERADMIN in practice

• ben 사용자 생성(default = ACCOUNTADMIN)

CREATE USER ben PASSWORD = '123' 
DEFAULT_ROLE = ACCOUNTADMIN 
MUST_CHANGE_PASSWORD = TRUE;

 

• ben 사용자에게 hr_admin 역할 부여

GRANT ROLE HR_ADMIN TO USER ben;

 

• 역할 개체 확인

SHOW ROLES;

 

• 역할 계층 생성(hr_admin → SYSADMIN)

GRANT ROLE HR_ADMIN TO ROLE SYSADMIN;

 

 

 

13. PUBLIC

• 최소 권한 역할(계층의 맨 아래)
• 모든 사용자가 이 역할에 자동으로 할당됨
• 개체 소유 가능
  
  
  
  

 

 

 

 

 

Udemy의 'Snowflake - The Complete Masterclass (2023 Edition)'를 공부한 내용을 바탕으로 작성하였습니다.