snowflake-enterprise-rbac

Configure Snowflake enterprise RBAC with system roles, custom role hierarchies, SSO/SCIM integration, and least-privilege access patterns. Use when implementing role-based access control, configuring SSO with SAML/OIDC, or setting up organization-level governance in Snowflake. Trigger with phrases like "snowflake RBAC", "snowflake roles", "snowflake SSO", "snowflake SCIM", "snowflake permissions", "snowflake access control".

Jump to install

Repository: jeremylongshore/claude-code-plugins-plus-skills
Last source activity: July 17, 2026 at 22:18
Detected SKILL.md language: English
Stars: 2,604
Forks: 378

Install options

The review-first prompt is selected by default. You can switch to a direct command or download a local copy.

Review the source files

Read SKILL.md and any companion files shown by SkillsMP before deciding whether to install.

Back to search Back to installation

Snowflake Enterprise RBAC

Overview

Configure enterprise-grade access control using Snowflake's system-defined roles, custom role hierarchies, SSO via SAML/OIDC, and SCIM for automated user provisioning.

Snowflake System Roles

Role	Purpose	Use For
ACCOUNTADMIN	Top-level admin	Billing, resource monitors, replication
SECURITYADMIN	Security management	Users, roles, grants, network policies
SYSADMIN	Object management	Databases, warehouses, schemas, tables
USERADMIN	User management	Create users and roles
PUBLIC	Default for all users	Minimal access, applied automatically

Best Practice: Never use ACCOUNTADMIN as a default role. Create custom roles and grant them to SYSADMIN.

Instructions

Step 1: Design Custom Role Hierarchy

-- Functional roles (what people do)
CREATE ROLE DATA_ENGINEER;
CREATE ROLE DATA_ANALYST;
CREATE ROLE DATA_SCIENTIST;
CREATE ROLE BI_VIEWER;
CREATE ROLE APP_SERVICE;         -- Service accounts

-- Access roles (what they can access)
CREATE ROLE RAW_DATA_READER;
CREATE ROLE CURATED_DATA_READER;
CREATE ROLE CURATED_DATA_WRITER;
CREATE ROLE GOLD_DATA_READER;

-- Role hierarchy (bottom-up)
--   BI_VIEWER → GOLD_DATA_READER
--   DATA_ANALYST → CURATED_DATA_READER + GOLD_DATA_READER
--   DATA_SCIENTIST → DATA_ANALYST + RAW_DATA_READER
--   DATA_ENGINEER → all access roles
--   All custom roles → SYSADMIN

GRANT ROLE GOLD_DATA_READER TO ROLE BI_VIEWER;
GRANT ROLE CURATED_DATA_READER TO ROLE DATA_ANALYST;
GRANT ROLE GOLD_DATA_READER TO ROLE DATA_ANALYST;
GRANT ROLE DATA_ANALYST TO ROLE DATA_SCIENTIST;
GRANT ROLE RAW_DATA_READER TO ROLE DATA_SCIENTIST;
GRANT ROLE RAW_DATA_READER TO ROLE DATA_ENGINEER;
GRANT ROLE CURATED_DATA_READER TO ROLE DATA_ENGINEER;
GRANT ROLE CURATED_DATA_WRITER TO ROLE DATA_ENGINEER;
GRANT ROLE GOLD_DATA_READER TO ROLE DATA_ENGINEER;

-- All custom roles under SYSADMIN
GRANT ROLE DATA_ENGINEER TO ROLE SYSADMIN;
GRANT ROLE DATA_ANALYST TO ROLE SYSADMIN;
GRANT ROLE DATA_SCIENTIST TO ROLE SYSADMIN;
GRANT ROLE BI_VIEWER TO ROLE SYSADMIN;
GRANT ROLE APP_SERVICE TO ROLE SYSADMIN;

Step 2: Grant Object Privileges

-- Access role: RAW_DATA_READER
GRANT USAGE ON DATABASE PROD_DW TO ROLE RAW_DATA_READER;
GRANT USAGE ON SCHEMA PROD_DW.BRONZE TO ROLE RAW_DATA_READER;
GRANT SELECT ON ALL TABLES IN SCHEMA PROD_DW.BRONZE TO ROLE RAW_DATA_READER;
GRANT SELECT ON FUTURE TABLES IN SCHEMA PROD_DW.BRONZE TO ROLE RAW_DATA_READER;

-- Access role: CURATED_DATA_READER
GRANT USAGE ON DATABASE PROD_DW TO ROLE CURATED_DATA_READER;
GRANT USAGE ON SCHEMA PROD_DW.SILVER TO ROLE CURATED_DATA_READER;
GRANT SELECT ON ALL TABLES IN SCHEMA PROD_DW.SILVER TO ROLE CURATED_DATA_READER;
GRANT SELECT ON FUTURE TABLES IN SCHEMA PROD_DW.SILVER TO ROLE CURATED_DATA_READER;

-- Access role: CURATED_DATA_WRITER
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA PROD_DW.SILVER TO ROLE CURATED_DATA_WRITER;
GRANT INSERT, UPDATE, DELETE  FUTURE TABLES  SCHEMA PROD_DW.SILVER  ROLE CURATED_DATA_WRITER;


 USAGE  DATABASE PROD_DW  ROLE GOLD_DATA_READER;
 USAGE  SCHEMA PROD_DW.GOLD  ROLE GOLD_DATA_READER;
    TABLES  SCHEMA PROD_DW.GOLD  ROLE GOLD_DATA_READER;
   FUTURE TABLES  SCHEMA PROD_DW.GOLD  ROLE GOLD_DATA_READER;


 USAGE  WAREHOUSE ETL_WH  ROLE DATA_ENGINEER;
 USAGE  WAREHOUSE ANALYTICS_WH  ROLE DATA_ANALYST;
 USAGE  WAREHOUSE ANALYTICS_WH  ROLE DATA_SCIENTIST;
 USAGE  WAREHOUSE DASHBOARD_WH  ROLE BI_VIEWER;

Step 3: Configure SSO with SAML

-- Create SAML security integration
CREATE OR REPLACE SECURITY INTEGRATION saml_sso
  TYPE = SAML2
  ENABLED = TRUE
  SAML2_ISSUER = 'https://idp.company.com/saml/metadata'
  SAML2_SSO_URL = 'https://idp.company.com/saml/sso'
  SAML2_PROVIDER = 'OKTA'        -- Or 'ADFS', 'CUSTOM'
  SAML2_X509_CERT = '-----BEGIN CERTIFICATE-----
MIIBIj...
-----END CERTIFICATE-----'
  SAML2_SP_INITIATED_LOGIN_PAGE_LABEL = 'Company SSO'
  SAML2_ENABLE_SP_INITIATED = TRUE
  SAML2_SNOWFLAKE_ACS_URL = 'https://myorg-myaccount.snowflakecomputing.com/fed/login'
  SAML2_SNOWFLAKE_ISSUER_URL = 'https://myorg-myaccount.snowflakecomputing.com';

-- Map IdP groups to Snowflake roles (done in IdP, not SQL)
-- Okta: Group "Engineering" → Snowflake role "DATA_ENGINEER"
-- Okta: Group "Analytics" → Snowflake role "DATA_ANALYST"

Step 4: Configure SCIM for Automated User Provisioning

-- Create SCIM integration (users/roles synced from IdP automatically)
CREATE OR REPLACE SECURITY INTEGRATION scim_provisioning
  TYPE = SCIM
  SCIM_CLIENT = 'OKTA'           -- Or 'AZURE', 'GENERIC'
  RUN_AS_ROLE = 'SECURITYADMIN';

-- Get SCIM endpoint and token for IdP configuration
SELECT SYSTEM$GENERATE_SCIM_ACCESS_TOKEN('scim_provisioning');

-- SCIM auto-creates users and assigns roles based on IdP groups
-- No manual user creation needed after SCIM is configured

Step 5: Audit Role Grants

-- All current role grants
SELECT grantee_name, role,
       granted_by, created_on
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
WHERE deleted_on IS NULL
ORDER BY grantee_name;

-- Users with ACCOUNTADMIN (should be minimal)
SELECT grantee_name, role, granted_by
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
WHERE role = 'ACCOUNTADMIN' AND deleted_on IS NULL;

-- Unused privileges (granted but never used)
SELECT DISTINCT granted_on, name, privilege, grantee_name
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES gtr
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah
  ON gtr.name = ah.direct_objects_accessed[0]:objectName
WHERE ah.query_id IS NULL
  AND gtr.deleted_on IS NULL
  AND gtr.granted_on = 'TABLE';

RBAC Checklist

No human user has ACCOUNTADMIN as default role
Custom roles follow functional + access role pattern
All custom roles roll up to SYSADMIN
GRANT ... ON FUTURE used for new objects
SSO configured with IdP group-to-role mapping
SCIM enabled for automated provisioning/deprovisioning
Quarterly audit of role grants and unused privileges
Service accounts use key pair auth with dedicated roles

Error Handling

Issue	Cause	Solution
SSO login fails	Wrong SAML config	Verify ACS URL and certificate
Role not inherited	Missing role grant	Check hierarchy with `SHOW GRANTS OF ROLE x`
SCIM sync fails	Token expired	Regenerate SCIM access token
Future grants not applying	Schema not included	Add `ON FUTURE` grants per schema

Resources

Next Steps

For major platform migrations, see snowflake-migration-deep-dive.