Advanced Access Control¶

-- Access roles (object-level permissions)
CREATE ROLE analytics_reader;
CREATE ROLE analytics_writer;
CREATE ROLE finance_reader;
CREATE ROLE finance_writer;

-- Functional roles (user-facing)
CREATE ROLE data_analyst;
CREATE ROLE data_engineer;
CREATE ROLE finance_analyst;

-- Map functional to access roles
GRANT ROLE analytics_reader TO ROLE data_analyst;
GRANT ROLE analytics_writer TO ROLE data_engineer;
GRANT ROLE finance_reader TO ROLE finance_analyst;

-- Connect to SYSADMIN
GRANT ROLE data_analyst TO ROLE SYSADMIN;
GRANT ROLE data_engineer TO ROLE SYSADMIN;
GRANT ROLE finance_analyst TO ROLE SYSADMIN;

-- Assign to users
GRANT ROLE data_analyst TO USER alice;
GRANT ROLE data_engineer TO USER bob;

-- Service role with specific permissions
CREATE ROLE etl_service_role;
GRANT USAGE ON DATABASE raw_db TO ROLE etl_service_role;
GRANT USAGE ON ALL SCHEMAS IN DATABASE raw_db TO ROLE etl_service_role;
GRANT SELECT ON ALL TABLES IN DATABASE raw_db TO ROLE etl_service_role;
GRANT ALL ON DATABASE staging_db TO ROLE etl_service_role;
GRANT USAGE ON WAREHOUSE etl_wh TO ROLE etl_service_role;

-- Service user
CREATE USER etl_service
  DEFAULT_ROLE = etl_service_role
  DEFAULT_WAREHOUSE = etl_wh
  MUST_CHANGE_PASSWORD = FALSE
  RSA_PUBLIC_KEY = '...';

GRANT ROLE etl_service_role TO USER etl_service;
GRANT ROLE etl_service_role TO ROLE SYSADMIN;

-- Create database roles
CREATE DATABASE ROLE sales_db.reader;
CREATE DATABASE ROLE sales_db.writer;
CREATE DATABASE ROLE sales_db.admin;

-- Grant database privileges
GRANT USAGE ON ALL SCHEMAS IN DATABASE sales_db TO DATABASE ROLE sales_db.reader;
GRANT SELECT ON ALL TABLES IN DATABASE sales_db TO DATABASE ROLE sales_db.reader;

GRANT USAGE ON ALL SCHEMAS IN DATABASE sales_db TO DATABASE ROLE sales_db.writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN DATABASE sales_db TO DATABASE ROLE sales_db.writer;

-- Hierarchy within database roles
GRANT DATABASE ROLE sales_db.reader TO DATABASE ROLE sales_db.writer;
GRANT DATABASE ROLE sales_db.writer TO DATABASE ROLE sales_db.admin;

-- Grant database role to account role
GRANT DATABASE ROLE sales_db.reader TO ROLE data_analyst;
GRANT DATABASE ROLE sales_db.admin TO ROLE data_engineer;

-- Future grants on new tables in a schema
GRANT SELECT ON FUTURE TABLES IN SCHEMA my_db.public TO ROLE reader;
GRANT ALL ON FUTURE TABLES IN SCHEMA my_db.public TO ROLE writer;

-- Future grants on new schemas in a database
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE my_db TO ROLE reader;

-- Future grants on new views
GRANT SELECT ON FUTURE VIEWS IN SCHEMA my_db.public TO ROLE reader;

-- View future grants
SHOW FUTURE GRANTS IN SCHEMA my_db.public;
SHOW FUTURE GRANTS IN DATABASE my_db;

-- Revoke future grants
REVOKE SELECT ON FUTURE TABLES IN SCHEMA my_db.public FROM ROLE reader;

-- Grant on existing AND future
GRANT SELECT ON ALL TABLES IN SCHEMA my_db.public TO ROLE reader;
GRANT SELECT ON FUTURE TABLES IN SCHEMA my_db.public TO ROLE reader;

-- Enable MFA for user (user enrolls via Duo Mobile)
ALTER USER admin_user SET MINS_TO_BYPASS_MFA = 0;

-- Enforce MFA at account level for specific roles
-- (No built-in account-level MFA enforcement - must manage per user)

-- Bypass MFA temporarily (emergency)
ALTER USER admin_user SET MINS_TO_BYPASS_MFA = 60;

-- Check MFA status
SELECT name, ext_authn_duo, mins_to_bypass_mfa
FROM SNOWFLAKE.ACCOUNT_USAGE.USERS;

-- Create SCIM integration
CREATE SECURITY INTEGRATION okta_scim
  TYPE = SCIM
  SCIM_CLIENT = 'okta'
  RUN_AS_ROLE = 'GENERIC_SCIM_PROVISIONER';

-- View integration details
DESCRIBE SECURITY INTEGRATION okta_scim;

-- Generate RSA key pair (client side)
-- openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
-- openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

-- Assign public key to user
ALTER USER service_user SET RSA_PUBLIC_KEY = 'MIIBIjANBg...';

-- Rotate keys (supports two active keys)
ALTER USER service_user SET RSA_PUBLIC_KEY_2 = 'MIIBIjANBg...new...';
-- After rotation complete:
ALTER USER service_user SET RSA_PUBLIC_KEY = 'MIIBIjANBg...new...';
ALTER USER service_user UNSET RSA_PUBLIC_KEY_2;

-- Create network policy
CREATE NETWORK POLICY office_only
  ALLOWED_IP_LIST = ('203.0.113.0/24', '198.51.100.0/24')
  BLOCKED_IP_LIST = ('203.0.113.99');

-- Apply at account level
ALTER ACCOUNT SET NETWORK_POLICY = office_only;

-- Apply at user level (overrides account policy)
ALTER USER remote_contractor SET NETWORK_POLICY = contractor_policy;

-- View active policies
DESCRIBE NETWORK POLICY office_only;

-- Grants to a role
SHOW GRANTS TO ROLE data_analyst;

-- Grants on an object
SHOW GRANTS ON TABLE my_db.public.customers;

-- Grants of a role (who has this role)
SHOW GRANTS OF ROLE data_analyst;

-- All grants in account (ACCOUNT_USAGE)
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE deleted_on IS NULL
  AND granted_on = 'TABLE'
  AND name = 'CUSTOMERS';