Masking student demographics based on user ID
Ryan Weitzman Sep 27, 2018 10:54 AMI 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.