⚙️ 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.

info

If the SQL returns NULL or an empty string, no role is assigned to that entity. If for_user_role_sql is defined but no roles are assigned to an entity, the entity will not be created, thus no user would will access to it.

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"
        tag: DB_CustomSQLSemanticLink
        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.