Snowflake - Roles and Users Practice
This video gives Lab execution part of Roles and Users in Snowflake.
The theory part was explained in below video.
• Snowflake - Role Based Access Control - ...
Queries:
Account Admin
==============
//User 1 - Account Admins
CREATE USER Donald PASSWORD = 'abc123'
DEFAULT_ROLE = ACCOUNTADMIN
MUST_CHANGE_PASSWORD = TRUE;
GRANT ROLE ACCOUNTADMIN TO USER Donald;
//User 2 - Security Admin
CREATE USER Charles PASSWORD = 'abc123'
DEFAULT_ROLE = SECURITYADMIN
MUST_CHANGE_PASSWORD = TRUE;
GRANT ROLE SECURITYADMIN TO USER Charles;
//User 3 - Sysadmin
CREATE USER Janet PASSWORD = 'abc123'
DEFAULT_ROLE = SYSADMIN
MUST_CHANGE_PASSWORD = TRUE;
GRANT ROLE SYSADMIN TO USER Janet;
Security Admin
================
// Login as user Charles who is Sec Admin
CREATE ROLE sales_admin;
CREATE ROLE sales_users;
//Create hierarchy
GRANT ROLE sales_users to ROLE sales_admin;
GRANT ROLE sales_admin to ROLE SYSADMIN;
//create sales user
CREATE USER Amar_sales PASSWORD = 'abc123' DEFAULT_ROLE = sales_users
MUST_CHANGE_PASSWORD = TRUE;
GRANT ROLE sales_users TO USER Amar_sales;
//create user for sales admin
CREATE USER Akbar_sales_admin PASSWORD = 'abc123' DEFAULT_ROLE = sales_admin
MUST_CHANGE_PASSWORD = TRUE;
GRANT ROLE sales_admin TO USER Akbar_sales_admin;
// 2.Create Roles & Users for HR
CREATE ROLE hr_admin;
CREATE ROLE hr_users;
//Create hierarchy
GRANT ROLE hr_users to ROLE hr_admin;
//This time we will not assign roles to SYSADMIN
//grant role hr_admin to role SYSADMIN;
//create hr user
CREATE USER Bobby_hr PASSWORD = 'abc123' DEFAULT_ROLE = hr_users
MUST_CHANGE_PASSWORD = TRUE;
GRANT ROLE hr_users TO USER Bobby_hr;
//create user for sales admin
CREATE USER Balu_hr_admin PASSWORD = 'abc123' DEFAULT_ROLE = hr_admin
MUST_CHANGE_PASSWORD = TRUE;
GRANT ROLE hr_admin TO USER Balu_hr_admin;
System Admin
=============
// Login as user Janet who is Sec Admin
//Create a warehouse of size SMALL
CREATE WAREHOUSE public_wh
WITH
WAREHOUSE_SIZE='SMALL'
AUTO_SUSPEND=300
AUTO_RESUME= TRUE;
//grant usage on warehouse to role public
GRANT USAGE ON WAREHOUSE public_wh
TO ROLE PUBLIC
//create database accessible to everyone
CREATE DATABASE public_db;
GRANT USAGE ON DATABASE public_db TO ROLE PUBLIC;
// create sales database
CREATE DATABASE sales_db;
//grant ownership to sales_admin
GRANT OWNERSHIP ON DATABASE sales_db TO ROLE sales_admin;
//now the owner of this database is sales_admin which is assigned to SYSADMIN
GRANT OWNERSHIP ON SCHEMA sales_db.public TO ROLE sales_admin;
// create hr database
CREATE DATABASE hr_db;
//grant ownership to hr_admin that we had created using SEC ADMIN
GRANT OWNERSHIP ON DATABASE hr_db TO ROLE hr_admin;
//now the owner of this database is hr_admin which is not assigned to SYSADMIN
GRANT OWNERSHIP ON SCHEMA hr_db.public TO ROLE hr_admin;
//try to drop hr_db - but we can't drop
DROP DATABASE hr_db;
Custom Roles
=============
//Operate with the custom roles
USE ROLE sales_admin;
USE sales_db;
//Create a table
create or replace table customers(
id number,
full_name varchar,
email varchar,
phone varchar,
create_date DATE DEFAULT CURRENT_DATE);
// insert data in table
insert into customers (id, full_name, email,phone)
values
(1,'abc','[email protected]','262-665-9168'),
(2,'def','[email protected]','734-987-7120'),
(3,'ghi','[email protected]','867-946-3659'),
(4,'jkl','[email protected]','563-853-8192');
SHOW TABLES;
//switch to sales_users role
USE ROLE sales_users;
SELECT* FROM CUSTOMERS;
//switch back to admin role and grant access
USE ROLE sales_admin;
GRANT USAGE ON DATABASE sales_db TO ROLE sales_users;
GRANT USAGE ON SCHEMA sales_db.public TO ROLE sales_users;
GRANT SELECT ON TABLE sales_db.public.CUSTOMERS TO ROLE sales_users;
//switch to sales_users role
USE ROLE sales_users;
SELECT* FROM CUSTOMERS;
//try DML operations using sales_users role
DELETE FROM CUSTOMERS;
DROP TABLE CUSTOMERS;
//switch back to admin role, grant delete access
USE ROLE sales_admin;
GRANT ALL ON TABLE sales_db.public.CUSTOMERS TO ROLE sales_users;
//switch to sales_users role
USE ROLE sales_users;
DELETE FROM CUSTOMERS;
//you can't drop this object with All priv, only owner can drop objects
DROP TABLE CUSTOMERS;
//switch back to admin role and grant ownsership
USE ROLE sales_admin;
GRANT OWNERSHIP ON TABLE sales_db.public.CUSTOMERS TO ROLE sales_users;
//Above query won't work, first we have to revoke other privileges
REVOKE ALL ON TABLE sales_db.public.CUSTOMERS FROM ROLE SALES_USERS;
GRANT OWNERSHIP ON TABLE sales_db.public.CUSTOMERS TO ROLE SALES_USERS;
//switch to sales_users role
USE ROLE sales_users;
DROP TABLE CUSTOMERS;
User Admin
============
// login with SEC ADMIN and switch to USERADMIN
CREATE ROLE market_admin;
CREATE ROLE market_users;
GRANT ROLE market_users to ROLE market_admin;
GRANT ROLE market_admin to ROLE SYSADMIN;
CREATE USER bharath PASSWORD = 'abc123' DEFAULT_ROLE = market_users
MUST_CHANGE_PASSWORD = TRUE;
GRANT ROLE market_users TO USER bharath;