How to Obtain and Utilize Dataiku Username to a SQL Query (Oracle)

Options
kevmcclosk
kevmcclosk Registered Posts: 5 ✭✭✭

I would like to add 2 columns to our most important SQL tables (Oracle) and use them as an "audit log" to track who made the change and when the change was made:

AUDIT_USER_NAME

AUDIT_TIMESTAMP

In my SQL Query Recipe, I am using the following columns:

SELECT
user as audit_user_name
,sysdate as audit_timestamp

Unfortunately, 'user' contains the underlying service account ID (SARPS_PRI_DATAIKU) that is actually connected to Oracle. What I would like to capture is the true Dataiku UserID of the Dataiku user who initiated this change.

Is it possible to obtain and utilize the Dataiku UserID in a SQL recipe?

AUDIT_USER_NAMEVARCHAR2AUDIT_TIMESTAMPDATESV_CODENVARCHAR2RATE_PCTNUMBER

SARPS_PRI_DATAIKU
2023-09-29 09:32:12.0
N03
0.035
SARPS_PRI_DATAIKU
2023-09-29 09:32:12.0
Z144X
0.025
SARPS_PRI_DATAIKU
2023-09-29 09:32:12.0
Z11X
0.0235

Best Answer

Answers

  • Sarina
    Sarina Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer Posts: 315 Dataiker
    edited July 17
    Options

    Hi @kevmcclosk
    ,

    Are you running your SQL recipe directly or from a scenario? I think that instead of the "connection user" (which appears to use a service account user to connect to Oracle with a single login), you probably want to write the user that triggered the job (or scenario) to your database.

    When run as a scenario, you could have a step in your scenario that grabs the scenario run-as user. You can then store this as a project variable, and insert the project variable in your SQL script.

    Here is an example of setting the scenario run as user in a project variable:

    import dataiku
    
    from dataiku.scenario import Scenario
    from dataiku.scenario import BuildFlowItemsStepDefHelper
    
    # The Scenario object is the main handle from which you initiate steps
    scenario = Scenario()
    
    client = dataiku.api_client()
    project = client.get_default_project()
    
    variables = project.get_variables()
    variables['standard']['scenario_runas_user'] = scenario.scenario_trigger['triggerAuthCtx']['realUserLogin']
    
    project.set_variables(variables)


    You can see the variable from your SQL recipe if you validate your SQL code, and look at the left-hand side "Variables" tab.

    I hope that helps, let us know if you have any other questions based on your specific setup!

    Thanks,
    Sarina

  • kevmcclosk
    kevmcclosk Registered Posts: 5 ✭✭✭
    Options

    This is helpful and I appreciate your response! But I am looking for something that can be used outside of scenarios and Python. An example use-case is the following:

    I receive a request to update data via SQL notebook query. I would like an audit trail (recorded in the database) showing that I was the last person to touch that row of data.

  • kevmcclosk
    kevmcclosk Registered Posts: 5 ✭✭✭
    Options

    Thank you both for your input and expertise. I greatly appreciate your willingness to share knowledge and experience!

Setup Info
    Tags
      Help me…