11 Replies Latest reply on Sep 21, 2018 12:02 PM by Lawrence Block

    column security  (as in masking PHI info)  based on username() functionality

    Lawrence Block

      Is there any documentation on if it is possible to block out specific columns with an asterisk  vs row level security which allows access by row per se as that may affect the overall calculations if users only have access to certain  rows -

       

      So that if there is data for group A User can see it but can't see B.

       

      Sorry I don't have a workbook yet as I need to do a lot of work to hide all the PHI information before even uploading-

      right now I am mostly looking for broad information as to feasibility at all and where to look. This is my first foray into  security.

       

       

      Group      ID

      A               123

      A               234

      B               ***

      B              ***

       

      TIA

       

      Lawrence

        • 1. Re: column security  (as in masking PHI info)  based on username() functionality
          Joe Oppelt

          I would approach it like this:

           

          First of all, you would need to make a string calc so that you can mix numeric output with asterisks.


          Then you'd have unlimited creative license to insert asterisks when USERNAME() or ISMEMBEROF() dictates that the user should not see certain things.

           

          (For the record, I would use ISMEMBEROF rather than USERNAME.  In my opinion it's easier to manage new users at the group level than to have to keep changing a calc to accommodate new users.  Just my personal preference on that.)

          • 2. Re: column security  (as in masking PHI info)  based on username() functionality
            Lawrence Block

            Joe;

            Thanks-So in the end then I can simply limit what fields are then downloaded per se. That is my biggest concern-

             

            Lawrence

            • 3. Re: column security  (as in masking PHI info)  based on username() functionality
              Joe Oppelt

              That's a different question.   If you limit what gets drawn from the data source, those rows won't be there at all, and loading *** won't happen because nothing will be there to make a mark where *** would get displayed.

               

              What I suggested earlier would read in everything, and then override the value that would get displayed with ***.

               

              So what are you really looking to do here?

              • 4. Re: column security  (as in masking PHI info)  based on username() functionality
                Lawrence Block

                Joe;

                Sorry for the delay-I was off for a couple of days but back on it-

                So to give context-I have a report with 21  medical centers and with lots of clinical leaderships in different groups that will be allowed access to the data ONLY at their respective medical center in addition to the medical provider being able to see their own data.

                 

                 

                So for example -chief of surgery at med center A can see all the providers and medical record numbers at med center A but would only see  xxxxxx for MRN and provider data at med center B.  All other columns-admit date, order date, etc etc can be left as is

                 

                Provider A at his medical center can see his/her data but can't see other provider names or medical record numbers as they would be asterisked out-

                 

                 

                 

                I have now limited permissions so that users can only download summary data-  This will stop users from seeing all the data in other tabs  and gets around the underlying data question-and I can control what they see on the provider/patient page.

                 

                 

                Currently I have all 21 security groups already created-just have to understand a bit more about how to incorporate the ISMEMBEROF()  function-  any examples you can send would be great.

                 

                This would be great if I can bypass the conventional path of row level security as it would  reproduce millions of rows and would simply create a mess of trying to QA the numbers again in addition to slowing the efficiency of queries.

                 

                Thanks

                 

                Lawrence 

                 

                 

                 

                • 5. Re: column security  (as in masking PHI info)  based on username() functionality
                  Lawrence Block

                  Joe;

                   

                  I found this link-

                  Will check it out in the morning.  But this may be the golden goose that will work.

                  User Functions

                   

                  However it brings up another interesting dilemma-if I need to create groups on the tableau server is there a way to connect them to a sql server or other source as they will dynamically change.

                   

                  Lawrence

                  • 6. Re: column security  (as in masking PHI info)  based on username() functionality
                    Joe Oppelt

                    Right.  The user functions are what I was referring to earlier.

                     

                    I use that extensively at my work here.  We have the concept of a "power user" for dashboards and sheets.  Some people get to see more stuff than others.  for instance, in my broadcast workbook I have special considerations for broadcast power users.  I have this calc:

                     

                    //  Calc used to do stuff for BroadcastPowerViewers

                    if
                    (ISMEMBEROF('Designers' ) or ISMEMBEROF('BroadcastPowerViewers' )) and
                    [Test Param] = 'a'
                    then 1 else 0 end

                     

                     

                    I am in the Designers group.  I added me to this calc, as well as a bogus little test parameter that I can turn on and off so I can see how things behave for power users and for general users.

                     

                    As people come and go in the business, I maintain memberships in the appropriate groups on the server.  I don't need to mess with any SQL, nor will I ever need to edit this calc (unless some different super-power-user level gets added to the hierarchy.)

                     

                    I don't use it for row-level security, but I helped someone out in another thread doing precisely that.  You can put a data source filter on the data source in your workbook that controls how data gets filtered into the workbook.  Check out this thread:  Row level access (Hide filters/parameter even when editing worksheet online)

                    • 7. Re: column security  (as in masking PHI info)  based on username() functionality
                      Lawrence Block

                      Joe;

                      Ok-sorry for the NOOB questions-so you are part of the group Designers that is uploaded the server -with that calculation in place

                      essentially anyone in the  Designers group can see  the data if  [Test Param] = 'a'  but no one else can-

                       

                      So I could load the groups in tableau server such MedctrA...Medctrz

                      and then  put a calc so that

                      if

                      ISMEMBEROF('MedctrA' )  and Medctr = 'A'

                      then 1 else 0

                       

                      else if  (ISMEMBEROF('MedctrB' )  and Medctr = 'B'

                      then 1 else 0 end

                      ....

                       

                      else if  (ISMEMBEROF('MedctrZ' )  and Medctr = 'Z' 

                      then 1 else 0 end

                       

                      I think I am understanding it-

                       

                      Thanks again.

                       

                      Lawrence

                      • 8. Re: column security  (as in masking PHI info)  based on username() functionality
                        Lawrence Block

                        Joe;

                        One last question does the group have to be on the tableau server per se or can it be made within tableau  as I will be importing the security groups as part of security within a sharepoint site-

                         

                        Lawrence

                        • 9. Re: column security  (as in masking PHI info)  based on username() functionality
                          Joe Oppelt

                          Lawrence Block wrote:

                           

                          Joe;

                          Ok-sorry for the NOOB questions-so you are part of the group Designers that is uploaded the server -with that calculation in place

                          essentially anyone in the Designers group can see the data if [Test Param] = 'a' but no one else can-

                           

                           

                          All of us who make the tableau dashboards for the users in the company are in the Designers group.  People who do broadcast stuff are in the Broadcast group, and some of them are also in the BroadcastPowerViewer group.  I am in neither group.

                           

                           

                          I only mess with the [Test Param] stuff when I'm testing something new that will impact the power users.  It allows me to create a condition to simulate what it will look like both for the general user and for the power user by turning the parameter on and off.  When I publish the workbook, I publish it with the parameter turned on so that the condition will work for the power user.  (If I accidentally publish with it the param turned off, it will disable the functionality for the power user.)  Don't get hung up on the [test Param] stuff too much.  That's just my way of testing out new functionality.

                           

                          Lawrence Block wrote:

                           

                          ...

                           

                          So I could load the groups in tableau server such MedctrA...Medctrz

                          and then put a calc so that

                          if

                          ISMEMBEROF('MedctrA' ) and Medctr = 'A'

                          then 1 else 0

                           

                          else if (ISMEMBEROF('MedctrB' ) and Medctr = 'B'

                          then 1 else 0 end

                          ....

                           

                          else if (ISMEMBEROF('MedctrZ' ) and Medctr = 'Z'

                          then 1 else 0 end

                           

                           

                          That calc would work as a filter on your sheet.  If you wanted to make a data source filter calc where you filter what gets read into the workbook, you would want to make it Boolean logic.  Something like this:

                           

                          ISMEMBEROF('MedctrA' ) and Medctr = 'A'

                          or

                          ISMEMBEROF('MedctrB' ) and Medctr = 'B'

                          or

                          ....

                           

                          ISMEMBEROF('MedctrZ' ) and Medctr = 'Z'

                           

                          If you put that in the "Conditions" panel of the data source filter, then whichever group the user is a member of will get only those rows he's allowed to see.

                          • 10. Re: column security  (as in masking PHI info)  based on username() functionality
                            Joe Oppelt

                            Lawrence Block wrote:

                             

                            Joe;

                            One last question does the group have to be on the tableau server per se or can it be made within tableau as I will be importing the security groups as part of security within a sharepoint site-

                             

                            Lawrence

                             

                            To the best of my knowledge, the user functions only deal with Tableau Server groups.  And I'm not much of a Server expert, so I don't know if you can automate setting them up from a sharepoint feed.  All ours are done manually, and when a new user gets assigned a user license, s/he is also put into the appropriate group(s) at that time.

                            • 11. Re: column security  (as in masking PHI info)  based on username() functionality
                              Lawrence Block

                              Joe;

                              Thanks again for your feedback!

                               

                              Lawrence