-- Verify admin role
SELECT member FROM [SHOW GRANTS ON ROLE admin] WHERE member = current_user();

-- Check CockroachDB version
SELECT version();

-- User audit logging configuration
SHOW CLUSTER SETTING sql.log.user_audit;

-- Admin audit logging
SHOW CLUSTER SETTING sql.log.admin_audit.enabled;

-- All audit-related settings
SELECT variable, value
FROM [SHOW ALL CLUSTER SETTINGS]
WHERE variable LIKE '%audit%'
ORDER BY variable;

-- Enable admin audit logging
SET CLUSTER SETTING sql.log.admin_audit.enabled = true;

-- Enable audit logging for a specific role
-- Format: <role_name> <audit_mode>
-- Audit modes: ALL (all statements), READ (reads only), WRITE (writes only), NONE (disable)
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL';

-- Audit multiple roles (newline-separated)
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL
security_admin ALL
app_service_account READ';

-- Create a role for users accessing sensitive data
CREATE ROLE sensitive_data_reader;
GRANT SELECT ON TABLE customers, payments, pii_table TO sensitive_data_reader;

-- Assign users to the audited role
GRANT sensitive_data_reader TO app_user;

-- Enable audit logging for this role
SET CLUSTER SETTING sql.log.user_audit = 'sensitive_data_reader ALL';

-- Log queries taking longer than 1 second
SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '1s';

-- Log all queries (high overhead — use only for investigation)
-- SET CLUSTER SETTING sql.log.slow_query.latency_threshold = '0';

-- Confirm settings are active
SHOW CLUSTER SETTING sql.log.user_audit;
SHOW CLUSTER SETTING sql.log.admin_audit.enabled;

-- Execute a test statement to generate an audit event
SELECT 1;

# On CockroachDB Cloud, check log export configuration
ccloud cluster info <cluster-name> -o json
# Look for log_export_config section

-- Disable user audit logging
SET CLUSTER SETTING sql.log.user_audit = '';

-- Disable admin audit logging
SET CLUSTER SETTING sql.log.admin_audit.enabled = false;

-- Reset slow query threshold to default
RESET CLUSTER SETTING sql.log.slow_query.latency_threshold;