Row-Level Security

For Data Analysts

Row-Level Security (RLS) enables fine-grained access control by automatically filtering data based on user attributes. Users only see the rows they are authorized to access.

Overview

RLS policies in Olytix Core:

• Filter rows automatically based on user context
• Apply transparently to all queries
• Support dynamic conditions using user attributes
• Combine with column masking for comprehensive data protection

How RLS Works

┌─────────────────────────────────────────────────────────────────┐
│                        User Query                                │
│  SELECT region, SUM(revenue) FROM orders GROUP BY region        │
└───────────────────────────┬─────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────────────┐
│                    RLS Policy Engine                             │
│  User: analyst@company.com                                       │
│  Roles: [analyst, finance_team]                                  │
│  Attributes: {region: "north_america"}                           │
│                                                                  │
│  Policy: regional_access                                         │
│  Filter: region = $user.region                                   │
└───────────────────────────┬─────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────────────┐
│                     Modified Query                               │
│  SELECT region, SUM(revenue) FROM orders                        │
│  WHERE (region = 'north_america')                                │
│  GROUP BY region                                                 │
└─────────────────────────────────────────────────────────────────┘

Defining RLS Policies

YAML Configuration

Create policies in your Olytix Core project:

# security/policies/regional_access.yml
name: regional_access
description: Users can only see data from their assigned region
cube: orders
enabled: true
priority: 10

# SQL template filter
filter_sql: "region = $user.region"

# Apply to these roles
roles:
  - analyst
  - regional_manager

# Audit all access
log_access: true

Structured Conditions

For more complex policies, use structured conditions:

# security/policies/department_access.yml
name: department_access
description: Users can only see data from their department
cube: employees
enabled: true
priority: 10

conditions:
  - field: department
    operator: equals
    value: $user.department

  - field: is_active
    operator: equals
    value: true

combine_with: AND  # AND or OR

roles:
  - hr_analyst
  - manager

Multi-Condition Policies

# security/policies/sales_territory.yml
name: sales_territory
description: Sales reps see only their territory data
cube: opportunities
enabled: true
priority: 20

conditions:
  - field: territory_id
    operator: in
    value: $user.territories

  - field: status
    operator: not_equals
    value: "archived"

combine_with: AND

roles:
  - sales_rep
  - sales_manager

groups:
  - sales_team

Policy Operators

Operator	Description	Example
equals	Exact match	region = 'NA'
not_equals	Not equal	status != 'deleted'
in	In list	territory IN ('T1', 'T2')
not_in	Not in list	type NOT IN ('internal')
contains	Contains substring	name LIKE '%keyword%'
starts_with	Starts with	code LIKE 'PRD%'
ends_with	Ends with	email LIKE '%@company.com'
gt	Greater than	amount > 1000
gte	Greater than or equal	level >= 3
lt	Less than	age < 65
lte	Less than or equal	priority <= 2
between	Between two values	date BETWEEN '2024-01-01' AND '2024-12-31'
is_null	Is null	manager IS NULL
is_not_null	Is not null	email IS NOT NULL
regex	Regex match	code ~ '^[A-Z]{2}[0-9]+$'

User Attribute References

Use these placeholders in policy filters:

Placeholder	Description	Example Value
$user_id	User's unique ID	"user123"
$user_email	User's email	"analyst@company.com"
$user_roles	User's roles list	["analyst", "finance"]
$user_groups	User's groups list	["north_america"]
$user.{attr}	Custom attribute	$user.department
$current_date	Current date	CURRENT_DATE
$current_timestamp	Current timestamp	CURRENT_TIMESTAMP

Example: Dynamic Filters

# Filter by user's assigned regions (list)
name: multi_region_access
filter_sql: "region IN ($user.regions)"
roles:
  - regional_admin

# Filter by user's manager hierarchy
name: manager_hierarchy
filter_sql: "manager_id = $user_id OR employee_id = $user_id"
roles:
  - manager

# Time-based access
name: recent_data_only
filter_sql: "created_at >= $current_date - INTERVAL '30 days'"
roles:
  - temporary_analyst

Policy Priority and Combination

Priority

Higher priority policies are evaluated first:

# Priority 100 - evaluated first
name: admin_override
priority: 100
filter_sql: "1=1"  # No filter for admins
roles:
  - admin

# Priority 50 - evaluated second
name: manager_access
priority: 50
filter_sql: "department IN ($user.departments)"
roles:
  - manager

# Priority 10 - evaluated last
name: user_access
priority: 10
filter_sql: "owner_id = $user_id"
roles:
  - user

Multiple Policies

When multiple policies apply, they are combined with AND:

# Policy 1: Regional filter
name: regional_filter
filter_sql: "region = $user.region"

# Policy 2: Department filter
name: department_filter
filter_sql: "department = $user.department"

# Combined result:
# WHERE (region = 'NA') AND (department = 'Finance')

Cube-Level Configuration

Apply RLS at the cube level:

# cubes/orders.yml
name: orders
sql_table: public.orders

measures:
  - name: total_revenue
    type: sum
    sql: revenue

dimensions:
  - name: region
    type: string
    sql: region
  - name: order_date
    type: time
    sql: order_date

# RLS configuration
security:
  rls:
    enabled: true
    policies:
      - regional_access
      - department_filter

    # Exempt roles from all RLS
    exempt_roles:
      - admin
      - superuser

API Configuration

Via API

# Create a policy
curl -X POST http://localhost:8000/api/v1/security/policies \
  -H "Authorization: Bearer $ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "regional_access",
    "description": "Filter by user region",
    "cube": "orders",
    "filter_sql": "region = $user.region",
    "roles": ["analyst"],
    "enabled": true,
    "priority": 10
  }'

# List policies
curl http://localhost:8000/api/v1/security/policies \
  -H "Authorization: Bearer $ADMIN_TOKEN"

# Update a policy
curl -X PUT http://localhost:8000/api/v1/security/policies/regional_access \
  -H "Authorization: Bearer $ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "enabled": false
  }'

# Delete a policy
curl -X DELETE http://localhost:8000/api/v1/security/policies/regional_access \
  -H "Authorization: Bearer $ADMIN_TOKEN"

Policy Evaluation Result

When querying, the RLS engine returns evaluation metadata:

{
  "data": [...],
  "security": {
    "rls": {
      "policies_applied": ["regional_access", "department_filter"],
      "predicates": [
        "(region = 'north_america')",
        "(department = 'Finance')"
      ],
      "evaluation_time_ms": 1.2
    }
  }
}

Testing Policies

Dry Run Mode

Test policies without executing queries:

curl -X POST http://localhost:8000/api/v1/security/policies/evaluate \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "cube": "orders",
    "user_context": {
      "user_id": "test_user",
      "roles": ["analyst"],
      "attributes": {
        "region": "north_america",
        "department": "Finance"
      }
    }
  }'

Response:

{
  "policies_evaluated": ["regional_access", "department_filter"],
  "predicates": [
    "(region = 'north_america')",
    "(department = 'Finance')"
  ],
  "combined_predicate": "(region = 'north_america') AND (department = 'Finance')",
  "access_granted": true
}

CLI Testing

# Test policy evaluation
olytix-core security test-rls \
  --cube orders \
  --user-id test_user \
  --roles analyst \
  --attributes '{"region": "north_america"}'

Common Patterns

Hierarchical Access

Allow managers to see their team's data:

name: team_hierarchy
description: Managers see their direct reports' data
cube: performance_reviews
filter_sql: |
  employee_id = $user_id
  OR manager_id = $user_id
  OR employee_id IN (
    SELECT employee_id FROM employees
    WHERE manager_id = $user_id
  )
roles:
  - manager
  - employee

Time-Windowed Access

Restrict access to recent data:

name: recent_data
description: Users can only access last 90 days
cube: transactions
filter_sql: "transaction_date >= $current_date - INTERVAL '90 days'"
roles:
  - analyst

Multi-Tenant Isolation

Isolate data by tenant:

name: tenant_isolation
description: Strict tenant data isolation
cube: "*"  # Apply to all cubes
filter_sql: "tenant_id = $user.tenant_id"
roles:
  - user
priority: 100

Ownership-Based Access

Users see only their own records:

name: ownership
description: Users see only records they own
cube: documents
filter_sql: "owner_id = $user_id OR is_public = true"
roles:
  - user

Security Best Practices

1. Start with deny-all - Default to no access, explicitly grant permissions
2. Use role-based policies - Easier to manage than user-specific rules
3. Test thoroughly - Verify policies in staging before production
4. Audit policy changes - Log all policy modifications
5. Document policies - Maintain clear documentation for compliance
6. Review regularly - Periodically audit policies for accuracy

Troubleshooting

No Data Returned

1. Check if user has matching roles/groups
2. Verify user attributes match policy conditions
3. Test policy with dry run mode

Performance Issues

1. Ensure filtered columns are indexed
2. Simplify complex SQL filters
3. Consider pre-filtering in source tables

Policy Not Applied

1. Verify policy is enabled
2. Check policy priority order
3. Confirm cube name matches exactly

Next Steps

• Configure column masking
• Enable audit logging
• Security overview