Snowflake Tool Permissions Prerequisites

Permissions overview

General guidelines

• Create the Snowflake user while signed in with an ACCOUNTADMIN role.

• Assign required roles and a DEFAULT_WAREHOUSE to ensure efficient query processing.

• Grant USAGE on all schemas in the target databases to simplify access management.

Database and warehouse roles

Database roles

• USAGE: Lists the database and allows metadata queries.

• CREATE SCHEMA: Creates schemas within the database.

• CREATE TABLE: Creates tables within the database.

• SELECT: Queries data from tables.

• INSERT: Inserts data into tables.

• UPDATE: Updates table data.

• DELETE: Deletes table data.

• REFERENCES: Creates foreign-key relationships.

Warehouse roles

• USAGE: Runs queries on the warehouse.

• MONITOR: Reviews warehouse usage and performance.

• OPERATE: Starts, stops, and resizes the warehouse.

• OWNERSHIP: Grants full control over the warehouse.

Set up Snowflake permissions

Follow these steps to provision a dedicated Cloudera Octopai user and assign the necessary Snowflake roles and privileges.

1. Create a dedicated user

Run the following SQL to create a Snowflake user for Cloudera Octopai metadata extraction:

CREATE USER <username>
  PASSWORD = '<password>'
  DEFAULT_WAREHOUSE = '<warehouse>'
  MUST_CHANGE_PASSWORD = false;
        

Result: the user appears in Snowflake.

2. Create a dedicated role

Create a role to encapsulate Cloudera Octopai permissions:

CREATE ROLE <role_name>;

Result: the role is registered in Snowflake.

3. Assign the role to the user

Grant the role and set it as the default:

GRANT ROLE <role_name> TO USER <username>;

ALTER USER <username> SET DEFAULT_ROLE = <role_name>;

4. Grant warehouse usage

Allow the role to run workloads on the chosen warehouse:

GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE <role_name>;

Verify warehouse grants when needed:

SHOW GRANTS ON WAREHOUSE <warehouse_name>;

5. Grant imported privileges on the Snowflake database

Provide access to the shared SNOWFLAKE database:

GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE <role_name>;

6. Grant object-specific permissions

Use the following commands as required for your environment:

Functions

GRANT USAGE ON FUNCTION <db_name>.<schema_name>.<function_name>(<datatype1>,<datatype2>, ...) TO ROLE <role_name>;

Procedures

GRANT USAGE ON PROCEDURE <db_name>.<schema_name>.<procedure_name>(<datatype1>,<datatype2>, ...) TO ROLE <role_name>;

Pipes

GRANT MONITOR, OPERATE ON PIPE <database_name>.<schema_name>.<pipe_name> TO ROLE <role_name>;

Dynamic tables

GRANT USAGE ON DATABASE <db_name> TO ROLE <role_name>;
GRANT USAGE ON SCHEMA <schema_name> TO ROLE <role_name>;
GRANT SELECT ON ALL DYNAMIC TABLES IN SCHEMA <schema_name> TO ROLE <role_name>;
        

Optional: enable access to future dynamic tables.

GRANT SELECT ON FUTURE DYNAMIC TABLES IN SCHEMA <schema_name> TO ROLE <role_name>;

Troubleshooting checks

Use these queries to validate role assignments and investigate issues:

• Check database-level grants:

SHOW GRANTS TO ROLE <role_name> ON DATABASE <database_name>;

• Review default warehouse and role settings:

SHOW USERS LIKE '%<username>%';

SHOW ROLES LIKE '%<role_name>%';

• Confirm warehouse grants:

SHOW GRANTS ON WAREHOUSE <warehouse_name>;

• Inspect query history logs through the Snowflake UI.

Revoke warehouse permissions

Use revoke statements when access must be removed:

REVOKE <privilege> ON WAREHOUSE <warehouse_name> FROM ROLE <role_name>;

Example:

REVOKE MONITOR, OPERATE, USAGE ON WAREHOUSE my_warehouse FROM ROLE my_role;

Set up the Snowflake metadata source

Configure metadata sources through the Cloudera Octopai Client.

After clicking Next, select the databases to scan using the DB List parameter. The list reflects the Snowflake access you granted.

Enhanced Snowflake connector: key pair authentication

Cloudera Octopai supports key pair authentication, a secure alternative to passwords as Snowflake deprecates legacy methods.

Authentication options

You can configure key pair authentication in two ways:

• Paste the encrypted private key and passphrase directly into the Cloudera Octopai Agent configuration.

  

• Provide the file path to the encrypted private key along with its passphrase.

  

Both methods ensure secure authentication during metadata extraction.

Why migrate from passwords

Snowflake is retiring single-factor password authentication:

• November 2025: password-only sign-ins are blocked for service and human users. See Snowflake announcement and community update.

• March 2026: programmatic access for password-based legacy service accounts is fully disabled. Refer to Snowflake MFA rollout documentation.

Supported authentication methods will be multi-factor (SAML/OAuth) and key pair authentication.

Recommended actions

• Configure key pair authentication in Cloudera Octopai using one of the available methods.

• Ensure the Snowflake service account is marked as a SERVICE user (not LEGACY_SERVICE).

• Password-based service account access in Cloudera Octopai will no longer be supported after March 2026. Plan your migration to avoid service disruptions.

Review the full setup steps in the Snowflake Key Pair Authentication Guide.

Verify the extracted metadata files

Troubleshoot extraction issues

If extraction fails:

• Confirm Snowflake permissions.

• Send logs (including connector number and name) to Cloudera Support. Logs reside at C:\Program Files (x86)\Octopai\Service\log.

  

Access the Cloudera Octopai target folder

1. On the server hosting the Cloudera Octopai Client, open the TGT folder (default: C:\Program Files (x86)\Octopai\Service\TGT).

2. Locate the ZIP file named after the connector and open it.

   

3. Review the inner files for completeness and quality.