See why hundreds of industry leaders trust Secoda to unlock their data's full potential.
See why hundreds of industry leaders trust Secoda to unlock their data's full potential.
Get the guideThe SHOW GRANTS command in Snowflake provides a comprehensive overview of all access control privileges explicitly granted to roles, users, and shares within a Snowflake instance. By listing these privileges, it plays a crucial role in managing and auditing access control configurations.
A grant in Snowflake refers to a formal permission or privilege that controls access to Snowflake's resources, such as databases, schemas, tables, or even more granular data elements. These grants determine what actions specific users or roles can perform, such as reading, writing, or executing stored procedures.
In Snowflake, managing grants is a fundamental aspect of enforcing security and governance, ensuring that users only have the access necessary to perform their roles.
The importance of the SHOW GRANTS command in Snowflake cannot be overstated. It plays a critical role in several areas:
The SHOW GRANTS command is designed to display the privileges that have been assigned within a database system. The command can be customized to show different levels of detail depending on what the user needs. Below is a breakdown of its structure.
This is the simplest form of the SHOW GRANTS command. It retrieves all grants across the system.
SHOW GRANTS;
Optional Clause:
LIMIT <rows>: Limits the number of rows returned by the query.
SHOW GRANTS LIMIT 10;
This structure is used to retrieve privileges granted at the account level, meaning it shows what permissions are assigned to the account itself.
SHOW GRANTS ON ACCOUNT;
Optional Clause:
LIMIT <rows>: As with the basic command, you can limit the number of results.
SHOW GRANTS ON ACCOUNT LIMIT 5;
This variation shows privileges granted on specific objects, such as tables, views, or schemas. You must specify the type and name of the object.
SHOW GRANTS ON <object_type> <object_name>;
Optional Clause:
LIMIT <rows>: Limits the number of rows returned by the query.
SHOW GRANTS ON TABLE my_table LIMIT 3;
This form of the command displays privileges granted to specific entities like applications, roles, shares, or users.
SHOW GRANTS TO { APPLICATION <app_name> | ROLE <role_name> | USER <user_name> };
Optional Clause:
LIMIT <rows>: Limits the number of rows returned by the query.
SHOW GRANTS TO USER my_user LIMIT 10;
This command shows the privileges that are assigned to specific roles within the system.
SHOW GRANTS OF ROLE <role_name>;
Optional Clause:
LIMIT <rows>: Limits the number of rows returned by the query.
SHOW GRANTS OF ROLE my_role LIMIT 5;
This structure is used to display privileges that will automatically be granted to objects created in the future within a specific schema or database.
SHOW FUTURE GRANTS IN { SCHEMA <schema_name> | DATABASE <database_name> };
Optional Clause:
LIMIT <rows>: Limits the number of rows returned by the query.
SHOW FUTURE GRANTS IN SCHEMA my_schema LIMIT 7;
This form of the command shows future grants that will be automatically assigned to specific roles.
SHOW FUTURE GRANTS TO ROLE <role_name>;
Optional Clause:
LIMIT <rows>: Limits the number of rows returned by the query.
SHOW FUTURE GRANTS TO ROLE my_role LIMIT 4;
Understanding the nuances of the SHOW GRANTS command is essential for effective use. Here are some critical points to consider:
granted_by column indicates the role that authorized the privilege grant. This role could be the object owner, a role with the grant option, or a role with the global MANAGE GRANTS privilege.SHOW GRANTS … TO SHARE IN APPLICATION PACKAGE syntax includes additional columns such as grantee_name, granted_to, and granted_by_role_type.grant_options column returns FALSE for managed access schemas, while the privilege column includes OWNERSHIP and MANAGE GRANTS for roles owning managed access schemas.While using the SHOW GRANTS command, you might encounter some common challenges or errors. Here are some solutions:
Here are the key takeaways from this tutorial on the SHOW GRANTS command in Snowflake:
Built for data teams, designed for everyone so you can get more from your data stack.
© 2024 Secoda, Inc. All rights reserved.
By using this website, you accept our Terms of Use and Privacy Policy.
