You can translate the question and the replies:

Snowflake connection - change warehouse based on e.g. a particular role

Hi, With Snowflake as our backend, we keep track of the compute consumption per departement. Users are assigned a "department role" and each departement role uses a dedicated warehouse. How can I do something similar in Denodo? I want to give users access to the same objects, but depending on what department their in, use a different warehouse. As the warehouse is hardcoded in the database URI, I need to set up different connections... and even if I set up different connections, how can I make sure that the right connection is used for the right user? With kind regards, Philippe

user
02-10-2020 15:43:30 -0400

3 Answers

Hi It is possible in the Denodo Platform to assign a particular role to a group of users. For your scenario, I would create roles and assign the required privileges (such as connect, execute, metadata) over the required base views in the databases of the Virtual DataPort server. Then I would assign these specific roles to the users created in Virtual DataPort. These base views would access specific objects in the underlying data warehouse (data source for Denodo) and base views would use the data source connections to the specific data warehouse. So, when the user with this role is establishing a connection from the Denodo platform to the underlying data source he would only be able to access the data warehouse to which he needs to access. I find this Knowledge Base Article [Denodo User security](https://community.denodo.com/kb/view/document/Denodo%20Security%20Overview?category=Security) very useful in understanding about the User and Role management in Denodo platform. You can also check this [Databases, Users and Access Rights](https://community.denodo.com/docs/html/browse/latest/vdp/administration/databases_users_and_access_rights_in_virtual_dataport/databases_users_and_access_rights_in_virtual_dataport) in Virtual DataPort on how to create and manage users and roles in the Denodo platform. Hope this helps !

Denodo Team
05-10-2020 09:30:37 -0400

Hi, Thank you for your answer. I don't see how this would work: in Snowflake terms, a "warehouse" is a compute cluster with a particular "T-Shirt" size with a performance and cost associated with it; it is not a database in the conventional use of the term. Whenever you connect to Snowflake the connection string contains the warehouse (or compute cluster) that wil be used to query data. E.g.: jdbc:snowflake://xy12345.snowflakecomputing.com/?user=peter&**warehouse=mywh**&db=mydb&schema=public. Here, the user "peter" will connect query the schema public in the database mydb, using the warehouse (read compute cluster) mywh. This said, a base view goes agains a data source and that datasource is set up using a connection string similar to the one above. What I would like to find out: is there a way to make the chunk warehouse=mywh dynamic? E.g. using a parameter @{WAREHOUSE}) that would get its value from a user's property or a user's role property?

user
05-10-2020 12:03:32 -0400

Hi, I would make use of the initial SQL sentence funtionality in Denodo 8.0 to execute a initial sentence before actual query is executed against the snowflake datasource. Virtual DataPort only allows static connection in the JDBC connection URL, so you can leverage the snowflake command [USE WAREHOUSE](https://docs.snowflake.com/en/sql-reference/sql/use-warehouse.html#use-warehouse) as your initial SQL if you want to switch between the warehouses. Have a look at the document [JDBC Sources](https://community.denodo.com/docs/html/browse/latest/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#jdbc-sources) and you can also check the similar community Q&A [DB and SCHEMA - default versus usage](https://community.denodo.com/answers/question/details?questionId=9060g000000XezZAAS&title=DB%20and%20SCHEMA%20-%20default%20versus%20usage). Hope this helps!

Denodo Team
13-10-2020 09:04:54 -0400

ADD ANSWER

You must sign in to add an answer. If you do not have an account, you can register here