column-based-access.mdx

redirect_from
/recipes/column-based-access

Enforcing column-based access

Use case

We want to manage user access to different data depending on a database relationship. In the recipe below, we will manage supplier access to their products. A supplier can't see other supplier's products.

Data modeling

To implement column-based access, we will use supplier's email from a JSON Web Token, and the queryRewrite extension point to manage data access.

We have products and suppliers cubes with a many_to_one relationship from products to suppliers:

cubes:
  - name: products
    sql_table: products

    joins:
      - name: suppliers
        relationship: many_to_one
        sql: "{CUBE}.supplier_id = {suppliers.id}"

    dimensions:
      - name: name
        sql: name
        type: string

cube(`products`, {
  sql_table: `products`,

  joins: {
    suppliers: {
      relationship: `many_to_one`,
      sql: `${CUBE}.supplier_id = ${suppliers.id}`,
    },
  },

  dimensions: {
    name: {
      sql: `name`,
      type: `string`,
    },
  },
});

cubes:
  - name: suppliers
    sql_table: suppliers

    dimensions:
      - name: id
        sql: id
        type: string
        primary_key: true

      - name: email
        sql: email
        type: string

cube(`suppliers`, {
  sql_table: `suppliers`,

  dimensions: {
    id: {
      sql: `id`,
      type: `string`,
      primary_key: true,
    },

    email: {
      sql: `email`,
      type: `string`,
    },
  },
});

Configuration

Let's add the supplier email filter if a query includes any dimensions or measures from the products cube:

module.exports = {
  queryRewrite: (query, { securityContext }) => {
    const cubeNames = [
      ...(query.dimensions || []),
      ...(query.measures || []),
    ].map((e) => e.split(".")[0]);

    if (cubeNames.includes("products")) {
      if (!securityContext.email) {
        throw new Error("No email found in Security Context!");
      }

      query.filters.push({
        member: `suppliers.email`,
        operator: "equals",
        values: [securityContext.email],
      });
    }

    return query;
  },
};

Query

To get the supplier's products, we will send two identical requests with different emails inside JWTs.

{
  "iat": 1000000000,
  "exp": 5000000000,
  "email": "purus.accumsan@Proin.org"
}

{
  "iat": 1000000000,
  "exp": 5000000000,
  "email": "gravida.sit.amet@risus.net"
}

Result

We have received different data depending on the supplier's email.

// purus.accumsan@Proin.org
[
  {
    "products.name": "Awesome Soft Salad",
  },
  {
    "products.name": "Rustic Granite Gloves",
  },
];

// gravida.sit.amet@risus.net
[
  {
    "products.name": "Incredible Granite Cheese",
  },
];

Source code

Please feel free to check out the full source code or run it with the docker-compose up command. You'll see the result, including queried data, in the console.