Create database, schema, table then grant permission to Snowflake user role for client (Dremio) to access. – Dremio Support

Overview

Snowflake has the user access control, it controls users’ access to database, schema, table objects through assignment of privileges to roles. Any external clients connect to Snowflake with a specific user role, and want to access Snowflake’s database, schema, or table; it requires to grant privilege of the database, schema, table to the role that external client (like Dremio) use in the connection to connect to Snowflake.

Applies To

All Dremio versions

Use case Details

Summarize the use case for:

Create Snowflake warehouse
Create Snowflake database
Create Snowflake schema
Create Snowflake table
Grant privilege user role to access warehouse, database, schema, and table
Display the privilege of the database and schema that just created
Connect Dremio to Snowflake then retrieve data from Snowflake table

Create Snowflake warehouse

Click on Admin on the left
Click on Warehouses
Click on + Warehouse button on the right
Enter the warehouse name

Grant permission to the role name PUBLIC to access the warehouse name KB_WH

Create Snowflake database

Click on Data on the left
Click on Databases
Then click on the “+ Database” on the top right
Enter database name (ex: KB_DB1)
Click Create button to create the database name KB_DB1

Grant permission to role name PUBLIC to access the database name KB_DB1

Create Snowflake schema

Click on the database name KB_DB1 that just created
Click on the “+ Schema” button on the top right
Enter the schema name (ex: KB_SC1)
Click Create button to create the schema name KB_SC1

Grant permission to the role name PUBLIC to access schema name KB_SC1

Create Snowflake table

Click on the schema name that you want to create a table under it (ex: KB_SC1)
Click on the dropdown Create button on the top right
Then click on Table
Choose the table type that you want to create
The SQL editor window will display, enter the create table SQL statement to create table.
- - In this example: We create a table name SAMPLEEMPLOYEE with the SQL create statement as below:
    create or replace TABLE KB_DB1.KB_SC1. SAMPLEEMPLOYEE (
    EMP_FIRSTNAME VARCHAR (16777216),
    EMP_LASTNAME VARCHAR (16777216),
    EMP_AGE NUMBER (38, 0));

Grant permission to role PUBLIC to access table name SAMPLEEMPLOYEE

Integrate Dremio to Snowflake

Retrieve data from Snowflake table

Troubleshoot:

Case 1: Dremio connects to Snowflake success, but unable to see Snowflake database

Cause:

Snowflake database not displace because the Snowflake user role PUBLIC that use in Dremio connects to Snowflake does not have a privilege to access Snowflake database.

Solution:

Login to Snowflake database portal then grant a privilege to the Snowflake user role to the database that you want to access from client side (ex: Dremio).

After granted permission for user role to access database then the database name will display in Dremio

Case 2: Error when run select statement from Dremio to retrieve data from Snowflake table.

Cause:

Even though the Snowflake user role in Dremio connection has privilege to access Snowflake database and schema, but it does not has access to Snowflake table.

Solution:

Login to Snowflake database portal then grant the Snowflake user role that use in Dremio connection to have permission to access Snowflake table.

After issued the Snowflake user role PUBLIC to have access to table then query in Dremio run success.

Related articles