You can translate the question and the replies:

best practice for user session variable/attribute

what is the best practice to pre-load/set a variable based on the active connection to Denodo? Is this done best by creating a custom function in Java, if yes can you provide an example containing SQL as below. use-case: query current user with his/her attached roles, use that outcome in a case-when of a view. ... create temporary table user_roles as (select getsession('user') as msn, getsession('roles') as role from dual()); ... select msn, case when role = 'relevantrole' then 'Y' else 'N' end as hash_on from ( SELECT msn, value as role FROM FLATTEN (user_roles AS v, v.role) ) where role = 'relevantrole';
user
31-10-2017 07:52:49 -0400

1 Answer

Hi, If I have to write a custom function to implement this use case, I would use the [Denodo4E extension](https://community.denodo.com/docs/html/browse/6.0/platform/installation/denodo4e/denodo4e) and the sample functions available in <DENODO_HOME>/samples/vdp/customFunctions folder. I can write a function that takes a role as input parameter and retrieves all the roles with a query that uses GETSESSION('roles'). Then, loop through this list of roles until one of the roles matches with the input role. If a match is found, the function would return 'Y', else 'N'. You can refer to '[Developing Custom Functions](https://community.denodo.com/docs/html/browse/6.0/vdp/developer/developing_extensions/developing_custom_functions/developing_custom_functions)' for guidance on developing custom functions. Hope this helps!
Denodo Team
02-11-2017 01:26:58 -0400
You must sign in to add an answer. If you do not have an account, you can register here