How to retrieve the username within the view for incoming request?

How to retrieve the username within the view for incoming request?

book

Article ID: KB0077092

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization All supported versions

Description

Requests to TDV can come from either JDBC/ODBC or Web Service clients, and that request may make an outbound call to a REST data source/other resources. This KB article explains the process of restricting the result set returning from the external REST service based on the client username/ID.

Resolution

To achieve this use case, table or views must be wrapped in SQL script and then call the script by "/lib/util/CurrentUserName" to get current user name in the request.

Here is an example SQL snippet:
 
PROCEDURE EmployeesDemo(OUT result CURSOR (PROCEDURE EmployeesDemo(OUT result CURSOR (employeeid INTEGER, firstname VARCHAR(50), title VARCHAR(255)))
BEGIN     
    DECLARE username VARCHAR(50);   
    CALL /lib/util/CurrentUserName(username);     
    OPEN result FOR  SELECT employees.employeeid, employees.firstname,employees.title       
         FROM    /shared/examples/ds_orders/tutorial/employees employees       
         WHERE employees.firstname = username ;     
END

Issue/Introduction

How to retrieve username within the view for incoming request?