Snowflake Grants : Read only

Read-only grants in Snowflake are critical for several reasons, especially when it comes to ensuring data security, compliance, and operational efficiency. These grants allow users and roles to access data without the risk of modifying it, which is essential in various use cases like business intelligence (BI), analytics, reporting, and auditing.

Read (Select) Grants : Database and Schema

In Snowflake, grants at both the database and schema levels are used to control access to various database objects and operations. By combining grants, Snowflake allows you to tailor access controls to the specific needs of different users and roles, ensuring a secure and well-governed data environment.

• Example Scenario: If you want a role, analyst_read_role, to have access to read order table from sales schema in company_db database but not be able to modify anything, you would:

1. Grant USAGE on the database.
2. Grant USAGE on the schema.
3. Grant SELECT on table within that schema.

use role accountadmin;
-- Grant USAGE on the database to allow visibility of the database
grant USAGE ON DATABASE company_db
TO ROLE analyst_read_role;

-- Grant USAGE on the specific schema to allow visibility 
-- of the schema and its objects
grant USAGE ON SCHEMA company_db.sales
TO ROLE analyst_read_role;

-- Grant SELECT on order table in the schema to 
-- allow read-only access
grant SELECT ON TABLE company_db.sales.order
TO ROLE analyst_read_role;

• Give SELECT privilege on all tables for a company_db database:

use role accountadmin;
grant SELECT on all tables in database company_db
to role analyst_read_role;
grant SELECT on all tables in schema company_db.sales 
to role analyst_read_role;

• Give FUTURE SELECT access to tables in company_db database: In Snowflake, future access refers to the ability to automatically grant privileges to new objects that are created in the future within a specific schema, database, or other container object. This is useful for managing permissions efficiently, especially in dynamic environments where new tables, views, or other objects are frequently created.

use role accountadmin;
grant SELECT on future tables in database company_db
to role analyst_read_role;
grant SELECT on future tables in schema company_db.sales
to role analyst_read_role;

Snowflake grants are not limited to just tables. Grants can be applied to a variety of database objects, allowing for fine-grained control over who can access and manipulate data and perform certain actions. The concept of grants in Snowflake extends to many types of objects such as schemas, databases, views, stages, sequences, functions, procedures, and more.

Common Use Cases for Read-Only Grants

1. Monitoring and Alerting Systems
2. Reporting
3. Data Science and Machine Learning
4. Triage, Auditing and Compliance
5. Secure Data Sharing

Conclusion:

Read-only grants are a best practice for maintaining data security, integrity, and compliance in Snowflake. They are essential for safe data access in BI, reporting, and data-sharing scenarios, ensuring that users can access and analyze data without the risk of modifying it.

References

Again, A huge thanks to the Snowflake documentation, community and all the resources available that made this write-up possible

1. Snowflake Grants
2. Future grants
3. Infographics via Canva

Disclaimer: This article is AI-assisted. The article structure and idea list are 100% manually curated and researched. I proofread all AI-generated texts to ensure information accuracy and to add some contexts