Question

[Retired] Masking Sensitive Fields for Some Users

  • 15 February 2017
  • 25 replies
  • 585 views

Userlevel 7
Badge

The content of this article has been updated and migrated to a Looker Help Center article.



25 replies

Userlevel 2

That’s pretty cool! Now can we use user attributes to toggle, say, the “hidden” property and hide the field completely when not appropriate by user?

Userlevel 5
Badge

@la5rocks not yet- you can call user attributes inside SQL, but there isn’t a notion of using them at the LookML level to toggle attributes. For this you would want to use model permissions as usual!

Sam, you say “not yet” does this imply this is in the pipeline ?

If so, an estimate as to when or which version it might be aimed for ? 🙂


I too would also like to use user attributes to be able to set a field’s hidden attribute, this would be a very powerful and useful feature; it certainly gets my vote.


An extension of this would being able to set other attribute values such as: can_filter: , drill_fields: , sql: or concatenate an attribute values such as description: , label: or group_label: would be awesome; a great and easy way to highly customise a user’s experience.


If my understanding is correct, the only current way to achieve column filtering at a user/group level, would be to maintain multiple copies of a model, and permission each model accordingly. This seems highly inefficient in general, but especially for our code base. Our model has many explores and each one many joins, often to the same view but sometimes joining in differnt ways. Any new joins or changes for a given explore would then need to be duplicated in each model. The models could then easily get out of sync with changes to one and not others !

Userlevel 5
Badge

@Philippa That was a reckless choice of words on my end - it’s not currently in the pipeline but it’s an awesome idea and I’d love to see Looker get there 🙂


That said, elaborating on your use case like this gives the product team more visibility into who wants features and why, so thank you very much for posting!


You are correct that the only way to do this now is to maintain multiple copies of a model. I will say though that you can use include to keep your models DRY and in sync - we have a great article that explains extending models.

Userlevel 2

Here’s my challenge, though. We’re providing mutli-tentant reporting, and if the solution is to create a separate model for each of hundreds of clients, this gets really tough to manage. Yes, we can include to alleviate the pain of models, but how can I product a standard set of shared core looks? If each has its own model, I can’t share those, I have to create an individual set of looks for each client.


Suggestions?

Userlevel 5
Badge

Could LookML dashboards solve some of the pain point here? I imagine you have core Looks that only differ in model. If this is the case, you could make a LookML dashboard for the core Looks and then just include that dashboard in each different model. That way, you have the definition of the Looks in just one place, while still producing a different core set per model.

Userlevel 2

That is reasonable. But, I’m talking about a standard set of Looks, not dashboards. So, you’re saying I’d have to create a set for each client, creating lots and lots of copies to manage with the inevitable changes?


  • 1 and we are in a multi-tenant environment as well and having this option at a user/group attribute level (i.e. either through a inclusion, exclusion list or by creating sets) would certainly provide a more simpler and robust option. As David indicated in his post, this is the biggest reason that we are trying to avoid creating multiple copies of the same model though i understand extensions help here a and our LookML layer models are extended from the base explore file. It is the look that we are worried too as we would ideally want to have one look that supports each client.

Userlevel 5
Badge

If the Look is static and the only thing that needs to be changed is the underlying model, then LookML dashboards would be a decent workaround - you could even make LookML dashboards with one Look. You’re right, David, that LookML dashboards become tricky if you then need to change the Look on a per-customer basis.


To what extent is the Look static in your situations? Is this more of a template situation? You could make a Space with template Looks that you copy over when creating a new user/tenant. Of course, you’d have to change the underlying model for each of those Looks and then re-save, which isn’t ideal.


Ultimately, the desire here is to have Looks that can be changed based on who is viewing it, right? We have filters on user attributes which is a step in this direction. What kinds of things should also vary by user?


What is the syntax? (Will it run in SQL Runner?(

Userlevel 5
Badge

Hey @John_Norman,


You can call user attributes with a liquid HTML variable, which means that you can call them inside a SQL block of LookML. As shown in the example in this post, you can do:


dimension: revenue {
type: number
sql:
CASE WHEN '{{ _user_attributes["can_see_revenue"] }}' = 'y'
THEN ${TABLE}.revenue
ELSE
0
END ;;
}

This lets you call the can_see_revenue user attribute inside the sql of the dimension. Looker will insert the value of the user attribute for that user when it generates SQL for the query.


So to answer your question, no, you can’t call liquid HTML variables inside SQL Runner. I’m curious what kind of use case you have in mind for that!

Userlevel 5
Badge

@Philippa @la5rocks


As of Looker 5.2, you can use Liquid in label fields. Combine this with the ability to use User Attributes in Liquid, and the fact that a field with a blank label doesn’t appear in the field picker, and you have a way to hide fields via user attributes.


label: "{% if _user_attributes['see_hidden_fields'] == 'yes' %}Age{% else %}{% endif %}"

Awesome, thanks Sam for following up on this, this could be very versatile. I will have a play 😄

Userlevel 5
Badge

Update @Philippa @la5rocks -

This label trick doesn’t actually remove the field. It turns out a blank label will default to the view name - so the field will still be there with the name of the view. A similar “solution” would be to leave a blank space in the label, but then the field will appear just with a blank label.



My bad for jumping the gun on this; I got excited that we could use the new 5.2 functionality in a clever way. I’ll keep you posted on the actual progress we make on this.

Instead of User Attributes, can we refer a column in the view and check on the value of that column?


Thanks!

Userlevel 7
Badge

Hi Jie, you can certainly make one dimension conditional on another dimension, or on any column in your database, but that doesn’t seem to be in the same vein as this article, as it would not be user specific. If you wanted to look up an access-control via a user ID, you could do that with a non-correlated sub query in the dimension, assuming your database supports these, but a user attribute would still be required for the user ID. Maybe you could clarify your situation?

Thank you Fabio for trying to answer my question.


We have a few persistent derived tables (PDTs) and would like to answer user’s requests from different PDT based on the value of a filter. We do this for performance reasons.


Russell in Aggregate Awareness using _in_query showed the following example


view: orders {

sql_table_name:

{% if orders.created_date._in_query %}

orders

{% elsif orders.created_week._in_query %}

orders_smry_week

{% elsif orders.created_month._in_query %}

orders_smry_month

{% else %}

orders_smry_year

{% endif %} ;;


The example shows how to detect if the user includes a certain column. What we need is to detect the value of a yesno dimension (to be yes) included as a filter. I am looking for specific examples. Any help is greatly appreciated.


Thank you!

Userlevel 7
Badge

Hi Jie,


Currently, making a conditional liquid statement from a parameter/filter is possible, but a little clumsy.


The cleanest way to do aggregate awareness based on parameters, is to use an “unquoted” parameter type as listed in example #2 here: Great Use Cases for Parameter Fields


If you want to do it the alternate, more clumsy way that gets you the control you probably want, it would be like so:


dimension: existing_yesno {...}
dimension: existing_yesno_selection {
hidden: yes
sql:{% parameter existing_yesno %};; #Make sure there is no extra whitespace
}
sql_table_name: {% if view_name.existing_yesno_selection._sql == "some_value" %} foo {% else %} bar {% endif %};;
}

Thank you very much Fabio!


The key was using a dimension to fetch the value of a parameter. Once we figured that one out, the rest of our code becomes easy. We are very happy we got our “Switchboard” to work the way we want. I am sure you guys are working on getting us an easy way to get the selected value of a parameter.


Thank you very much for your help and thank you for the new examples.

Hey @sam, can Liquid be used in group_label fields? The documentation (https://docs.looker.com/reference/liquid-variables) suggests no, but I just wanted to make sure. If it isn’t supported, is it a planned feature?


I think it would be pretty helpful to group all dimensions that a user won’t have access to into a group. That way if they searched for the field they could find it (based on the label), and learn that it’s data that exists, but it would be obvious that they don’t currently have access.

Userlevel 5
Badge

Hey @mjalkio, this isn’t possible right now, and it’s not on our near-term roadmap, but I’m happy to bring your feedback to the product team!


You can still group together dimensions that you expect users won’t have access to. I’m curious, what sort of label would you put on this that would benefit from having access to Liquid?

@sam currently if I want users to only see the data that they have access to, I need to create different models and grant access to them. Most likely, these models extend from a common base.


If I could group dimensions using Liquid, I could put everything that someone doesn’t have access to under a “Not Permitted” group (or another similar name). The benefits would be:



  1. Users are able to see data that is available that they don’t have access to (maybe they want to request access). This improves on the separate models approach.

  2. Users do not need to query the data to learn that they don’t have access. This improves on the masking sensitive fields approach outlined here.

  3. The list of fields is not crowded with lots of groups that the user doesn’t have access to. This improves on the masking fields approach outlined here (everything would be grouped under a single big group), and also makes it less likely that I need to use separate models to make things clean.


Hope this makes sense!

Userlevel 5
Badge

This is awesome @mjalkio - thanks for taking the time to explain this!

@mjalkio, could you please explain how one can extend a model and restrict some fields from showing up in the new model?


Thanks!

Hey @claricem, I’ve taken the opposite approach. I define “base” explores which define everything that all my models will want, and then add to those base explores through extension. I think you can define everything in one place and restrict using the fields parameter (https://docs.looker.com/reference/explore-params/fields-for-explore), but that seemed like a dangerous approach to me. You might accidentally expose data to a model that should not have access to it.

Reply