User Impersonation with SQLExecutor2 in Web Apps

importthepandas

Hello!

We've a couple use cases where we'd like to be able to execute SQL on behalf of another user on Dataiku who has embedded credentials (in this case snowflake oauth). I know we can get to general python API functionality with impersonation, but is it possible to also use SQLExecutor2 to run queries on connections as the impersonated user?

---

Operating system used: Ubuntu 18.04

Zach

Hi @importthepandas
,

You can run SQL statements while impersonating another user by using the DSSClient.sql_query() method.

Here's an example that runs a query to print all the rows in a table using another user's credentials:

import dataiku

client = dataiku.api_client()

# Impersonate another user
user = client.get_user("oauth-user")
client_as_user = user.get_client_as()

query = client_as_user.sql_query("SELECT * FROM MY_TABLE", connection="MY_SNOWFLAKE_CONNECTION")

for row in query.iter_rows():
    print(row)

I tested it with Snowflake OAuth, and can confirm that it works.

Thanks,

Zach

importthepandas

thanks @ZachM
this is beautiful.

I'm assuming this should work with WebappImpersonationContext as well given we can get a client handle with the impersonated user's ticket?

referencing from docs here: https://doc.dataiku.com/dss/latest/webapps/security.html

Zach

Hi @importthepandas
,

Yes, it works with WebappImpersonationContext as well.