Role-Based Access Control (RBAC)

Overview

This topic describes how to apply Role-Based Access Control (RBAC) or Row-Level Security (RLS) by creating parameterized database views that filter data dynamically based on user preferences. Instead of creating separate databases for each user or role, you create a single GENERIC view that filters data at query time using session variables.

High-level flow

Architecture

Supported database types

How it works - end-to-end flow

Phase 1: Create generic views

The views_data parameter defines which tables should have RLS filters and on which columns. The wildcard value * indicates dynamic filtering.

Define which tables need filtering and on which columns:

VIEWS_DATA='{
  "Sales": {
    "Country": ["*"],
    "Region": ["*"]
  },
  "Calendar": {}
}'

vianctl metadata create-db --db-name "my_analytics" --parent-conn-name "source_database" --views-data "$VIEWS_DATA"

CLI parameters:

• --db-name: Name for your new database (the system appends _GENERIC)
• --parent-conn-name: The existing database connection containing source data
• --views-data: JSON string defining the filtering configuration

This creates my_analytics_GENERIC with:

• Sales table filtered by Country and Region (based on user preferences)
• Calendar table accessible to everyone (no filtering)

Phase 2: Configure user preferences

User preferences define what data each user can access. These are stored in the db_session_context module of the UserService. Admin users can assign these preferences for other users.

Set user preferences with vianctl

Single Preference:

vianctl userpreferences set \
  --system "vianai" \
  --module "db_session_context" \
  --name "Country" \
  --description "Optional description" \
  --value "Thailand" \
  --user_id "thailand_user_a"

Multiple preferences:

vianctl userpreferences set \
  --system "vianai" \
  --module "db_session_context" \
  --name "Country" \
  --description "Optional description" \
  --value "Thailand,Singapore,Malaysia" \
  --user_id "asean_user_a"

Connection-specific preferences If a user needs different access levels for different databases, use the {connection_name}:{preference_name} format:

vianctl userpreferences set \
  --system "vianai" \
  --module "db_session_context" \
  --name "Country" \
  --description "Optional description" \
  --value "asean_connection_GENERIC:Thailand,Singapore,Malaysia" \
  --user_id "aseran_user"

Preference resolution order:

1. Connection-specific preference (e.g., mydb_GENERIC:Country)
2. Global preference (e.g., Country)

Remove user ability to change their views preferences

By default, users can change these preferences. To keep users from changing the views preferences:

1. Create a new role and set the userpreference permission to read-only.

   vianctl roles create --role_name <role_name> --permissions [ {"userpreference":['read']} ]
   

2. Assign users to the role.

   • Update the role for existing users:

     vianctl roles updateuser --username <username> --groups <role_name>
     

   • Create new users with the role assigned:

     vianctl users create --username <username> --groups <role_name> 
     

Configuration guide

Views data format

The views_data JSON defines your filtering configuration:

{
  "TableName": {
    "ColumnName": ["*"]
  }
}

Example configuration

{
  "PnL Report": {
    "Country": ["*"],
    "Business Unit": ["*"],
    "Region": ["*"]
  },
  "GL Journal": {
    "Company Code": ["*"]
  },
  "Calendar": {},
  "Currency Rates": {},
  "Chart of Accounts": {}
}

This creates:

• PnL Report filtered by Country, Business Unit, and Region
• GL Journal filtered by Company Code
• Calendar, Currency Rates, and Chart of Accounts with no filtering

Troubleshooting

User sees all data

Check connection ends with _GENERIC:

vianctl metadata list --names "your_metadata" | jq '.data.connection_name'

SQL query returns an error

If you see the following error message in the SQL error log: Unknown setting 'SQL_<COLUMN>' in scope: <GENERATED_SQL>, make sure the user has all the column user preferences assigned.

Validation failure during SQL generation

Currently the GenerateSQL job does not take session variables into account when validating the generated golden SQL. This causes the error mentioned above to occur. As a workaround you can manually turn off the validation logic by updating the following config value: ext_conn_validation_map.