Row-Level Permission

In Veezoo, we provide a method to achieve row-level authorization using SQL templating with the virtual_table feature. This allows for custom data access restrictions down to the row level based on user-specific values.

SQL Templating

SQL templating allows parts of your SQL properties to be adjusted dynamically based on user-specific values. For example, you can use it to compare a user's email with a column in your table.

Support

Currently, SQL templating is supported in the following locations:

• Identifiers of database/schema/table/column
• Including database identifier in DB connection
• Virtual table SQL

Templating Language

Template sections are marked using ${...} and will be transformed to SQL on execution. The templating expression inside ${...} consists of a small DSL supporting:

• Accessing user attributes using syntax user_attribute("attribute_name")
• Direct variables, e.g. veezoo.Tenant.Id.
• Functions with syntax function(...)
  • The quote and raw functions are used to convert strings to SQL code
  • string_list is used for lists of strings, mapping them to an expression usable with IN in SQL

Pre-Defined Attributes and Variables

The following user attributes can be accessed through the user_attribute function, e.g. ${user_attribute("veezoo.User.Email")}:

Attribute	Type	Description
veezoo.User.First_Name	string	The user's first name
veezoo.User.Full_Name	string	The user's full name
veezoo.User.Email	string	The user's email address
veezoo.User.Roles	list	The user's assigned roles (use with string_list for SQL IN clauses)
veezoo.User.Is_Technical_User	boolean	true for technical users (sync operations, internal tools), false for regular users

In addition, you can access custom user attributes as specified by SAML or JWT. See the User Attributes Reference section for a complete list with descriptions.

The following attributes can be accessed directly, e.g. ${veezoo.Tenant.Id}:

• veezoo.Tenant.Id

Row-Level Authorization

To implement row-level authorization, you can transform a table into a virtual_table and include user-specific SQL templates.

import: [
    db.postgres.orders_db.public.ORDERS
]

db.orders_db.public {
    virtual_table orders {
        identifier: "orders"

        virtual_table_sql:
         """
            SELECT
                o.*
            FROM
                "orders_db"."public"."orders" o
            WHERE
                o."customer_email" = ${quote(user_attribute("veezoo.User.Email"))}
         """

        ...
    }
}

In the example above, the virtual_table_sql will only return the rows of the orders table where the customer_email matches the logged-in user's email.

As another example in the case of Multi-Tenant Self-Service Analytics, we can restrict the data based on the tenant ID.

db.storage.hiring_app {

    virtual_table candidates {

        identifier: "candidates"

        # Only show candidates from the current tenant
        virtual_table_sql:
         """
            SELECT
              *
            FROM
              candidates c
            WHERE
              c.company_id = ${quote(veezoo.Tenant.Id)}
            """


        column ID {
            identifier: "ID"
            is_primary_key: true
            primitive_type: "varchar"
        }

        ...
   }
}

Bypassing Row-Level Security for Technical Users

When using row-level authorization, you will want to allow the technical user (used for syncing entities) to bypass the security filter. Otherwise, some entities may be filtered out by the row-level restrictions when you sync them from the database.

You can achieve this by using the veezoo.User.Is_Technical_User attribute, which returns true for technical users and false for regular users:

db.orders_db.public {
    virtual_table orders {
        identifier: "orders"

        virtual_table_sql:
         """
            SELECT
                o.*
            FROM
                "orders_db"."public"."orders" o
            WHERE
                ${user_attribute("veezoo.User.Is_Technical_User")}
                OR o."customer_email" = ${quote(user_attribute("veezoo.User.Email"))}
         """

        ...
    }
}

In this example, technical users will see all rows (since Is_Technical_User evaluates to true), while regular users will only see rows where their email matches the customer_email column.

By using row-level authorization, you can enhance your data security and maintain a high degree of data access control within Veezoo. Remember to always review your SQL templates to ensure that they are accurate and efficient.