ClickHouse RBAC & Workload Management Lab

---

English translation of the Korean original, LLM-assisted (2026-10-06).

A hands-on environment for practicing ClickHouse RBAC (Role-Based Access Control) and workload management.

📋 Lab Overview

In this lab you will learn:

1. RBAC (Role-Based Access Control) - Creating roles and granting privileges - Creating users and assigning roles - Row-Level Security - Column-Level Security

2. Workload Management - Settings Profile (query resource limits) - Quota (time-based usage limits) - Workload Scheduling (v25+)

🗂️ File Structure

rbac-workloadmanagement/
├── README.md                      # This file
├── rbac-blog-plan.md              # Detailed plan
│
├── 01-setup.sql                   # Create test environment and data
├── 02-create-roles.sql            # Create roles and grant privileges
├── 03-create-users.sql            # Create users and assign roles
├── 04-row-policies.sql            # Row Policy (row-level security)
├── 05-column-security.sql         # Column-Level Security
├── 06-settings-profiles.sql       # Create Settings Profiles
├── 07-quotas.sql                  # Create Quotas
├── 08-workload-scheduling.sql     # Workload Scheduling (v25+)
├── 09-monitoring.sql              # Monitoring and verification queries
├── 99-cleanup.sql                 # Cleanup script
│
├── connect-as.sh                  # Helper script to connect as a user
└── test-as.sh                     # Privilege test script

🚀 Getting Started

Prerequisites

1. A ClickHouse server (local or cloud)
2. The clickhouse-client CLI tool
3. An account with administrator privileges

Environment Variables (optional)

# When using ClickHouse Cloud
export CH_HOST="your-host.clickhouse.cloud"
export CH_PORT="9440"
export CH_SECURE="--secure"

# When using a local ClickHouse
export CH_HOST="localhost"
export CH_PORT="9000"
export CH_SECURE=""

📚 Lab Steps

Step 1: Create the test environment

Connect with the administrator account and create the test database and tables.

clickhouse-client < 01-setup.sql

What gets created: - The rbac_demo database - The sales table (sales data) - The customers table (customer data, including sensitive information)

Step 2: Create roles

Create four roles and grant each the appropriate privileges.

clickhouse-client < 02-create-roles.sql

Roles created: - rbac_demo_readonly - Read-only (BI developer) - rbac_demo_analyst - Analyst (read + temporary tables) - rbac_demo_engineer - Data engineer (full privileges) - rbac_demo_partner - External partner (restricted read)

Step 3: Create users

Create a user for each role.

clickhouse-client < 03-create-users.sql

Users created: - demo_bi_user (password: SecurePass123!) - demo_analyst (password: AnalystPass456!) - demo_engineer (password: EngineerPass789!) - demo_partner (password: PartnerPass000!)

Step 4: Set up the Row Policy

Set up row-level security. The partner is restricted to seeing only APAC region data.

clickhouse-client < 04-row-policies.sql

Step 5: Set up column-level security

Restrict access to sensitive information (email, phone).

clickhouse-client < 05-column-security.sql

Step 6: Create Settings Profiles

Set query resource limits.

clickhouse-client < 06-settings-profiles.sql

Limits per role: - BI: 5GB memory, 1 minute execution, 2 threads - Analyst: 10GB memory, 5 minutes execution, 4 threads - Engineer: 50GB memory, 1 hour execution, 16 threads - Partner: 2GB memory, 30 seconds execution, 2 threads

Step 7: Create Quotas

Set time-based usage limits.

clickhouse-client < 07-quotas.sql

Step 8: Workload Scheduling (ClickHouse 25+)

Set up workload scheduling. (Requires ClickHouse 25.4 or later)

clickhouse-client < 08-workload-scheduling.sql

Step 9: Monitoring

Review and monitor what has been configured.

clickhouse-client < 09-monitoring.sql

🔧 User Connection Tools

connect-as.sh - Interactive connection

Connect to ClickHouse as a specific user.

# Connect as analyst
./connect-as.sh analyst

# Connect as BI user
./connect-as.sh bi

# Connect as engineer
./connect-as.sh engineer

# Connect as partner
./connect-as.sh partner

Running queries directly

# Run a single query
./connect-as.sh analyst "SELECT count() FROM rbac_demo.sales"

# Check a setting
./connect-as.sh analyst "SELECT getSetting('max_memory_usage')"

test-as.sh - Privilege test

Automatically tests the privileges of every user.

./test-as.sh

🧪 Test Scenarios

1. Row Policy test

Connect as demo_partner:

-- Only APAC data is visible (3 rows)
SELECT region, count() FROM rbac_demo.sales GROUP BY region;

Connect as demo_analyst:

-- Data for all regions is visible (8 rows)
SELECT region, count() FROM rbac_demo.sales GROUP BY region;

2. Column Security test

Connect as demo_analyst:

-- ✗ Error (no privilege on email, phone)
SELECT * FROM rbac_demo.customers;

-- ✓ Succeeds (allowed columns only)
SELECT id, name, region FROM rbac_demo.customers;

-- ✗ Error
SELECT email FROM rbac_demo.customers;

Connect as demo_engineer:

-- ✓ Succeeds (all columns accessible)
SELECT * FROM rbac_demo.customers;

3. Settings Profile test

Connect as demo_analyst:

-- Check current settings
SELECT getSetting('max_memory_usage');  -- 10000000000 (10GB)
SELECT getSetting('max_execution_time');  -- 300 (5 minutes)

-- Time limit test (error after 5 minutes)
SELECT sleep(1) FROM numbers(400);

-- readonly test (error)
INSERT INTO rbac_demo.sales VALUES (999, 'TEST', 'Test', 0, today(), 0);

4. Quota test

Connect as demo_analyst:

-- Check current Quota usage
SELECT * FROM system.quota_usage;

-- Run queries several times, then check usage
SELECT count() FROM rbac_demo.sales;  -- Run several times
SELECT * FROM system.quota_usage;  -- Confirm the queries count increases

5. Workload Scheduling test

Run queries with different workloads:

-- High priority (analytics)
SELECT count() FROM rbac_demo.sales
SETTINGS workload = 'demo_analytics';

-- Low priority (adhoc)
SELECT * FROM numbers(1000000)
SETTINGS workload = 'demo_adhoc';

-- Check scheduler state
SELECT * FROM system.scheduler ORDER BY path;

📊 Monitoring Queries

Per-user query statistics (last hour)

SELECT
    user,
    count() as query_count,
    countIf(type = 'QueryFinish') as successful,
    countIf(type = 'ExceptionWhileProcessing') as failed,
    round(avg(query_duration_ms), 2) as avg_duration_ms
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR
  AND user LIKE 'demo_%'
GROUP BY user;

Currently running queries

SELECT
    query_id,
    user,
    elapsed,
    formatReadableSize(memory_usage) as memory,
    substring(query, 1, 100) as query_preview
FROM system.processes
WHERE user LIKE 'demo_%';

Check Quota usage

SELECT
    quota_name,
    queries,
    max_queries,
    execution_time,
    max_execution_time,
    formatReadableSize(read_bytes) as read_bytes,
    formatReadableSize(max_read_bytes) as max_read_bytes
FROM system.quota_usage
WHERE quota_name LIKE 'demo_%';

🧹 Cleanup

When the lab is over, clean up all resources.

clickhouse-client < 99-cleanup.sql

Items cleaned up: - Row Policies - Quotas - Settings Profiles - Users - Roles - Workloads - Resources - Database (rbac_demo)

📖 References

Official Documentation

• ClickHouse Access Rights
• Settings Profiles
• Quotas
• Workload Scheduling

System Tables

• system.users - User list
• system.roles - Role list
• system.grants - Grant history
• system.row_policies - Row Policy list
• system.settings_profiles - Settings Profile list
• system.quotas - Quota settings
• system.quota_usage - Quota usage
• system.workloads - Workload settings
• system.scheduler - Scheduler state
• system.query_log - Query log

💡 Best Practices

RBAC

1. Principle of least privilege: Grant only the minimum privileges needed
2. Role-based management: Use Roles instead of granting privileges directly to users
3. Naming convention: Use a clear naming rule ({env}_{team}_{access_level})
4. Regular audits: Review privileges quarterly and remove unnecessary ones
5. Use Row Policy: Apply row-level security to sensitive data

Workload Management

1. Tiered profiles: Design profiles to fit real-time / interactive / batch jobs
2. Dual limits: Apply hourly and daily Quotas together
3. Workload separation: Separate jobs with different priorities into separate workloads
4. Monitoring: Check resource usage periodically

❗ Cautions

1. Password security: Do not use the lab passwords in production
2. HOST restriction: In production, always restrict to specific IPs
3. Workload Scheduling: Available only on ClickHouse 25.4 or later
4. Cleanup script: 99-cleanup.sql deletes all data, so be careful

🆘 Troubleshooting

"Access Denied" error

-- Check privileges
SHOW GRANTS FOR demo_analyst;
SELECT * FROM system.grants WHERE user_name = 'demo_analyst';

Queries are slow (Profile/Quota limits)

-- Check current settings
SELECT name, value FROM system.settings WHERE changed = 1;
SELECT * FROM system.quota_usage;

Row Policy is not applied

-- Check Row Policy
SELECT * FROM system.row_policies WHERE table = 'sales';

📝 License

MIT — same as the rest of the repository.

---

rbac-workloadmanagement/
├── README.md                      # 이 파일
├── rbac-blog-plan.md              # 상세 계획서
│
├── 01-setup.sql                   # 테스트 환경 및 데이터 생성
├── 02-create-roles.sql            # 역할 생성 및 권한 부여
├── 03-create-users.sql            # 사용자 생성 및 역할 할당
├── 04-row-policies.sql            # Row Policy (행 수준 보안)
├── 05-column-security.sql         # Column-Level Security
├── 06-settings-profiles.sql       # Settings Profile 생성
├── 07-quotas.sql                  # Quota 생성
├── 08-workload-scheduling.sql     # Workload Scheduling (v25+)
├── 09-monitoring.sql              # 모니터링 및 검증 쿼리
├── 99-cleanup.sql                 # 정리 스크립트
│
├── connect-as.sh                  # 사용자 접속 헬퍼 스크립트
└── test-as.sh                     # 권한 테스트 스크립트

# ClickHouse Cloud를 사용하는 경우
export CH_HOST="your-host.clickhouse.cloud"
export CH_PORT="9440"
export CH_SECURE="--secure"

# 로컬 ClickHouse를 사용하는 경우
export CH_HOST="localhost"
export CH_PORT="9000"
export CH_SECURE=""

clickhouse-client < 01-setup.sql

clickhouse-client < 02-create-roles.sql

clickhouse-client < 03-create-users.sql

clickhouse-client < 04-row-policies.sql

clickhouse-client < 05-column-security.sql

clickhouse-client < 06-settings-profiles.sql

clickhouse-client < 07-quotas.sql

clickhouse-client < 08-workload-scheduling.sql

clickhouse-client < 09-monitoring.sql

# 분석가로 접속
./connect-as.sh analyst

# BI 사용자로 접속
./connect-as.sh bi

# 엔지니어로 접속
./connect-as.sh engineer

# 파트너로 접속
./connect-as.sh partner

# 단일 쿼리 실행
./connect-as.sh analyst "SELECT count() FROM rbac_demo.sales"

# 설정 확인
./connect-as.sh analyst "SELECT getSetting('max_memory_usage')"

./test-as.sh

-- APAC 데이터만 보임 (3개 행)
SELECT region, count() FROM rbac_demo.sales GROUP BY region;

-- 모든 지역 데이터 보임 (8개 행)
SELECT region, count() FROM rbac_demo.sales GROUP BY region;

-- ✗ 에러 발생 (email, phone 권한 없음)
SELECT * FROM rbac_demo.customers;

-- ✓ 성공 (허용된 컬럼만)
SELECT id, name, region FROM rbac_demo.customers;

-- ✗ 에러 발생
SELECT email FROM rbac_demo.customers;

-- ✓ 성공 (모든 컬럼 접근 가능)
SELECT * FROM rbac_demo.customers;

-- 현재 설정 확인
SELECT getSetting('max_memory_usage');  -- 10000000000 (10GB)
SELECT getSetting('max_execution_time');  -- 300 (5분)

-- 시간 제한 테스트 (5분 초과 시 에러)
SELECT sleep(1) FROM numbers(400);

-- readonly 테스트 (에러 발생)
INSERT INTO rbac_demo.sales VALUES (999, 'TEST', 'Test', 0, today(), 0);

-- 현재 Quota 사용량 확인
SELECT * FROM system.quota_usage;

-- 쿼리 여러 번 실행 후 사용량 확인
SELECT count() FROM rbac_demo.sales;  -- 여러 번 실행
SELECT * FROM system.quota_usage;  -- queries 카운트 증가 확인

-- 높은 우선순위 (analytics)
SELECT count() FROM rbac_demo.sales
SETTINGS workload = 'demo_analytics';

-- 낮은 우선순위 (adhoc)
SELECT * FROM numbers(1000000)
SETTINGS workload = 'demo_adhoc';

-- 스케줄러 상태 확인
SELECT * FROM system.scheduler ORDER BY path;

SELECT
    user,
    count() as query_count,
    countIf(type = 'QueryFinish') as successful,
    countIf(type = 'ExceptionWhileProcessing') as failed,
    round(avg(query_duration_ms), 2) as avg_duration_ms
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR
  AND user LIKE 'demo_%'
GROUP BY user;

SELECT
    query_id,
    user,
    elapsed,
    formatReadableSize(memory_usage) as memory,
    substring(query, 1, 100) as query_preview
FROM system.processes
WHERE user LIKE 'demo_%';

SELECT
    quota_name,
    queries,
    max_queries,
    execution_time,
    max_execution_time,
    formatReadableSize(read_bytes) as read_bytes,
    formatReadableSize(max_read_bytes) as max_read_bytes
FROM system.quota_usage
WHERE quota_name LIKE 'demo_%';

clickhouse-client < 99-cleanup.sql

-- 권한 확인
SHOW GRANTS FOR demo_analyst;
SELECT * FROM system.grants WHERE user_name = 'demo_analyst';

-- 현재 설정 확인
SELECT name, value FROM system.settings WHERE changed = 1;
SELECT * FROM system.quota_usage;

-- Row Policy 확인
SELECT * FROM system.row_policies WHERE table = 'sales';