The world is becoming increasingly computerized. Soon, every part of our lives will be connected to the Internet, providing consumers with more conveniences such as instant access to information. While this is a positive development, the sheer volume of data generated as a result of digitalization is staggering. Snowflake is one such Cloud Data Warehouse that is helping out users manage this colossal volume of data by establishing efficient development strategies through its application.
Snowflake allows for granular control over object access, including who can access what objects, what operations can be performed on those objects, and who can create or change access control policies.
In this article, you will gain information about Show Privileges Snowflake using SHOW GRANTS Command. You will also gain a holistic understanding of Snowflake, its key features, and steps for building Snowflake Apps. Read along to find out in-depth information about Show Privileges Snowflake using SHOW GRANTS Command.
Table of Contents
What is Snowflake?
Image Source
Snowflake is a Data Warehouse-as-a-service platform built for the cloud. Its data architecture is different from that of Amazon Redshift because it uses the scalable, elastic Azure Blobs Storage as the internal storage engine and Azure Data Lake to store the unstructured, structured, and on-premise data ingested via the Azure Data Factory.
The Snowflake Data Warehouse provides security and protection of data using Amazon S3 policy controls, SSO, Azure SAS tokens, and Google Cloud Storage access permissions. You can also scale your storage depending on your storage needs.
The key benefit of leveraging Snowflake are as follows:
- Given the elastic nature of the cloud, you can scale up your virtual warehouse to take advantage of extra compute resources to say run a high volume of queries, or load data faster.
- With Snowflake you can combine semistructured and structured data for analysis and load it into the database without the need to transform or convert it into a fixed relational schema beforehand.
- Snowflake has a multi-cluster architecture that takes care of concurrency issues like failures and delays.
- Snowflake’s architecture enables companies to leverage it to seamlessly share data with any data consumer.
Key Features of Snowflake
Image Source
Here are some of the benefits of using Snowflake as a Software as a Service (SaaS) solution:
- Snowflake enables you to enhance your Analytics Pipeline by transitioning from nightly batch loads to real-time data streams, allowing you to improve the quality and speed of your analytics. By allowing safe, concurrent, and controlled access to your Data Warehouse across your organization, you can improve the quality of analytics at your company.
- Snowflake uses the caching paradigm to swiftly deliver the results from the cache. To avoid re-generation of the report when nothing has changed, Snowflake employs persistent (within the session) query results.
- Snowflake allows you to break down data silos and provide access to meaningful insights across the enterprise, resulting in better data-driven decision-making. This is a crucial first step toward bettering partner relationships, optimizing pricing, lowering operational expenses, increasing sales effectiveness, and more.
- Snowflake allows you to better understand user behavior and product usage. You can also use the whole scope of data to ensure customer satisfaction, drastically improve product offers, and foster Data Science innovation.
- Snowflake allows you to create your own Data Exchange, which allows you to securely communicate live, controlled data. It also encourages you to improve data relationships throughout your business units, as well as with your partners and customers.
- Secure Data Lake: You can use a secure Data Lake to store all compliance and cybersecurity data in one place. Snowflake Data Lakes ensure quick incident response times. This allows you to understand the complete picture of an incident by clubbing high-volume log data in a single location, and efficiently analyzing years of log data in seconds.
A fully managed No-code Data Pipeline platform like Hevo Data helps you integrate and load data from 100+ different sources (including 40+ free sources) to a Data Warehouse such as Snowflake or Destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources allows users to bring in data of different kinds in a smooth fashion without having to code a single line.
Get Started with Hevo for Free
Check out some of the cool features of Hevo:
- Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
- Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
- Connectors: Hevo supports 100+ integrations to SaaS platforms, files, Databases, analytics, and BI tools. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 Data Lakes; and MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL Databases to name a few.
- Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
- 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
- Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (including 40+ free sources) that can help you scale your data infrastructure as required.
- 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!
What is SQL for Snowflake?
Image Source
Snowflake’s Data Cloud platform has a data warehouse workload that supports the most widely used standardized SQL version (ANSI) for comprehensive relational database querying. It may also combine semi-structured data, such as JSON, with structured data, such as SQL. Snowflake makes JSON data more accessible and allows users to integrate it with structured data.
Snowflake allows users to access JSON data with SQL queries and seamlessly join it to traditional tabular data. This innovative querying strategy enables users to store JSON documents in a relational database using a new data type (VARIANT) that is automatically optimized in the background for columnar and MPP access.
The different types of SQL Commands supported by Snowflake are:
- DDL (Data Definition Language) Commands
- DML (Data Manipulation Language) Commands
- Query Syntax & Operators
- TCL (Transaction Control Language) Commands
What are DDL Commands?
DDL stands for Data Definition Language. Snowflake objects including users, virtual warehouses, databases, schemas, tables, views, columns, functions, and stored procedures are created, manipulated, and modified using DDL commands.
They are also used to conduct several account-level and session-level operations, such as parameter setting, variable initialization, and transaction initiation.
The following commands serve as the base for all DDL commands:
- ALTER <object>
- COMMENT
- CREATE <object>
- DESCRIBE <object>
- DROP <object>
- SHOW <objects>
- USE <object>
Each of the above DDL commands requires an object type and an identifier. The remaining parameters and options available for the command are object-specific.
The different categories of DDL Commands are as follows:
- Account & Session DDL Commands
- User & Security DDL Commands
- Warehouse & Resource Monitor DDL Commands
- Database, Schema, & Share DDL Commands
- Table, View, & Sequence DDL Commands
- Data Loading / Unloading DDL Commands
- DDL Commands for User-Defined Functions, External Functions, and Stored Procedures
- Data Pipeline DDL Commands
What are User & Security DDL Commands?
Snowflake has a comprehensive collection of SQL commands for managing users and security. These commands can only be used by users who have been granted roles with the OWNERSHIP privilege on the managed item. The ACCOUNTADMIN and SECURITYADMIN roles are generally the only ones that can do this.
However, individual users can perform the following tasks for themselves:
- Change their password (only through the web interface).
- View their user information (via DESCRIBE USER).
- Change their default role, virtual warehouse, or namespace (via ALTER USER).
- Change their session parameters (via ALTER SESSION).
The different types of User & Security DDL Commands fall under the following categories:
- User Management
- Role Management
- Object Tagging Management
- Access Control Management
- Network Policy Management
- Session Policy Management
- Third-Party Integrations
- API Integrations
- Notification Integrations
- Security Integrations
- Storage Integrations
Understanding Access Control Management
You can use the following commands to grant (and revoke) Object Privileges to Roles, as well as roles to users and other roles, to manage object access control:
- GRANT <privileges> … TO ROLE
- REVOKE <privileges> … FROM ROLE
- GRANT <privilege> … TO SHARE
- REVOKE <privilege> … FROM SHARE
- GRANT OWNERSHIP
- GRANT ROLE
- REVOKE ROLE
- SHOW GRANTS
What are Privileges in Snowflake?
Each securable object has a set of Privileges that can be assigned to or granted on it. Privileges must be granted on an individual object basis for existing objects. Future grants simplify grant management by allowing the definition of an initial set of privileges on objects created in a schema.
The GRANT and REVOKE commands are used to manage privileges.
- In Regular (non-managed) Schemas, these commands are only available to roles that own an object (i.e. have the OWNERSHIP privilege on the object) or roles that have the MANAGE GRANTS global privilege for the object (typically the SECURITYADMIN role).
- Object owners lose the ability to make grant decisions in a Managed Access Schema. Only the schema owner or a role with the MANAGE GRANTS privilege can grant privileges on schema objects, including future grants, thereby centralizing privilege management.
NOTE: A role with the global MANAGE GRANTS privilege has the ability to grant additional privileges to the current (grantor) role.
What are SHOW <objects> Commands?
Before knowing about Show privileges Snowflake using SHOW GRANTS command, let’s have a look on SHOW <objects> command.
It returns a list of existing objects for the specified object type. The output contains metadata for the objects, such as:
- Common properties (name, creation timestamp, owning role, comment, etc.)
- Object-specific properties
1) Syntax
SHOW <object_type_plural> [ LIKE '<pattern>' ] [ IN <scope_object_type> [ <scope_object_name> ] ]
2) Usage
The uses of SHOW commands are as follows:
- SHOW commands do not need to be executed in a running warehouse.
- SHOW commands only return objects for which the current user’s current role has the required access privileges. For example:
- The databases for which the user’s role has the OWNERSHIP or USAGE privilege are listed in the SHOW DATABASES output.
- The output of SHOW SCHEMAS includes the schemas for which the user’s role has the OWNERSHIP or USAGE privilege.
- The SHOW TABLES output includes any tables that the user’s role has access to. In addition, the role must have the USAGE privilege on the parent database and schema.
- The MANAGE GRANTS access privilege implicitly grants the holder access to all objects in the account. The MANAGE GRANTS privilege is granted by default to account administrators (users with the ACCOUNTADMIN role) and security administrators (users with the SECURITYADMIN role).
The following clauses can be used to control the output of most SHOW commands:
- The LIKE clause, which is optional, can be used to filter the list of objects returned by name.
- The database object types include an additional, optional IN clause that can be used to limit the command’s scope to a specific schema or database, or to the entire account.
What is SHOW GRANTS Command?
SHOW GRANTS command lists all access control privileges that have been explicitly granted to roles, users, and shares.
To understand the show privileges Snowflake using SHOW GRANTS Command, you need to understand them:
1) Syntax
The syntax of SHOW GRANTS Command to showcase show privileges Snowflake is as follows:
SHOW GRANTS ON ACCOUNT
SHOW GRANTS ON <object_type> <object_name>
SHOW GRANTS TO { ROLE <role_name> | USER <user_name> | SHARE <share_name> }
SHOW GRANTS OF ROLE <role_name>
SHOW GRANTS OF SHARE <share_name>
SHOW FUTURE GRANTS IN SCHEMA { <schema_name> }
SHOW FUTURE GRANTS IN DATABASE { <database_name> }
2) Variants
The different variants of SHOW GRANTS command to showcase show Privileges Snowflake are as follows:
I) SHOW GRANTS ON …
a) ACCOUNT
It displays a list of all the account-level (i.e. global) privileges that have been granted to roles.
b) object_type object_name
It displays a list of all the privileges that have been granted to the object.
II) SHOW GRANTS TO …
a) ROLE role_name
It lists all of the role’s privileges and roles.
b) USER user_name
It displays a list of all the roles that have been assigned to the user. It should be noted that the PUBLIC role, which is available to all users by default, is not listed.
c) SHARE share_name
It lists all of the rights granted to the share.
III) SHOW GRANTS OF…
a) ROLE role_name
It displays a list of all users and roles to whom the role has been granted.
b) SHARE share_name
It lists all of the accounts for the share and indicates which accounts are using it.
IV) SHOW FUTURE GRANTS IN …
a) SCHEMA <database_name.><schema_name>
It lists all privileges granted to a role on new (i.e. future) objects of a specified type in the schema. When querying a schema in the current database, database_name specifies the database in which the schema resides and it is also optional.
b) DATABASE database_name
It lists all privileges granted to a role on new (i.e. future) objects of a specified type in the database.
3) Usage
The SHOW GRANTS command should follow the following criteria:
- The command does not need to be executed in a running warehouse.
- The command returns a maximum of 10K records for the specified object type, as determined by the access privileges of the role used to execute the command; any records exceeding the 10K limit are not returned, even when a filter is applied.
- To see results with more than 10K records, you can use the Information Schema to query the corresponding view (if one exists).
- You can use the RESULT_SCAN function to post-process the output of this command, which treats the output as a table that can be queried.
4) Examples
1) The below command displays the list of all privileges that have been granted on the sales database.
show grants on database sales;
Output:
+---------------------------------+-----------+------------+------------+------------+--------------+--------------+--------------+
| created_on | privilege | granted_on | name | granted_to | grantee_name | grant_option | granted_by |
|---------------------------------+-----------+------------+------------+------------+--------------+--------------+--------------|
| Thu, 07 Jul 2016 05:22:29 -0700 | OWNERSHIP | DATABASE | REALESTATE | ROLE | ACCOUNTADMIN | true | ACCOUNTADMIN |
| Thu, 07 Jul 2016 12:14:12 -0700 | USAGE | DATABASE | REALESTATE | ROLE | PUBLIC | false | ACCOUNTADMIN |
+---------------------------------+-----------+------------+------------+------------+--------------+--------------+--------------+
2) The below command displays the list of all privileges granted to the analyst role.
show grants to role analyst;
Output:
+---------------------------------+------------------+------------+------------+------------+--------------+------------+
| created_on | privilege | granted_on | name | granted_to | grant_option | granted_by |
|---------------------------------+------------------+------------+------------+------------+--------------+------------+
| Wed, 17 Dec 2014 18:19:37 -0800 | CREATE WAREHOUSE | ACCOUNT | DEMOENV | ANALYST | false | SYSADMIN |
+---------------------------------+------------------+------------+------------+------------+--------------+------------+
3) The below command displays the list of all the roles granted to the demo user.
show grants to user demo;
Output:
+---------------------------------+------+------------+-------+---------------+
| created_on | role | granted_to | name | granted_by |
|---------------------------------+------+------------+-------+---------------+
| Wed, 31 Dec 1969 16:00:00 -0800 | DBA | USER | DEMO | SECURITYADMIN |
+---------------------------------+------+------------+-------+---------------+
4) The below command displays the list of all roles and users who have been granted the analyst role.
show grants of role analyst;
Output:
+---------------------------------+---------+------------+--------------+---------------+
| created_on | role | granted_to | grantee_name | granted_by |
|---------------------------------+---------+------------+--------------+---------------|
| Tue, 05 Jul 2016 16:16:34 -0700 | ANALYST | ROLE | ANALYST_US | SECURITYADMIN |
| Tue, 05 Jul 2016 16:16:34 -0700 | ANALYST | ROLE | DBA | SECURITYADMIN |
| Fri, 08 Jul 2016 10:21:30 -0700 | ANALYST | USER | JOESM | SECURITYADMIN |
+---------------------------------+---------+------------+--------------+---------------+
5) The below command displays a list of all privileges granted on future objects in the sales.public schema.
show future grants in schema sales.public;
Output:
+-------------------------------+-----------+----------+---------------------------+----------+-----------------------+--------------+
| created_on | privilege | grant_on | name | grant_to | grantee_name | grant_option |
|-------------------------------+-----------+----------+---------------------------+----------+-----------------------+--------------|
| 2018-12-21 09:22:26.946 -0800 | INSERT | TABLE | SALES.PUBLIC.<TABLE> | ROLE | ROLE1 | false |
| 2018-12-21 09:22:26.946 -0800 | SELECT | TABLE | SALES.PUBLIC.<TABLE> | ROLE | ROLE1 | false |
+-------------------------------+-----------+----------+---------------------------+----------+-----------------------+--------------+
For further information about privileges and roles, you can see Access Control in Snowflake.
Conclusion
In this article, you have learned about Show Privileges Snowflake using Show Privileges Command. This article also provided information on Snowflake, its key features, and the different ways of showcasing and using Show Privileges Snowflake in detail. For further information on Snowflake Primary Key Constraint, Cast & Try_cast Commands, Copy command, you can visit the following links.
Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.
Visit our Website to Explore Hevo
Hevo Data with its strong integration with 100+ data sources (including 40+ Free Sources) allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows integrating data from non-native sources using Hevo’s in-built Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools.
Want to give Hevo a try?
Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.
Share your experience of understanding Show Privileges Snowflake using Show Grant command in the comment section below! We would love to hear your thoughts.