Security and Access Control Guide

-- Create a new user with password
CREATE USER john
IDENTIFIED WITH sha256_password BY 'secure_password'
SETTINGS max_memory_usage = 10000000000;  -- 10GB limit

-- Modify user settings
ALTER USER john
SETTINGS max_memory_usage = 20000000000;  -- 20GB limit

-- List all users
SELECT *
FROM system.users
WHERE name NOT LIKE '%default%';

-- Create a quota
CREATE QUOTA user_quota
    KEYED BY ip_address
    FOR INTERVAL 1 hour MAX queries = 1000
    FOR INTERVAL 1 day MAX result_rows = 1000000;

-- Apply quota to user
ALTER USER john
    QUOTA user_quota;

-- Create a role
CREATE ROLE analyst;

-- Grant privileges to role
GRANT SELECT ON database.* TO analyst;
GRANT SELECT ON system.query_log TO analyst;

-- Assign role to user
GRANT analyst TO john;

-- List role grants
SELECT *
FROM system.grants
WHERE user_name = 'john';

-- Create role hierarchy
CREATE ROLE junior_analyst;
CREATE ROLE senior_analyst;

GRANT SELECT ON database.small_tables TO junior_analyst;
GRANT SELECT ON database.* TO senior_analyst;
GRANT junior_analyst TO senior_analyst;

-- Set default role
ALTER USER john DEFAULT ROLE senior_analyst;

-- Create row policy
CREATE ROW POLICY department_access
ON database.employees
FOR SELECT
USING department_id = currentUser();

-- Apply policy to role
ALTER ROW POLICY department_access
ON database.employees
TO analyst;

-- List active policies
SELECT *
FROM system.row_policies
WHERE database = 'database'
  AND table = 'employees';

-- Check if connection is secure
SELECT value
FROM system.settings
WHERE name = 'secure';

-- View SSL configuration
SELECT *
FROM system.ssl_certificates;

-- Enable query logging
SET log_queries = 1;

-- View query history
SELECT
    user,
    query_id,
    query,
    exception,
    event_time
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date >= today() - 1
ORDER BY event_time DESC;

-- View access attempts
SELECT
    user_name,
    access_type,
    grant_option,
    database,
    table,
    column,
    creation_time
FROM system.grants
ORDER BY creation_time DESC;

-- Monitor current sessions
SELECT
    user,
    client_hostname,
    client_name,
    elapsed,
    query
FROM system.processes
ORDER BY elapsed DESC;

-- Check authentication failures
SELECT
    user,
    authentication_method,
    exception,
    event_time
FROM system.query_log
WHERE type = 'ExceptionBeforeStart'
  AND event_date >= today() - 1
  AND exception LIKE '%Authentication%'
ORDER BY event_time DESC;

-- Check user privileges
SELECT *
FROM system.grants
WHERE user_name = 'problem_user';

-- Check role assignments
SELECT *
FROM system.role_grants
WHERE user_name = 'problem_user';

-- Check recent authentication issues
SELECT
    user,
    client_hostname,
    exception
FROM system.query_log
WHERE event_date >= today()
  AND type = 'ExceptionBeforeStart'
  AND exception LIKE '%Authentication%'
ORDER BY event_time DESC;