Teiid Function to read subject from ExecutionContext

[tanmoypalit]

Hi,

We have some tables which stores data as per user.

If user information is not passed as a parameter to the Teiid query, is it possible to write a custom java function which can read user information from the current ExecutionContext and pass it to the query?

Although this seems little weird to me but please let me know if this is possible and how?

- Tanmoy

[rareddy]

When writing UDF, you have access to CommandContext as very first parameter. On it you can find session object where you find user information. However, you can not rewrite the query to include this new information.

https://docs.jboss.org/author/display/TEIID/Support+for+User-Defined+Functions+%28Non-Pushdown%29

There are two choices you have

* You can define an access pattern on the view so that user is forced to supply the necessary information.

* Use Dynamic SQL Command - Procedure Language - Teiid 8.7 (draft) - Project Documentation Editor

* Extend the translator and detect the situation and add the additional clause. May delegate translator. - Delegating Translator - Teiid 8.7 (draft) - Project Documentation Editor

Ramesh..

[shawkins]

We may need to know more about your scenario.  How would user information be passed as a parameter to the Teiid query?  What kind of source and source access (select, procedure call) will result and how does it expect to use the user information?

Steve

[tanmoypalit]

Hi Ramesh,

Thanks for the reply. It works for me.

public class ContextInfo {

/**   * @param context  

  * * @return the created Timestamp   */ 

public static String GetContextUser(CommandContext context)  

{  

   return context.getUserName().split("@")[0].toUpperCase();  

}

}

Also I have noticed that as the username may be different for each call, the function need to be "deterministic=false". In my case, the root VDB which has this function is never connected directly by consumer apps, rather there other consumer VDB which are connecting to this root VDB using ds.xml configured with CallerIdentityModule. So each consumer VDB call may have a new user but will use the same connection pool. I found that if I keep "deterministic=true" then "GetContextUser" is returning values from previous calls, although direct connection to the root VDB with different users works fine. I think my scenario is "Deterministic" but it does not work when connected via another consumer VDB using data source configuration (CallerIdentityModule). Is there any solution around this? I read that a non-deterministic function may cause performance issues.

Steven,

Scenario is simple. Suppose table A has 500 rows each with a Group_ID, now each Group_IDs are associated with the session User_ID in GROUP_ACCESS table. So I need to read Session User, find it's Groups and then filter rows in the main table based on the Group_IDs belong to the current Session user.

-Tanmoy

[rareddy]

Tanmoy,

Detministic means *if* function is passed same parameters each time, would that return the same result every time. So in that case a "add" function is deterministic. Where as "random" function is not. Your function based on user returns a different value based on who owns the session, this is NOT deterministic.

if you had caching turned on a deterministic function results may be cached, where as non-detministic results are not cached. Thus you may be seeing the behavior. If you are trying to simply filter the rows them I also suggest you also look into the column masking feature  where you can add a condition automatically to the query to filter based on the row value.

Ramesh..

[shawkins]

Have a look at the Row Based Security section specifically if you want to keep the logic associated with data roles.  If all you want is the user name, the you can use the built-in user function with substring and locate to get just the name.

[tanmoypalit]

Thanks Steven. Built-in USER() function works fine.