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

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

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 or run it with the docker-compose up command. You'll see the result, including queried data, in the console.

Did you find this page useful?