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

[kevmcclosk]

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

[SarinaS]

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]

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.

[Turribeach · Accepted solution]

Hi, the way Sarina has suggested is the best you are going to get without having to change the way you authenticate to your Oracle database. Note that while you can pass variables in your SQL recipes or notebooks using this technique you will never be able to guarantee the user is actually coding in this way. Therefore if you use must enforce the user identity on your Oracle database you should be looking at Oracle User Impersonation:

https://doc.dataiku.com/dss/latest/connecting/sql/oracle.html#user-impersonation

[kevmcclosk]

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