Access Control Setup in SQL¶
For reference, here are the SQL instructions to set up access control for a feature store. Note that in the first block, there are several SET commands that tell the script the names you want to use for your producer and consumer roles as well as the database and schema where the feature views will be stored. All of these objects are created if they do not exist.
-- Initialize variables for usage in SQL scripts below
SET FS_ROLE_PRODUCER = '<FS_PRODUCER_ROLE>';
SET FS_ROLE_CONSUMER = '<FS_CONSUMER_ROLE>';
SET FS_DATABASE = '<FS_DATABASE_NAME>';
SET FS_SCHEMA = '<FS_SCHEMA_NAME>';
SET FS_WAREHOUSE = '<FS_WAREHOUSE>';
-- Create schema
SET SCHEMA_FQN = CONCAT($FS_DATABASE, '.', $FS_SCHEMA);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($SCHEMA_FQN);
-- Create roles
CREATE ROLE IF NOT EXISTS IDENTIFIER($FS_ROLE_PRODUCER);
CREATE ROLE IF NOT EXISTS IDENTIFIER($FS_ROLE_CONSUMER);
-- Build role hierarchy
GRANT ROLE IDENTIFIER($FS_ROLE_PRODUCER) TO ROLE SYSADMIN;
GRANT ROLE IDENTIFIER($FS_ROLE_CONSUMER) TO ROLE IDENTIFIER($FS_ROLE_PRODUCER);
-- Grant PRODUCER role privileges
GRANT CREATE DYNAMIC TABLE ON SCHEMA IDENTIFIER($SCHEMA_FQN) TO ROLE IDENTIFIER($FS_ROLE_PRODUCER);
GRANT CREATE VIEW ON SCHEMA IDENTIFIER($SCHEMA_FQN) TO ROLE IDENTIFIER($FS_ROLE_PRODUCER);
GRANT CREATE TAG ON SCHEMA IDENTIFIER($SCHEMA_FQN) TO ROLE IDENTIFIER($FS_ROLE_PRODUCER);
GRANT CREATE DATASET ON SCHEMA IDENTIFIER($SCHEMA_FQN) TO ROLE IDENTIFIER($FS_ROLE_PRODUCER);
-- Grant CONSUMER role privileges
GRANT USAGE ON DATABASE IDENTIFIER($FS_DATABASE) TO ROLE IDENTIFIER($FS_ROLE_CONSUMER);
GRANT USAGE ON SCHEMA IDENTIFIER($SCHEMA_FQN) TO ROLE IDENTIFIER($FS_ROLE_CONSUMER);
GRANT SELECT,MONITOR ON FUTURE DYNAMIC TABLES IN SCHEMA IDENTIFIER($SCHEMA_FQN) TO ROLE IDENTIFIER($FS_ROLE_CONSUMER);
GRANT SELECT,MONITOR ON ALL DYNAMIC TABLES IN SCHEMA IDENTIFIER($SCHEMA_FQN) TO ROLE IDENTIFIER($FS_ROLE_CONSUMER);
GRANT SELECT,REFERENCES ON FUTURE VIEWS IN SCHEMA IDENTIFIER($SCHEMA_FQN) TO ROLE IDENTIFIER($FS_ROLE_CONSUMER);
GRANT SELECT,REFERENCES ON ALL VIEWS IN SCHEMA IDENTIFIER($SCHEMA_FQN) TO ROLE IDENTIFIER($FS_ROLE_CONSUMER);
GRANT USAGE ON FUTURE DATASETS IN SCHEMA IDENTIFIER($SCHEMA_FQN) TO ROLE IDENTIFIER($FS_ROLE_CONSUMER);
GRANT USAGE ON ALL DATASETS IN IDENTIFIER($SCHEMA_FQN) TO ROLE IDENTIFIER($FS_ROLE_CONSUMER);
-- [Optional] Grant USAGE ON WAREHOUSE to CONSUMER
GRANT USAGE ON WAREHOUSE IDENTIFIER($FS_WAREHOUSE) TO ROLE IDENTIFIER($FS_ROLE_CONSUMER);