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:

YAML

JavaScript

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

YAML

JavaScript

cubes:
  - name: suppliers
    sql_table: suppliers
 
    dimensions:
      - name: id
        sql: id
        type: string
        primary_key: true
 
      - name: 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 (opens in a new tab) or run it with the docker-compose up command. You'll see the result, including queried data, in the console.

Enforcing mandatory filters Enforcing role-based access