Create read-only role/user in Snowflake

To Nha Notes | March 7, 2023, 1:23 p.m.

1. Create role READ_ONLY

USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS READ_ONLY;
GRANT ROLE READ_ONLY TO ROLE SYSADMIN;

GRANT USAGE
  ON DATABASE <DB_NAME>
  TO ROLE READ_ONLY;

GRANT USAGE
  ON SCHEMA <DB_NAME>.<SCHEMA_NAME>
  TO ROLE READ_ONLY;

GRANT SELECT
  ON ALL TABLES IN SCHEMA <DB_NAME>.<SCHEMA_NAME>
  TO ROLE READ_ONLY;

GRANT SELECT
ON FUTURE TABLES IN SCHEMA <DB_NAME>.<SCHEMA_NAME> TO ROLE READ_ONLY;

GRANT USAGE
  ON WAREHOUSE <WAREHOUSE_NAME>
  TO ROLE READ_ONLY;

2. Create user READ_ONLY

USE ROLE USERADMIN;
CREATE USER IF NOT EXISTS READ_ONLY
WITH
    DISPLAY_NAME = 'READ_ONLY'
    LOGIN_NAME = 'READ_ONLY'
    PASSWORD = '<PASSWORD>'
    MUST_CHANGE_PASSWORD = FALSE
    DEFAULT_WAREHOUSE = '<DB_NAME>'
    DEFAULT_NAMESPACE = '<DB_NAME>.<SCHEMA_NAME>'
    DEFAULT_ROLE = READ_ONLY
    COMMENT = 'FAQ read only user';

-- Grant the role to user
USE ROLE USERADMIN;
GRANT ROLE READ_ONLY TO USER READ_ONLY;