Onboarding Steps

-- Step 1. Switch to the Account Admin role
USE ROLE ACCOUNTADMIN;

-- Step 2. Create a new role for Bluesky Data
CREATE ROLE BLUESKY_READONLY
   COMMENT = 'This role is used by Bluesky Data';
   
-- Step 3. Give our role privileges on the internal SNOWFLAKE db to allow us to analyze historical queries and metadata
GRANT IMPORTED PRIVILEGES
  ON DATABASE SNOWFLAKE
  TO ROLE BLUESKY_READONLY;

-- Step 4. Create a small sized Warehouse
CREATE WAREHOUSE BLUESKY_WH WITH WAREHOUSE_SIZE = 'SMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 30 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = 'STANDARD';

-- Step 5. Give our role USAGE on the Warehouse
GRANT USAGE ON WAREHOUSE BLUESKY_WH TO ROLE BLUESKY_READONLY;

-- Step 6. Grant all privileges to the warehouse
GRANT ALL PRIVILEGES ON WAREHOUSE BLUESKY_WH TO ROLE BLUESKY_READONLY;

-- Step 7. Create a user (username is BLUESKY_USER, password is 123)
CREATE USER BLUESKY_USER PASSWORD = '123' DEFAULT_ROLE =  BLUESKY_READONLY
MUST_CHANGE_PASSWORD = TRUE;

-- Step 8. Grant the role to user
GRANT ROLE BLUESKY_READONLY TO USER BLUESKY_USER;

-- Step 9. Set the default role as read_only for safety reasons
ALTER USER BLUESKY_USER SET DEFAULT_ROLE = BLUESKY_READONLY;

-- Step 10. Create a DB for us and grant us write access to the DB. This step allows us to define our own views (e.g., sampling, or filters) for analysis purposes.
CREATE DATABASE BLUESKY_DB;

-- Step 11. Grant all privileges for the newly created DB (BLUESKY_DB) TO ROLE BLUESKY_READONLY
GRANT ALL PRIVILEGES ON DATABASE BLUESKY_DB TO ROLE BLUESKY_READONLY;

-- Step 12. Grant monitoring permission to role BLUESKY_READONLY
GRANT MONITOR USAGE ON ACCOUNT TO ROLE BLUESKY_READONLY;

-- Step 13. Allows access to query operator stats
GRANT MONITOR ON ACCOUNT TO ROLE BLUESKY_READONLY;

-- Step 14. Create a place for us to store the account metadata
CREATE SCHEMA BLUESKY_DB.DATA;

-- Step 15: Grant ownership to the DATA schema
GRANT OWNERSHIP ON SCHEMA BLUESKY_DB.DATA TO ROLE BLUESKY_READONLY;

-- Step 16. Grant privileges to the DATA schema
GRANT ALL PRIVILEGES ON SCHEMA BLUESKY_DB.DATA TO ROLE BLUESKY_READONLY;

-- Step 17. Explicitly grants privilege to create a stage on the newly created schema
GRANT CREATE STAGE ON SCHEMA BLUESKY_DB.DATA TO ROLE BLUESKY_READONLY;

-- Step 18. Creates a storage integration
CREATE OR REPLACE STORAGE INTEGRATION BLUESKY_GCS_INTEGRATION
       TYPE = EXTERNAL_STAGE
       ENABLED = TRUE
       STORAGE_PROVIDER = 'GCS'
       STORAGE_ALLOWED_LOCATIONS = ('*');

-- Step 19. Grant ownership to the BLUESKY_GCS_INTEGRATION storage integration
GRANT OWNERSHIP ON INTEGRATION BLUESKY_GCS_INTEGRATION TO ROLE BLUESKY_READONLY;

-- Step 20. Grant all privileges to the BLUESKY_GCS_INTEGRATION storage integration
GRANT ALL PRIVILEGES ON INTEGRATION BLUESKY_GCS_INTEGRATION TO ROLE BLUESKY_READONLY;

-- Step 21. Grant privilege to create storage integrations
GRANT CREATE INTEGRATION ON ACCOUNT TO ROLE BLUESKY_READONLY;