In the RelationalAI Native App Installation guide, we include instructions for creating two default roles: rai_admin and rai_developer. Typically these are sufficient for most business needs. However, you may want to create a different role to align the name with an internal business team. This article provides the minimum privileges needed for a standard user type of role.
Minimum Privileges
Here we create a standard role called my_role with access to database my_database its schema my_schema and a table in that schema my_table. Replace these with the appropriate names for your desired role and dataset.
CREATE ROLE my_role;
-- Allow the role to access objects in the RelationalAI application.
GRANT APPLICATION ROLE relationalai.rai_user TO ROLE my_role;
-- Assign the role to yourself and any desired additional users.
GRANT ROLE my_role TO USER "username_here"
-- Let the role use this dataset.
GRANT USAGE ON DATABASE my_database TO ROLE my_role;
GRANT USAGE ON SCHEMA my_database.my_schema TO ROLE my_role;
GRANT SELECT ON TABLE my_database.my_schema.my_table TO ROLE my_role;
-- Allow the role to stream data from the source table to the RelationalAI model.
GRANT CREATE STREAM ON SCHEMA my_database.my_schema TO ROLE role_name;
Recommended: Export Results
Allow the role to export results from the RelationalAI model to a results table in Snowflake, either by directly writing query results to a Snowflake table, or by sending the results of a stored procedure to a Snowflake table.
GRANT CREATE SCHEMA ON DATABASE my_database TO ROLE my_role;
GRANT CREATE TABLE ON SCHEMA my_database.my_schema TO ROLE my_role;
Warning: you will not immediately be able to see the results table when they are created. Choose one option below to allow your role to view the results table.
Less restrictive option:
Let the role use any new tables in the schema and database, as well as create new schemas in the designated database. This only needs to be run once while setting up the role.
GRANT CREATE TABLE ON FUTURE SCHEMAS IN DATABASE my_database TO ROLE my_role;
GRANT SELECT ON ALL TABLES IN SCHEMA my_database.my_schema TO ROLE my_role;
GRANT SELECT ON FUTURE TABLES IN DATABASE my_database TO ROLE my_role;
More restrictive option:
Alternatively, after you create a results table, remember to give your role permission to see and use only that one. This needs to be run every time you create a new results table with a new name.
GRANT USAGE ON SCHEMA my_database.results_schema TO ROLE my_role;
GRANT SELECT ON TABLE my_database.results_schema.results_table TO ROLE my_role;
Optional: Snowflake Notebooks
If you would like the role to use Snowflake Notebooks, run these grants below. This operates on the names of objects from the standard installation. If you are using custom object names, change the name of the database, schema, warehouse, or compute pool as appropriate.
Notebook Integrations
The notebook integrations were created during the RelationalAI app installation. The pypi_access_integration is required for container notebooks. It allows you to install the RelationalAI package with pip install relationalai. Alternatively, the S3_RAI_INTERNAL_BUCKET_EGRESS_INTEGRATION is required for warehouse notebooks. Its allows you to store the RelationalAI package in location accessible for local import.
-- Let the role use the default objects for running and storing Snowflake notebooks
GRANT USAGE ON DATABASE rai_notebooks TO ROLE my_role;
GRANT USAGE ON SCHEMA rai_notebooks.public TO ROLE my_role;
GRANT CREATE NOTEBOOK ON SCHEMA rai_notebooks.public TO ROLE my_role;
GRANT USAGE ON WAREHOUSE notebooks_wh TO ROLE my_role;
GRANT USAGE ON COMPUTE POOL NOTEBOOK_CPU_XS TO ROLE my_role;
GRANT CREATE SERVICE ON SCHEMA rai_notebooks.public TO ROLE my_role;
-- Allow the role to use the Snowflake notebook integrations.
GRANT USAGE ON INTEGRATION pypi_access_integration TO ROLE my_role;
GRANT USAGE ON INTEGRATION S3_RAI_INTERNAL_BUCKET_EGRESS_INTEGRATION TO ROLE my_role;
Check Settings
Double check that privileges have been assigned correctly for a role, table, or user:
SHOW GRANTS TO ROLE my_role;
SHOW FUTURE GRANTS TO ROLE my_role;
SHOW GRANTS ON TABLE my_database.my_schema.my_table;
-- Check your own grants
SHOW GRANTS;
-- Check grants for another user, replace the username
SHOW GRANTS TO USER "genevieve@relational.ai";
For more information see: