Implement enterprise-grade role-based access control in ClickHouse using SQL-based
user management, hierarchical roles, row-level policies, and quotas.
Prerequisites
ClickHouse with access_management = 1 enabled (default in Cloud)
Admin user with GRANT OPTION
Instructions
Step 1: Create Users with Authentication
-- SHA256 password (standard)CREATEUSER app_backend
IDENTIFIED WITH sha256_password BY'strong-password-here'DEFAULT DATABASE analytics
HOST IP '10.0.0.0/8'-- Restrict to VPC
SETTINGS max_memory_usage =10000000000, -- 10GB per query
max_execution_time =60; -- 60s timeout-- Double SHA1 (MySQL wire protocol compatible)CREATEUSER legacy_app
IDENTIFIED WITH double_sha1_password BY'password'DEFAULT DATABASE analytics;
-- bcrypt (strongest, slowest — use for admin accounts)CREATEUSER admin_user
IDENTIFIED WITH bcrypt_password BY'admin-password';
-- Verify user was createdSHOWCREATEUSER app_backend;
SELECT name, host_ip, default_database FROM system.users;
Step 2: Create Role Hierarchy
-- Base roles (leaf-level permissions)
ROLE data_reader;
analytics. data_reader;
ROLE data_writer;
analytics. data_writer;
ROLE schema_manager;
, , analytics. schema_manager;
ROLE analyst;
data_reader analyst;
TEMPORARY . analyst;
ROLE developer;
data_reader, data_writer developer;
ROLE platform_admin;
data_reader, data_writer, schema_manager platform_admin;
RELOAD, FLUSH LOGS . platform_admin;
analyst app_backend;
developer app_backend;
platform_admin admin_user;
ROLE developer app_backend;
GRANTS app_backend;
ACCESS;
CREATE
GRANT
SELECT
ON
*
TO
CREATE
GRANT
INSERT
ON
*
TO
CREATE
GRANT
CREATE TABLE
ALTER TABLE
DROP
TABLE
ON
*
TO
-- Composite roles (inherit from base roles)
CREATE
GRANT
TO
-- Analysts can also create temporary tables for ad-hoc work
GRANT
CREATE
TABLE
ON
*
*
TO
CREATE
GRANT
TO
CREATE
GRANT
TO
GRANT
SYSTEM
SYSTEM
ON
*
*
TO
-- Assign roles to users
GRANT
TO
-- Read-only
GRANT
TO
-- Read + write
GRANT
TO
-- Full access
-- Set default role (active when user connects)
SET
DEFAULT
TO
-- Verify the full permission chain
SHOW
FOR
SHOW
-- All users, roles, policies
Step 3: Row-Level Security
-- Multi-tenant isolation: each user sees only their tenant's dataCREATEUSER tenant_acme
IDENTIFIED WITH sha256_password BY'pass'DEFAULT DATABASE analytics;
CREATEUSER tenant_globex
IDENTIFIED WITH sha256_password BY'pass'DEFAULT DATABASE analytics;
-- Row policy: restrict by tenant_idCREATEROW POLICY acme_isolation ON analytics.events
FORSELECTUSING tenant_id =1TO tenant_acme;
CREATEROW POLICY globex_isolation ON analytics.events
FORSELECTUSING tenant_id =2TO tenant_globex;
-- Admin sees all rows (permissive policy)CREATEROW POLICY admin_all ON analytics.events
FORSELECTUSING1=1-- No filterTO platform_admin;
-- Verify: this user only sees tenant_id = 1-- (connect as tenant_acme)SELECT tenant_id, count() FROM analytics.events GROUPBY tenant_id;
-- Returns only rows where tenant_id = 1-- List all row policiesSELECT*FROM system.row_policies;
Step 4: Column-Level Grants
-- Grant SELECT on specific columns only (hide PII)GRANTSELECT(event_id, event_type, created_at) ON analytics.events TO analyst;
-- Analyst cannot SELECT email, user_id, ip_address-- Grant INSERT on specific columns (prevent metadata injection)GRANTINSERT(event_type, user_id, properties) ON analytics.events TO data_writer;
-- Verify column-level grantsSHOW GRANTS FOR analyst;
Step 5: Quotas (Resource Limits per User)
-- Limit query resources per time intervalCREATE QUOTA analyst_quota
FORINTERVAL1HOUR
MAX queries =1000,
MAX result_rows =10000000, -- 10M result rows
MAX read_rows =1000000000, -- 1B rows read
MAX execution_time =1800-- 30 minutes totalFORINTERVAL1DAY
MAX queries =10000,
MAX read_rows =10000000000TO analyst;
-- Check quota usageSELECT
quota_name, quota_key,
interval_duration,
queries, max_queries,
result_rows, max_result_rows,
round(queries / max_queries *100, 1) AS usage_pct
FROM system.quota_usage;
-- Override quota for specific userCREATE QUOTA power_user_quota
FORINTERVAL1HOUR MAX queries =10000TO developer;
Step 6: Settings Profiles
-- Create a restrictive profile for external analystsCREATE SETTINGS PROFILE analyst_profile
SETTINGS
readonly =1, -- Read-only mode
max_memory_usage =5000000000 MIN 0 MAX 10000000000, -- 5GB, can request up to 10GB
max_execution_time =120, -- 2 min timeout
max_threads =4, -- 4 threads per query
max_result_rows =1000000, -- 1M result rows
max_concurrent_queries_for_user =5, -- 5 parallel queries
use_uncompressed_cache =0-- Don't pollute cacheTO analyst;
-- Create a profile for ETL / ingestion usersCREATE SETTINGS PROFILE writer_profile
SETTINGS
max_memory_usage =10000000000,
max_execution_time =300,
max_insert_block_size =1000000,
async_insert =1,
async_insert_busy_timeout_ms =5000TO developer;
