User Impersonation with SQLExecutor2 in Web Apps

Options
importthepandas
importthepandas Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS Core Concepts, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 115 Neuron

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

Answers

  • Zach
    Zach Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 153 Dataiker
    Options

    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 dataikuclient = dataiku.api_client()# Impersonate another useruser = 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
    importthepandas Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS Core Concepts, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 115 Neuron
    Options

    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
    Zach Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 153 Dataiker
    Options

    Hi @importthepandas
    ,

    Yes, it works with WebappImpersonationContext as well.

Setup Info
    Tags
      Help me…