Learn how to efficiently grant SELECT permissions to all tables within a specific schema in Snowflake to the PUBLIC role. Our guide provides a step-by-step approach, including a script for batch processing this grant action. This method ensures secure and controlled access management, allowing you to maintain best practices in database security while simplifying permission assignments. Perfect for administrators looking to streamline their workflow in Snowflake’s cloud data platform.
In Snowflake, to grant the SELECT privilege on all tables within a schema to the PUBLIC role, you would use the GRANT statement. However, Snowflake does not support a direct GRANT SELECT ON ALL TABLES syntax. Instead, you must grant the SELECT privilege on each table individually or use a script to iterate over all tables in a schema.
Here is an example of how you would grant the SELECT privilege on a single table to the PUBLIC role:
GRANT SELECT ON TABLE schema_name.table_name TO ROLE PUBLIC;
To grant the SELECT privilege to all tables within a schema, you would typically create a script that generates the necessary GRANT statements for each table. In Snowflake, you can use a procedure to automate this as follows:
-- Procedure to grant SELECT on all tables in a schema to the PUBLIC role
CREATE OR REPLACE PROCEDURE grant_select_on_all_tables(schema_name STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var query = "SHOW TABLES IN " + SCHEMA_NAME;
var result = snowflake.execute({sqlText: query});
var grant_sql = "";
while (result.next()) {
var tableName = result.getColumnValue(2);
grant_sql += "GRANT SELECT ON TABLE " + SCHEMA_NAME + "." + tableName + " TO ROLE PUBLIC; ";
}
try {
if (grant_sql.length > 0) {
snowflake.execute({sqlText: grant_sql});
}
return "Grants successful.";
} catch (err) {
return "Error: " + err;
}
$$;
-- Then call the procedure with the schema name
CALL grant_select_on_all_tables('YOUR_SCHEMA_NAME');
Always be cautious when granting permissions to the PUBLIC role, as it means every user in the Snowflake account will have those permissions. It’s generally safer to create specific roles with the necessary permissions and grant access to those roles as needed.
Snowflake important urls to refer
