Vue.js QueryBuilder by Cube.js anonymise columns by local value

Question

I have the following scenario: A user has multiple clients. Those clients generate different reports based on their client id (easy with the filter option).

Some columns need to be anonymised based on a flag which is not stored in the database and a value which is stored in the database, so I need to pass it by the json query and "anonymise" it in the dimension sql.

Sadly there is no way to pass an argument with the provided QueryBuilder (Vue.js) by Cube.js which i am using.

Maybe you know a way to accomplish that.

Answer 1

You can achieve that by using the queryTransformer https://cube.dev/docs/multitenancy-setup#user-context-vs-query-transformer

You'll need to predefine anonymized member versions in the schema, so it looks like:

  dimensions: {
    // the actual dimension that can be `anonymized`
    userPhone: {
      sql: 'user_phone',
      type: 'number',
    },
    // `anonymized` version of the actual dimension
    userPhoneAnonymized: {
      sql: `'***'`,
      type: `string`
    },
    // ...
  }   

Now, we can apply some logic in the queryTransformer and make sure that only users with proper permissions can access secret fields.

const ANONYMIZED_MEMBERS = ['CubeName.userPhone'];
// ...
queryTransformer: (query, { authInfo }) => {
  const user = authInfo.u;

  if (!user.canAccessSecrets) {
    if (query.dimensions && query.dimensions.length) {
      query.dimensions = query.dimensions.map((memberName) => {
        if (ANONYMIZED_MEMBERS.includes(memberName)) {
          return `${memberName}Anonymized`;
        }

        return memberName;
      });
    }
  }
  
  return query;
}

Using this approach will allow other Cube.js features (such as pre-aggregations) to work properly.