Snowflake Data Source Connection
Hi all,
While defining the Snowflake data source connection in Collibra catalog, I notice it expects the “Schema” name in addition to the Account, Database, Warehouse.
I am wondering if this schema name is the name of the schema which contains the tables, columns whose metadata I like to read. In that case, if I have 20 schemas storing objects (tables, columns) related to 20 different projects, I would end up in defining 20 different data source connections. I was expecting the granularity of the connection to be at Database level, and then I being able to choose the schemas that I like to ingest into the catalog.
Or, is this schema a proxy (i.e. dummy) to read metadata from all other schemas I like to ingest into the catalog?
appreciate information on how others have configured the data source connection.
noor
OP5 years ago · EditedOk.
The technical user is executing SHOW commands which require atleast select permission if it is a SHOW TABLES command. The approach of reading metadata through Information schema in Snowflake also seems to be requiring the select permission (ofcourse the driver is not following this approach).
The 3rd approach which seem to be not requiring the select permission on tables is querying the ACCOUNT_USAGE share file.
Again both 2nd & 3rd approaches are not through driver but through custom built solutions
christopherthullen1
·5 years ago · EditedUnfortunately, we have permission issues as well. We are working with the DBA about it.
noor
OP5 years ago · EditedAnother way to look at it is:
Since the technical user password that one has to type in Collibra is not readable after we type, gaining select permission on all tables across all schemas should still be an OK thing. The main concern was what if the password falls into hands of unauthorised users.
Also, if we setup connection through encrypted private key authentication, strangely the passphrase value of the key is appearing as plain text in Collibra ( I mean the passphrase is readable). I have asked Collibra how to make it behave like a password field.
christopherthullen1
·5 years ago · EditedThe schema is indeed the schema that contains the tables and columns whose metadata you want to read. The Collibra connector builds one domain per schema. Therefore, you have to create 20 connections for the 20 schemas.
Yes, I know, we have many schemas too. It would be nice to create something at the database level and let it create everything underneath.
noor
OP5 years ago · EditedThanks Christopher.
Yes, you are right.
Today, I tested the refresh by not passing the schema name, and no results were fetched & saved into the catalog.
Also, the “select on each table to ingest” permission for the technical user that connects to snowflake & extracts the metadata is a worrisome thing for us. We thought the user will fetch metadata through information schema until we gave a deeper look at the above permission.
How is it in your environment? Is there a way to bypass the permission?
I revoked that permission from the technical user & triggered the schema refresh. It fetches nothing from the schema but the ingestion process itself is marked as completed.
noor
OP5 years ago · Editedappreciate if any from the product team can help answer here. thank you.