Standardmäßig ist der Prompt ausgewählt, der zuerst die Quelle prüft. Sie können zu einem direkten Befehl wechseln oder eine lokale Kopie herunterladen.
Quelldateien prüfen
Lesen Sie SKILL.md und alle von SkillsMP angezeigten Begleitdateien, bevor Sie sich für eine Installation entscheiden.
Mit Codex oder Claude installieren Kopieren Sie diesen Prompt, fügen Sie ihn in Codex, Claude oder einen anderen Assistant ein und lassen Sie die Skill-Seite prüfen und installieren.
Ein direkter Befehl überspringt den Prüf-Prompt. Prüfen Sie die Quelle, bevor Sie ihn ausführen.
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;
