1 Reply Latest reply on Sep 27, 2018 11:24 AM by Joe Oppelt

    Masking student demographics based on user ID

    Ryan Weitzman

      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.

        • 1. Re: Masking student demographics based on user ID
          Joe Oppelt

          I handle stuff like this by using GROUPS on tableau server.

           

           

          Here, we place every user into one (or more) group(s).  Based on the group they are in, I display or hide things.

           

          (I didn't want to pick through the details of all that code you posted.)  How will Tableau know that user-A should see rows 1-through-20 and nothing more?

           

          Assuming there is a field that identifies this, you can create a data source filter that grabs only those rows appropriate for an individual or a group.


          Tableau has functions that tell you who the user is.

           

          The ISMEMBEROF() function tells you if the user is a member of the specified group.  (Maybe a whole department should see rows 1-through-20, so shove them all into one group.)

           

          The ISUSERNAME() function tells you if the user is the specified name.

           

          So you could create a Boolean data source filter that would look something like this:

           

          ISMEMBEROF("Group-A") and [Access Field] = "Dept-A"

          or ISMEMBEROF("Group-B") and [Access Field] = "Dept-B"

          .

          .

          .

           

          Each user group would only have the appropriate rows available to him.


          And when users have web-edit access, they can't get to the data source filters to monkey with them.