Row-level security

For customer-facing analytics, each end-user should only see the data they’re allowed to see and nothing more. That’s why row-level security is a first-class citizen in Embeddable.

Security Tokens and Security Context

Embeddable dashboards are embedded in your site via an HTML web component:

<em-beddable 
  token="eyJhbGciOiJIUzI..."
/>

• The token parameter (a security token) must be retrieved server-side from our Tokens API each time a user accesses your Embeddable dashboard.

• When requesting this security token, you also send a security context. This is simply a JSON object containing any fields you need to pass to your data models:

POST /api/v1/security-token
securityContext: { 
    userId: 45, 
    orgId: "9sZSJ9LHsiYXR0cmlidXRlIjoiZ2VvaXBf",
    countries: ['us-east', 'eu-west']
}
 
Response:
{ "token": "eyJhbGciOiJIUzI1NiIsInR5cCI..." }

The securityContext is just arbitrary JSON, and can be nested as deeply as you like.

How to use the Security Context

The security context is automatically available inside your data models, and is accessed like so:

cubes:
  - name: orders
    title: "Orders"
    sql: >
      SELECT * FROM public.orders
      WHERE org_id = '{ COMPILE_CONTEXT.securityContext.orgId }'
      AND user_id = { COMPILE_CONTEXT.securityContext.userId }

Or, if your customer data is, for example, split by database schema:

cubes:
  - name: orders
    title: "Orders"
    sql_table: "{ COMPILE_CONTEXT.securityContext.orgId }.orders"

If you’re unsure how best to approach data security, reach out to us—we’re here to help.

Using Jinja for Dynamic SQL

Jinja (docs (opens in a new tab)) is used under the hood to compile the models into SQL (given a particular securityContext). The { ... } notation in your model files is replaced before sending the query to your database. For example:

cubes:
  - name: customers
    title: "My customers"
    sql: >
      SELECT * 
      FROM public.customers 
      WHERE orgId = '{ COMPILE_CONTEXT.securityContext.ordId }'

with the following securityContext:

securityContext:
  orgId: abc123

compiles to:

SELECT *
FROM public.customers
WHERE ordId = 'abc123'

Conditional SQL

The nice thing about Jinja is that it also allows conditional logic for more advanced scenarios:

cubes:
  - name: customers
    title: "My customers"
    sql: >
      SELECT * 
      FROM public.customers
      {% if COMPILE_CONTEXT.securityContext.superUser %}
        WHERE 1 = 1
      {% else %}
        WHERE organisationId = '{ COMPILE_CONTEXT.securityContext.organisationId }'
      {% endif %}

For users flagged as superUser, the compiled SQL becomes:

SELECT *
FROM public.customers
WHERE 1 = 1

Multiple Values

When your security context contains an array (e.g. a list of allowed countries), use the list helper:

cubes:
  - name: customers
    title: "My customers"
    sql: >
      SELECT *
      FROM public.customers
      WHERE country IN {{ list(COMPILE_CONTEXT.securityContext.countries) }}

If countries is ["United States", "Canada", "Mexico"], the resulting SQL looks like:

SELECT *
FROM public.customers
WHERE country IN ('United States', 'Canada', 'Mexico')

Testing Security Contexts in Embeddable

To try out different security contexts in the no-code builder, define them in your repo at src/presets/security-contexts.sc.yml (here (opens in a new tab)):

- name: Nike
  securityContext:
    orgId: org5
    userId: 23478
 
- name: Adidas
  securityContext:
    orgId: 23
    userId: cmlidXRlIjoiZ2VvaXBf9sZSJ9LHsiYXR0

Each entry appears under the “View as” dropdown in the builder:

Switch between contexts to preview your dashboard as different users.