Snowflake Database proper grants for Kafka connector role

Question

I'm following the guide: Installing and Configuring the Kafka Connector

Snowflake Sink cannot be created with error: snowflake.database.name database does not exit

A bit of investigation and kafka_connector user cannot see the database from the showsql console:

kafka_connector_user_2#(no warehouse)@(no database).(no schema)>SHOW DATABASES like 'kafka%';
+------------+------+------------+------------+--------+-------+---------+---------+----------------+
| created_on | name | is_default | is_current | origin | owner | comment | options | retention_time |
|------------+------+------------+------------+--------+-------+---------+---------+----------------|
+------------+------+------------+------------+--------+-------+---------+---------+----------------+
0 Row(s) produced.

From sysadmin account:

Database

SHOW DATABASES like 'kafka%';
+-------------------------------+----------+------------+------------+--------+----------+---------+---------+----------------+
| created_on                    | name     | is_default | is_current | origin | owner    | comment | options | retention_time |
|-------------------------------+----------+------------+------------+--------+----------+---------+---------+----------------|
| 2020-06-24 05:02:47.370 -0700 | KAFKA_DB | N          | N          |        | SYSADMIN |         |         | 1              |
+-------------------------------+----------+------------+------------+--------+----------+---------+---------+----------------+

DB grants:

>SHOW GRANTS on database KAFKA_DB;
+-------------------------------+-----------------+------------+----------+------------+------------------------+--------------+------------+
| created_on                    | privilege       | granted_on | name     | granted_to | grantee_name           | grant_option | granted_by |
|-------------------------------+-----------------+------------+----------+------------+------------------------+--------------+------------|
| 2020-06-26 01:11:22.082 -0700 | CREATE SCHEMA   | DATABASE   | KAFKA_DB | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-26 01:11:22.082 -0700 | MODIFY          | DATABASE   | KAFKA_DB | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-26 01:11:22.082 -0700 | MONITOR         | DATABASE   | KAFKA_DB | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-26 01:11:22.082 -0700 | REFERENCE_USAGE | DATABASE   | KAFKA_DB | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-26 01:11:22.082 -0700 | USAGE           | DATABASE   | KAFKA_DB | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-24 05:02:47.167 -0700 | OWNERSHIP       | DATABASE   | KAFKA_DB | ROLE       | SYSADMIN               | true         | SYSADMIN   |
+-------------------------------+-----------------+------------+----------+------------+------------------------+--------------+------------+

Role - User:

>SHOW GRANTS to user KAFKA_CONNECTOR_USER_2;
+-------------------------------+------------------------+------------+------------------------+---------------+
| created_on                    | role                   | granted_to | grantee_name           | granted_by    |
|-------------------------------+------------------------+------------+------------------------+---------------|
| 2020-06-26 01:04:59.699 -0700 | KAFKA_CONNECTOR_ROLE_1 | USER       | KAFKA_CONNECTOR_USER_2 | SECURITYADMIN |
+-------------------------------+------------------------+------------+------------------------+---------------+

Role grants:

SHOW GRANTS to role KAFKA_CONNECTOR_ROLE_1;
+-------------------------------+--------------------------+------------+-----------------------+------------+------------------------+--------------+------------+
| created_on                    | privilege                | granted_on | name                  | granted_to | grantee_name           | grant_option | granted_by |
|-------------------------------+--------------------------+------------+-----------------------+------------+------------------------+--------------+------------|
| 2020-06-26 01:11:22.082 -0700 | CREATE SCHEMA            | DATABASE   | KAFKA_DB              | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-26 01:11:22.082 -0700 | MODIFY                   | DATABASE   | KAFKA_DB              | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-26 01:11:22.082 -0700 | MONITOR                  | DATABASE   | KAFKA_DB              | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-26 01:11:22.082 -0700 | REFERENCE_USAGE          | DATABASE   | KAFKA_DB              | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-26 01:11:22.082 -0700 | USAGE                    | DATABASE   | KAFKA_DB              | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-25 10:49:19.359 -0700 | CREATE PIPE              | SCHEMA     | KAFKA_DB.KAFKA_SCHEMA | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-25 10:49:18.358 -0700 | CREATE STAGE             | SCHEMA     | KAFKA_DB.KAFKA_SCHEMA | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-25 10:49:16.356 -0700 | CREATE TABLE             | SCHEMA     | KAFKA_DB.KAFKA_SCHEMA | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-25 10:49:14.354 -0700 | USAGE                    | SCHEMA     | KAFKA_DB.KAFKA_SCHEMA | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
| 2020-06-26 01:16:36.396 -0700 | OPERATE                  | WAREHOUSE  | COMPUTE_WH            | ROLE       | KAFKA_CONNECTOR_ROLE_1 | false        | SYSADMIN   |
+-------------------------------+--------------------------+------------+-----------------------+------------+------------------------+--------------+------------+

What am I missing ?

What problem do you have? What are you testing and what errors are you getting? — Jan Hudec

Rich Murnane Rich Murnane · Accepted Answer · 2020-06-26T11:28:49

It looks like your "context" isn't set for that session, where context includes "current":

role
warehouse
database and schema

I'd recommend reviewing your connection properties, as well as executing the following and trying again:

ALTER USER kafka_connector_user_2 SET DEFAULT_ROLE = 'KAFKA_CONNECTOR_ROLE_1';
ALTER USER kafka_connector_user_2 SET DEFAULT_WAREHOUSE = 'COMPUTE_WH' ;
ALTER USER kafka_connector_user_2 SET DEFAULT_NAMESPACE = 'KAFKA_DB.KAFKA_SCHEMA' ;

https://docs.snowflake.com/en/sql-reference/sql/alter-user.html

Setting the user's default context is something I recommend for all "programatic" users (a.k.a. "Service Accounts").

I hope this helps...Rich

Snowflake Database proper grants for Kafka connector role

1 Answers