Username
When using connection pooling or IAM authentication, the IAM user ID cannot be retrieved on the RDBMS using database functions like user_name()
, system_user
or USER
.
Because the user ID is often used in queries (e.g., to fill trace columns or filter data), the user interfaces and service tiers use platform specific methods to provide the username in the context of every database connection.
The tsf_user()
and tsf_original_login()
functions are provided to retrieve the correct username from SQL logic. It is possible to simulate other users. In those cases tsf_user()
will return the username of the simulated user, while tsf_original_login()
will retrieve the
user name of the actual user from the session context.
This method is not supported by Crystal Report/Crystal Clear or the Generate report process action, they return the pool user instead.
In these cases, you can provide the user name to the report as an input parameter, filled by a default that uses the tsf_user()
or tsf_original_login()
function.
Since tsf_original_login()
uses the session context (the session variable tsf_original_login
), it only works in combination with Indicium and not for a 2-tier Windows GUI. When used without Indicium it functions similar to tsf_user()
.
SQL Server
For SQL Server database connections, the CONTEXT_INFO is set by the user interface or the Indicium application tier for the current session or batch using the following command:
declare @binvar varbinary(128);
set @binvar = cast(@username as varbinary(128));
set context_info @binvar;
To get the username in SQL, use the tsf_user()
function:
select dbo.tsf_user();
IBM i DB2
For DB2 database connections, the client user ID is set in the connection string, using the ClientUserID
parameter.
To get the username in SQL, use the following statement:
select tsf_user() from sysibm.sysdummy1;
Oracle
For Oracle database connections, the ClientId property is set for the connection after the connection is opened.
To get the username in SQL, use the following statement:
select tsf_user() from dual;
Azure
For Azure, function tsf_user will return Azure users instead of system users.
In rare cases, it is desirable to deviate from the logged-in user and select a different user. For this, we recommended altering the CONTEXT_INFO as described here: SQL Server.