9 Replies Latest reply on Jul 31, 2019 11:44 AM by Matt Coles

    Locking down permissions to send alerts

    Sean Mullane

      Is there a way to secure VizAlerts to control who can set up alerts? I haven't seen anything yet that would allow us to control access to the emailer at a more granular level than the set of people with the ability to publish. Since publishers can control the "to" and "from" fields it's important that we be able to restrict that ability to a trusted subset of our users. In our case that subset is smaller than the set of publishers.

       

      We'd like to start using VizAlerts but basically need to make sure we have a way to prevent, say, J. Random Publisher from sending out emails from:TheCEO@ourdomain.com.

       

      Thanks

       

      PS: I should add that this question stems from my understanding that the "from" field can be set more of less arbitrarily by a publisher. If that's not correct it may change the importance of this question for us.

        • 1. Re: Locking down permissions to send alerts
          Matt Coles

          Hey Sean. That's the correct understanding--the publisher can set arbitrary values for the from/to/cc/bcc fields.

           

          There are two ways to restrict the "permissions":

           

          1. VizAlerts uses a domain whitelist in the config file which applies to all alerts that anyone publishes. If the email domain for any of the recipients of the the alert does not match any in the whitelist, the entire alert fails and the subscriber and admin are sent an email indicating that it couldn't be sent, and why.

           

          2. You can tell VizAlerts to disregard alerts being attempted by any Tableau Server user you want by editing the SQL query at the bottom of the config file. This query is essentially asking Tableau Server's repository database for a list of alerts to process. It's a little daunting to look at, but the comments at the bottom show where such a modification could be made. You'd uncomment the "allowed users" line by removing the first two dashes, and adding whatever users you wanted to have access to VizAlerts in there:

           

          WHERE 1 = 1
            AND (
            (s.is_test = true)
            OR
            -- ######################################################
            -- ######## Editable Filters #########
            -- ######################################################
            (
            LOWER(sch.name) LIKE '_lerts%' -- only Alerts schedules ("_" single-character wildcard allows you to substitute Cyrillic capital A, U+0410 for sorting purposes)
          
            --AND st.name IN ('Default','TestSite') -- allowed Sites
            --AND p.name IN ('Sales') -- allowed Projects
            --AND su_sub.name IN ('mcoles', 'nkamkolkar') -- allowed Users
            --AND st.name = 'administration'
            --
            -- ######################################################
            -- ######## DO NOT EDIT FILTERS BELOW THIS LINE #########
            -- ######################################################
            AND sch.active = 'f' -- only disabled schedules
            )
            )
            AND coalesce(su_sub.email, '') <> '' -- only accounts with valid emails (no service accts)
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          
          

           

          This allows for a lot of flexibility. You can whitelist, blacklist, allow only certain combinations of specific alerts and users, etc. Just watch the logs after modifying the query so that you ensure it parses and runs correctly. The only downside to this approach is that users attempting to use VizAlerts can still subscribe on the VizAlerts schedules because Tableau Server has no permissions model for those. Their alert simply won't run. So they might be a bit confused about it. One could build an admin alert to email any individual subscribed to a VizAlert who wasn't on a whitelist failry easily, though, which can mitigate that issue.

           

          Let me know if you have any more questions!

           

          Matt

          • 2. Re: Locking down permissions to send alerts
            Sean Mullane

            Thanks Matt, very helpful. This is necessary functionality (both the recipient domain whitelist and the sender whitelist) for us so I'm glad it's there. I can see whitelisting adding a bit of administrative overhead/headache but your admin alert idea could help.

            • 3. Re: Locking down permissions to send alerts
              Matt Coles

              One more thing I thought I should clarify--if you've allowed say, three different users the ability to create an alert by adding them to the SQL query above, there is no mechanism to stop any of those three from sending out an email from TheCEO@ourdomain.com.  I could at some point add a config setting that toggled whether users were allowed to send email from addresses that aren't their own. That wouldn't be hard. But as of right now, that feature doesn't exist.

              • 4. Re: Locking down permissions to send alerts
                Sean Mullane

                I think the filter in the SQL query meets our minimum security needs. We can restrict this ability to people we trust with this level of ability. As we expand usage I could see a need for something like what you're talking about, where we have some sort of mapping between which user is allowed to send from which addresses. I think restricting users to their own address may be too restrictive as it would prevent us from using a noreply@ourcompany.com type of address, which we would probably want to use for this.

                • 5. Re: Locking down permissions to send alerts
                  Brian Brooks

                  Hi Matt Coles,

                  We've tried to incorporate method #2 by modifying the custom SQL in the configuration workbook. Unfortunately it doesn't appear to function as expected, at least how we have implemented it. When we implement it, we grab the su_sub.name as it appears in the workbook when an alert is triggered. For example, mine would be 'brian.brooks'. That's what we're plugging into the custom SQL (see attached). From what I could tell in the rest of the string, it doesn't appear that there are any other setting changes that we needed to make when including the new portions of the custom SQL.

                   

                  What we're finding though is when putting this into our 2018.3 Dev environment, rather than restricting the use of VizAlerts to just user 'brian.brooks', it is still allowing all users to kick off a VizAlert. Have you (or anyone else) run into this issue?

                  Thanks again for a really awesome add on product!

                  • 6. Re: Locking down permissions to send alerts
                    Brian Brooks

                    It looks like we may have figured out our issue, and hopefully this can help others. We struggled for a while thinking the issue was with how our user names were coming through in the su_sub.name field and put in about every permutation we could think of in the custom SQL. What we hadn't considered modifying was this statement:

                    LOWER(sch.name) LIKE'_lerts%'-- only Alerts schedules ("_" single-character wildcard allows you to substitute Cyrillic capital A, U+0410 for sorting purposes)

                     

                    By changing it to a more common wildcard LIKE'%lerts%' it provided the functionality and output we expected. Now the su_sub.name field is pulling in our common name fields and allowing us to apply security.

                    • 7. Re: Locking down permissions to send alerts
                      Matt Coles

                      Glad to hear it, Brian. Actually, since my last reply, it's easier to apply this security measure. You don't have to hack SQL anymore because you can simply edit the VizAlertsConfig workbook. You can edit the calc directly based on any criteria you can imagine:

                       

                      A general whitelist would look like:

                       

                      IF [subscriber_sysname] = 'mcoles' OR [subscriber_sysname] = 'bbrooks'  // allow these users

                      THEN 1

                      ELSE [default_action_enabled_email]

                      END


                      You could also use a cross-database join in the workbook and pull in your own spreadsheet or database so that you could dynamically update the list from a master. The calc is self-maintaining in that case:

                       

                      IF NOT ISNULL([excel_user_name]) // a record exists in the list, so give them access

                      THEN 1

                      ELSE [default_action_enabled_email]

                      END

                       

                      Note that you'd want to perform the same changes for the action_enabled_sms if you're using the SMS feature, as well.

                      • 8. Re: Locking down permissions to send alerts
                        Brian Brooks

                        Very cool, Matt Coles! I like this much better than hacking at the SQL.

                         

                        Are there separate but similar calculated fields to mimic the functionality of the Allowed Projects and Allowed Sites, or would those be contained within this same calculated field as a nested IF clause?

                        • 9. Re: Locking down permissions to send alerts
                          Matt Coles

                          Same calc, yep. The [project_name] and [site_name] fields already exist in the workbook, so you'd use the exact same approach for those.