for_user_role_sql

Definition

The property for_user_role_sql specifies how user roles are assigned to entities during syncing, by setting for_user_role for the entities. This enables entity-level role-based access control, allowing you to restrict which users can see specific entities based on their assigned roles.

Special Value: ALLOW_ALL

The special value _ALLOW_ALL_ can be used to make an entity accessible to all users without any role restriction. When the SQL returns _ALLOW_ALL_, no for_user_role is set on the entity, and any roles returned by other for_user_role_sql for that entity are also ignored.

This is useful when you want some entities to be public while others are restricted.

Examples

Example 1: Simple Role Assignment

This example shows a basic for_user_role_sql for a Customer class, where access is restricted based on the customer's region:

kb {
    class Customer {
        name.en: "Customer"
        from_table: customer
        // e.g. 00042
        sql: "${customer.id}"
        // e.g. Arthur Dent
        name_sql.en: "${customer.name}"
        // Restrict access based on region
        for_user_role_sql: "${customer.region_role}"
    }
}

The entities will look like this (assuming region_role contains values like "EMEA_Sales"):

kb.Customer {
    entity 00042_Arthur_Dent_1234 {
        name.en: "Arthur Dent"

        for_user_role: "EMEA_Sales"

        id: "00042"
    }
    ...
}

With this configuration, only users with the "EMEA_Sales" role will be able to see and query this entity.

Example 2: Conditional Role Assignment

This example uses a SQL CASE expression to conditionally assign roles based on data values:

kb {
    class Order {
        name.en: "Order"
        from_table: orders
        sql: "${orders.id}"
        name_sql.en: "'Order #' || ${orders.id}"
        // Assign role based on order value
        for_user_role_sql:
            """
            CASE
                WHEN ${orders.total_amount} > 10000 THEN 'HighValueSales'
                ELSE 'StandardSales'
            END
            """
    }
}

Example 3: Multiple Role Columns

You can specify multiple for_user_role_sql properties to assign roles from different columns, allowing users with either role to see the entity:

kb {
    class Customer {
        name.en: "Customer"
        from_table: customer
        sql: "${customer.id}"
        name_sql.en: "${customer.name}"
        // Assign multiple roles from different columns
        for_user_role_sql: [
            "${customer.region_role}",
            "${customer.department_role}"
        ]
    }
}

Usage with entities_custom_sql

When using entities_custom_sql, you can include FOR_USER_ROLE columns to achieve the same effect:

kb {
    class Customer {
        name.en: "Customer"
        sql: "${customer.id}"
        from_table: customer

        entities_custom_sql:
            """
            SELECT
                c.id AS ID,
                c.name AS LABEL_EN_MAIN,
                c.region_role AS FOR_USER_ROLE
            FROM
                customer c
            """
    }
}

Multiple role columns can be specified as FOR_USER_ROLE, FOR_USER_ROLE_1, FOR_USER_ROLE_2, etc. For more details and best practices about defining classes and entities using custom SQL, see Class with Custom Entities defined by a SQL.