AskTable Permission Control Complete Guide: Four-Level Permission System Protecting Enterprise Data Security

In enterprise-level data analysis scenarios, data security is paramount. Users of different roles should only access data within their permission scope. AskTable provides a complete permission control system supporting four-level permission control at database/table/field/row levels.

This article provides a comprehensive explanation of AskTable's permission control mechanism to help you build a secure and reliable data analysis system.

---

1. Why Do We Need Permission Control?

1. Typical Application Scenarios

Scenario 1: Multi-Tenant Data Isolation

• •SaaS platform has multiple customers
• •Each customer can only see their own data
• •Need to achieve data isolation in the same database

Scenario 2: Subsidiary Data Permissions

• •Group company has multiple subsidiaries
• •Shanghai subsidiary can only see Shanghai data
• •Beijing subsidiary can only see Beijing data
• •Headquarters can see all data

Scenario 3: Sensitive Field Protection

• •Database has sensitive fields (phone numbers, ID cards, bank cards)
• •Regular employees cannot view sensitive information
• •Administrators can view complete information

Scenario 4: Environment Isolation

• •Production and test environment data are separated
• •Test personnel can only access test environment
• •Production personnel can only access production environment

2. Problems with Traditional Solutions

Application Layer Control:

• •❌ Permission checks are easily missed
• •❌ Code is scattered, difficult to maintain
• •❌ Cannot handle complex SQL queries

Database Views:

• •❌ View count explosion (roles × tables)
• •❌ Cannot dynamically pass user information
• •❌ High maintenance costs

AskTable's Solution:

• •✅ Unified permission management
• •✅ Dynamic permission variables
• •✅ Automatic SQL rewriting
• •✅ Four-level permission control

---

2. Core Concepts

1. Data Hierarchy

AskTable supports permission control at each level:

• •Datasource Level: Control which datasources can be accessed
• •Schema Level: Control which databases can be accessed
• •Table Level: Control which tables can be accessed
• •Field Level: Control which fields can be accessed
• •Row Level: Control which data rows can be accessed

2. Access Policy

An Access Policy defines what data a person can (or cannot) access.

Core Attributes:

• •permission: Access permission (allow or deny)
• •name: Policy name
• •dataset_config: Dataset configuration

Policy Types:

• •Allow Policy: Whitelist, can only access specified data
• •Deny Policy: Blacklist, cannot access specified data

3. Role

A Role is a collection of access policies. Users access data by assuming roles.

Role Characteristics:

• •A role can contain multiple access policies
• •Supports dynamic variables (such as {{user_id}})
• •Variable values can be passed at query time

---

3. Four-Level Permission Control

1. Datasource Level Permission

Control which datasources can be accessed:

{
  "permission": "allow",
  "name": "production_only",
  "dataset_config": {
    "datasource_ids": "ds_prod_001"
  }
}

Effect:

• •Can only access production environment datasource
• •Cannot access test environment datasource

2. Schema and Table Level Permission

Using Regular Expression Matching:

{
  "permission": "allow",
  "name": "public_tables_only",
  "dataset_config": {
    "datasource_ids": "*",
    "regex_patterns": {
      "schemas_regex_pattern": "^public$",
      "tables_regex_pattern": "^(users|orders|products)$"
    }
  }
}

Effect:

• •Can only access public schema
• •Can only access users, orders, products three tables

3. Field Level Permission

Hiding Sensitive Fields:

{
  "permission": "deny",
  "name": "hide_sensitive_fields",
  "dataset_config": {
    "datasource_ids": "*",
    "regex_patterns": {
      "fields_regex_pattern": ".*password.*|.*pwd.*|.*phone.*|.*id_card.*"
    }
  }
}

Effect:

• •Cannot access fields containing password, pwd, phone, id_card
• •AI will automatically exclude these fields when generating SQL

4. Row Level Permission

Filtering Data Rows:

{
  "permission": "allow",
  "name": "regional_data",
  "dataset_config": {
    "datasource_ids": "ds_001",
    "rows_filters": {
      "ds_001": [
        "public.orders.region = {{region}}",
        "public.users.status = 'active'"
      ]
    }
  }
}

Effect:

• •Can only query orders for specified region
• •Can only query users with status = active
• •SQL will automatically inject WHERE conditions

---

4. Dynamic Permission Variables

1. What Are Dynamic Variables?

Dynamic variables allow passing parameters at query time to achieve more flexible permission control.

Syntax: {{variable_name}}

Supported Variable Types:

• •User ID: {{user_id}}
• •Region: {{region}}
• •Department: {{department_id}}
• •Any custom variable

2. Usage Example

Defining Policy:

{
  "permission": "allow",
  "name": "user_own_data",
  "dataset_config": {
    "datasource_ids": "ds_001",
    "rows_filters": {
      "ds_001": [
        "public.orders.user_id = {{user_id}}",
        "public.*.created_by = {{user_id}}"
      ]
    }
  }
}

Passing Variables at Query Time:

from asktable import Asktable

client = Asktable(api_key="your_api_key")

# Pass user_id when querying
response = client.answers.create(
    datasource_id="ds_001",
    question="Query my orders",
    role_id="role_employee",
    role_variables={"user_id": "12345"}
)

Actually Executed SQL:

SELECT * FROM orders
WHERE user_id = '12345'  -- Automatically injected

3. Advanced Usage

Supported Operators:

• •Equal: =
• •Not equal: !=, <>
• •Greater/Less than: >, <, >=, <=
• •IN: IN (value1, value2)
• •LIKE: LIKE 'pattern'
• •IS NULL: IS NULL, IS NOT NULL

Supported Functions:

• •NOW(): Current time
• •Time calculations: NOW() - 1 YEAR, NOW() - 30 DAY

Example:

{
  "rows_filters": {
    "ds_001": [
      "public.orders.created_at > NOW() - 30 DAY",
      "public.orders.amount >= 1000",
      "public.orders.status IN ('paid', 'shipped')"
    ]
  }
}

---

5. Practical Cases

Case 1: Multi-Tenant Data Isolation

Scenario: SaaS platform, each tenant can only see their own data

Step 1: Create Access Policy

{
  "permission": "allow",
  "name": "tenant_isolation",
  "dataset_config": {
    "datasource_ids": "ds_saas",
    "rows_filters": {
      "ds_saas": [
        "*.*.tenant_id = {{tenant_id}}"
      ]
    }
  }
}

Step 2: Create Role

{
  "name": "tenant_user",
  "policies": ["tenant_isolation"]
}

Step 3: Pass Tenant ID at Query Time

response = client.answers.create(
    datasource_id="ds_saas",
    question="Query total users",
    role_id="role_tenant_user",
    role_variables={"tenant_id": "tenant_abc"}
)

Effect:

• •Tenant A can only see their own data
• •Tenant B can only see their own data
• •Completely isolated, no interference

Case 2: Subsidiary Data Permissions

Scenario: Group company, subsidiaries can only see their own regional data

Step 1: Create Regional Policy

{
  "permission": "allow",
  "name": "regional_access",
  "dataset_config": {
    "datasource_ids": "ds_erp",
    "rows_filters": {
      "ds_erp": [
        "public.orders.region = {{region}}",
        "public.customers.region = {{region}}",
        "public.employees.region = {{region}}"
      ]
    }
  }
}

Step 2: Create Role

{
  "name": "regional_manager",
  "policies": ["regional_access"]
}

Step 3: Queries from Different Regions

# Shanghai subsidiary
response = client.answers.create(
    datasource_id="ds_erp",
    question="Query this month's sales",
    role_id="role_regional_manager",
    role_variables={"region": "Shanghai"}
)

# Beijing subsidiary
response = client.answers.create(
    datasource_id="ds_erp",
    question="Query this month's sales",
    role_id="role_regional_manager",
    role_variables={"region": "Beijing"}
)

Case 3: Sensitive Field Masking

Scenario: Regular employees cannot view sensitive information like phone numbers and ID cards

Step 1: Create Sensitive Field Deny Policy

{
  "permission": "deny",
  "name": "hide_pii",
  "dataset_config": {
    "datasource_ids": "*",
    "regex_patterns": {
      "fields_regex_pattern": ".*phone.*|.*mobile.*|.*id_card.*|.*ssn.*|.*credit_card.*"
    }
  }
}

Step 2: Create Regular Employee Role

{
  "name": "employee",
  "policies": ["hide_pii"]
}

Step 3: Create Administrator Role (Unrestricted)

{
  "name": "admin",
  "policies": []  // Unrestricted
}

Effect:

• •When regular employees query, AI will not select sensitive fields
• •Administrators can query all fields

Case 4: Time Range Restriction

Scenario: Employees can only query data from the last 90 days

Step 1: Create Time Restriction Policy

{
  "permission": "allow",
  "name": "recent_data_only",
  "dataset_config": {
    "datasource_ids": "ds_001",
    "rows_filters": {
      "ds_001": [
        "*.*.created_at > NOW() - 90 DAY"
      ]
    }
  }
}

Effect:

• •All queries are automatically restricted to the last 90 days
• •Cannot query historical data

---

6. Permission Policy Best Practices

1. Principle of Least Privilege

Only grant necessary permissions:

• •Default deny all access
• •Explicitly grant needed permissions
• •Regularly review permission configuration

Example:

// Bad practice: grant all permissions
{
  "permission": "allow",
  "dataset_config": {
    "datasource_ids": "*"
  }
}

// Good practice: explicitly specify
{
  "permission": "allow",
  "dataset_config": {
    "datasource_ids": "ds_001",
    "regex_patterns": {
      "tables_regex_pattern": "^(users|orders)$"
    }
  }
}

2. Layered Permission Design

Recommended permission hierarchy:

1. •Base Permissions: Permissions all users have
2. •Department Permissions: Permissions divided by department
3. •Role Permissions: Permissions divided by role
4. •Personal Permissions: Special permissions for specific users

3. Using Regular Expressions

Flexible matching of table and field names:

{
  "regex_patterns": {
    // Match all tables starting with user_
    "tables_regex_pattern": "^user_.*$",

    // Match all tables containing temp or test
    "tables_regex_pattern": ".*(temp|test).*",

    // Match all sensitive fields
    "fields_regex_pattern": ".*(password|pwd|secret|token|key).*"
  }
}

4. Dynamic Variable Naming Conventions

Recommended naming conventions:

• •Use lowercase letters and underscores
• •Be semantically clear
• •Avoid abbreviations

Example:

✅ user_id
✅ department_id
✅ region_code
✅ start_date

❌ uid
❌ did
❌ r
❌ sd

---

7. Permission Testing and Verification

1. Testing Strategy

Testing Checklist:

• • Test allowed data access
• • Test denied data access
• • Test dynamic variable substitution
• • Test boundary conditions
• • Test multiple policy combinations

2. Verification Methods

Method 1: Use MCP Testing

from asktable import Asktable

client = Asktable(api_key="your_api_key")

# Test query
response = client.sqls.create(
    datasource_id="ds_001",
    question="Query all users",
    role_id="role_employee",
    role_variables={"user_id": "12345"}
)

# View generated SQL
print(response.query.sql)
# Should include: WHERE user_id = '12345'

Method 2: View SQL Directly

In AskTable interface:

1. •Use "Generate SQL" function
2. •View the generated SQL statement
3. •Confirm permission conditions are correctly injected

3. Common Troubleshooting

Problem 1: Permissions Not Taking Effect

Check:

• •Is the role configured correctly?
• •Is the policy correctly associated with the role?
• •Is role_id passed at query time?

Problem 2: Dynamic Variables Not Replaced

Check:

• •Is the variable name correct (case-sensitive)?
• •Is role_variables passed?
• •Is the variable value type correct?

Problem 3: Permissions Too Strict

Check:

• •Are there conflicting deny policies?
• •Is the regular expression too strict?
• •Are row filter conditions too strict?

---

8. Advanced Features

1. Multiple Policy Combinations

A role can contain multiple policies:

{
  "name": "sales_manager",
  "policies": [
    "regional_access",      // Regional restriction
    "hide_pii",            // Hide sensitive information
    "recent_data_only"     // Time restriction
  ]
}

Policy Priority:

• •Deny policies take precedence over Allow policies
• •Multiple Allow policies take intersection
• •Multiple Deny policies take union

2. Wildcard Usage

Datasource Wildcard:

{
  "datasource_ids": "*"  // All datasources
}

Table Name Wildcard:

{
  "rows_filters": {
    "ds_001": [
      "*.*.user_id = {{user_id}}"  // user_id field across all tables
    ]
  }
}

3. Complex Filter Conditions

Multiple Condition Combinations:

{
  "rows_filters": {
    "ds_001": [
      "public.orders.user_id = {{user_id}}",
      "public.orders.status IN ('paid', 'shipped')",
      "public.orders.created_at > NOW() - 30 DAY"
    ]
  }
}

Note:

• •Multiple conditions are AND relationships
• •Each condition takes effect independently

---

9. Summary

AskTable's permission control system provides:

Core Capabilities: ✅ Four-level permission control (datasource/Schema/table/field/row) ✅ Dynamic permission variables ✅ Flexible policy combinations ✅ Automatic SQL rewriting

Best Practices: ✅ Follow the principle of least privilege ✅ Use dynamic variables for flexible control ✅ Regularly review and test permission configuration ✅ Document permission design

Next Steps:

• •Enterprise Data Security Best Practices
• •AskTable Datasource Integration Guide

---

Related Reading:

• •AskTable SQL Permission Guard
• •MCP User Guide

Technical Exchange:

• •Website: asktable.com