User Impersonation with SQLExecutor2 in Web Apps

importthepandas
User Impersonation with SQLExecutor2 in Web Apps

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

0 Kudos
3 Replies
ZachM
Dataiker

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
Author

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 

0 Kudos
ZachM
Dataiker

Hi @importthepandas,

Yes, it works with WebappImpersonationContext as well.

0 Kudos

Labels

?

Setup info

?
A banner prompting to get Dataiku