I work for a University and I am using Tableau 10.4 along with Oracle SQL Sever.

Background:

I am using the web edit functionality of Tableau Server to be able to empower users to create/modify their own reports for Graduation Rates, seeing Student IDs. Currently, all my users have access to Student IDs and I am trying to change so they only see Student IDs based on the Department that they are within.

Here is the Initial SQL statement:

DECLARE

    PREV_SID VARCHAR(20);

    CURR_SID VARCHAR(20);

    CURR_USERNAME VARCHAR(15);

    USER_COUNT NUMBER :=0;

BEGIN 

    SELECT SYS_CONTEXT ('USERENV', 'SID') INTO CURR_SID FROM DUAL;

    SELECT [TableauServerUser] INTO CURR_USERNAME FROM DUAL;

    SELECT COUNT(USERNAME) INTO USER_COUNT FROM TABLEAUSESSIONS WHERE USERNAME = CURR_USERNAME;   

    IF USER_COUNT > 1 THEN

        DELETE FROM TableauSessions

        WHERE USERNAME = CURR_USERNAME

        AND ROWNUM != 1;

    END IF;

    SELECT CURRENT_SPID INTO PREV_SID FROM TableauSessions WHERE USERNAME =  CURR_USERNAME AND ROWNUM = 1;

    IF PREV_SID != CURR_SID AND PREV_SID IS NOT NULL THEN

        UPDATE TableauSessions

        SET CURRENT_SPID = CURR_SID

        WHERE CURRENT_SPID = PREV_SID

        AND USERNAME =  CURR_USERNAME;

    ELSE

        INSERT INTO TableauSessions VALUES (CURR_USERNAME, CURR_SID);

    END IF;

    EXCEPTION

            WHEN NO_DATA_FOUND THEN

               INSERT INTO TableauSessions VALUES (CURR_USERNAME, CURR_SID);

            WHEN TOO_MANY_ROWS THEN

               DELETE FROM TableauSessions

               WHERE USERNAME = CURR_USERNAME;

               INSERT INTO TableauSessions VALUES (CURR_USERNAME, CURR_SID);

   END;

The goal is to be able to collect the USER credentials along with SESSION ID and pass that to a table within Oracle SQL that has Table Session and USER ID. This helps determine who is logged in.

Then in Custom Query, the following SQL is written in Tableau:

SELECT

  "SSD_FTF_R"."ERSS_COHORT_YEAR" AS "ERSS_COHORT_YEAR",

  "SSD_FTF_R"."EMPLID" AS "EMPLID",

  "SSD_FTF_R"."COHORT_YEAR" AS "COHORT_YEAR",

  "SSD_FTF_R"."COHORT_YEAR_NAME" AS "COHORT_YEAR_NAME",

  "SSD_FTF_R"."GRADUATION_YEAR_NAME" AS "GRADUATION_YEAR_NAME",

FROM "SSD_FTF_R" "SSD_FTF_R"

WHERE "SSD_FTF_R"."ENTRY_DEPARTMENT" IN (

SELECT "S"."CLASS_DEPT" FROM "SECURITY" "S"

JOIN "TABLEAUSESSIONS" "T"

ON "S"."USERNAME" = "T"."USERNAME"

WHERE "T"."CURRENT_SPID" IN (SELECT SYS_CONTEXT ('USERENV','SID') FROM "DUAL"))

UNION ALL

SELECT

  "SSD_FTF_R"."ERSS_COHORT_YEAR" AS "ERSS_COHORT_YEAR",

  RANDOM_STR(9) AS "EMPLID",

  "SSD_FTF_R"."COHORT_YEAR" AS "COHORT_YEAR",

  "SSD_FTF_R"."COHORT_YEAR_NAME" AS "COHORT_YEAR_NAME",

  "SSD_FTF_R"."GRADUATION_YEAR_NAME" AS "GRADUATION_YEAR_NAME",

FROM "SSD_FTF_R" "SSD_FTF_R"

WHERE "SSD_FTF_R"."ENTRY_DEPARTMENT" NOT IN (

SELECT "S"."CLASS_DEPT" FROM "SECURITY" "S"

JOIN "TABLEAUSESSIONS" "T"

ON "S"."USERNAME" = "T"."USERNAME"

WHERE "T"."CURRENT_SPID" IN (SELECT SYS_CONTEXT ('USERENV','SID') FROM "DUAL"))

In the Custom Query, the USER ID is used to determine the data access based on a Security Table that has USER ID, Department, etc.  and returns the data with either the Student ID (called EMPLID in the Custom Query) or provides a random string (RANDOM_STR(9) AS "EMPLID" in the Custom Query) based on if the Department is associated with that USERS ID.

As Administrator for Tableau and Oracle servers, it is working fine for me. However, upon asking others to test for me. The PROBLEM is that when a user logs in it seems that the Custom Query is not being updated providing the data that the USER should have access to. The user experiences either a continuous loading or asks for further credentials which should not be required.