Initialization Session Variable from User Roles OBIEE 12C

Question

I have a situation where I want to initialize a session variable based upon the Role a user has assigned.

(1) I have user setup with many Role but specifically the "Eport Analytics" role in OBIEE 12C

(2) The user logs into Answers

(3) Writes a query (below) that searches for a specific User Role ("Eport Analytics") within the list of ROLES the user has assigned.

SELECT

LOCATE('Eport Analytics', VALUEOF(NQ_SESSION.ROLES))

FROM "Security Analytics"

(4) The query returns a value of 46 (indicating the string value has been found within the Roles (if the string value was not found then a 0 would have been returned)

(5) In Variable Manager I define a Session Variable (EPORT_ACCESS) with a default value of 0.

(6) I create an initialization block called "Eport Access" to fill the session variable (EPORT_ACCESS)

(7) I pasted the query (from above) in the Edit Data Source and set the Data Source Type = Database and the Use OBIEE EE Server toggle selected.

Note:  When I select the"Execution Precedence" the Add button does not work (all buttons are disabled).

(8) I log on as the User, select the value of the session variable (EPORT_ACCESS) and I get the default value of 0.  I test the values of the ROLES variable and I see the defined role (which returns a 46).

Any suggestions for how to capture in the session variable if a User has a specific role would be appreciated.  In addition, if you can explain why my EPORT_ACCESS session variable is not returning a 46 would be appreciated.

Thanks...

BenS · Answer

If a user has a specific role I need to capture that in a session variable.   The way that I have this setup is that a 0 will be returned if the role was not listed in the ROLES session variable or a value greater than 0 would be set if the role existed.   The session variable is going to be read by the Oracle database to perform some specific functionality (at the database level) while the user is logged on and running queries.  That specific functionality is to only occur when the user has that role.

rmoff · Answer

Let's look at this another way - what is it you're trying to do here? What's the requirement driving trying to populate a session variable like this?