Skip to main content

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")}:

  • veezoo.User.First_Name
  • veezoo.User.Full_Name
  • veezoo.User.Email
  • veezoo.User.Roles (list of values, which may require string_list for SQL)

In addition, you can access custom user attributes as specified by SAML or JWT.

The following attributes can be accessed directly, e.g. ${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"
        }

        ...
   }
}

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.