hardening-user-privileges

name	hardening-user-privileges
description	Hardens CockroachDB user privileges by auditing and tightening role-based access control, reducing admin grants, restricting PUBLIC role permissions, and applying least-privilege principles. Use when reducing excessive privileges, cleaning up admin access, or implementing RBAC best practices.
compatibility	Requires admin role to modify grants and role membership.
metadata	{"author":"cockroachdb","version":"1.0"}

Hardening User Privileges

Audits and tightens CockroachDB role-based access control (RBAC) by identifying over-privileged users, reducing admin grants, restricting PUBLIC role permissions, creating purpose-specific roles, and applying least-privilege principles.

When to Use This Skill

• Reducing the number of users with admin role
• Removing excessive PUBLIC role privileges (SELECT, INSERT, UPDATE, DELETE)
• Creating purpose-specific roles to replace broad admin grants
• Responding to a security audit finding about excessive privileges
• Implementing RBAC best practices for a production cluster
• Onboarding a cluster to a least-privilege access model

Prerequisites

• SQL access with admin role (required to modify grants and role membership)
• User inventory: Understanding of which users/applications need which level of access
• Application testing plan: Revoking grants can break applications that depend on them

Check your access:

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

Steps

1. Audit Current Users and Roles

-- List all users and their role memberships
SELECT
  username,
  options,
  member_of
FROM [SHOW USERS]
ORDER BY username;

-- Count admin role members
SELECT COUNT(*) AS admin_count
FROM [SHOW GRANTS ON ROLE admin];

-- List all admin users
SELECT member AS admin_user
FROM [SHOW GRANTS ON ROLE admin]
WHERE is_admin = true
ORDER BY member;

See SQL queries reference for additional audit queries.

2. Identify Over-Privileged Users

Admin role review:

-- Admin users — each should have a documented reason for admin access
SELECT member AS admin_user
FROM [SHOW GRANTS ON ROLE admin]
WHERE is_admin = true
ORDER BY member;

Evaluate each admin user:

• Keep admin: Cluster operators, DBAs, automation accounts that genuinely need full access
• Downgrade: Developers, analysts, application service accounts that only need specific permissions

PUBLIC role review:

-- Check what PUBLIC can do (these apply to ALL users)
SELECT
  database_name,
  schema_name,
  object_name,
  object_type,
  privilege_type
FROM [SHOW GRANTS FOR public]
WHERE privilege_type NOT IN ('USAGE')
  AND schema_name = 'public'
ORDER BY database_name, object_name;

System privilege review:

-- Users with sensitive system privileges
SELECT grantee, privilege_type
FROM [SHOW SYSTEM GRANTS]
WHERE privilege_type IN (
  'MODIFYCLUSTERSETTING',
  'CANCELQUERY',
  'CANCELSESSION',
  'VIEWACTIVITY',
  'CREATEDB',
  'CREATELOGIN'
)
ORDER BY privilege_type, grantee;

3. Create Purpose-Specific Roles

Replace broad admin grants with targeted roles. Database-level grants in CockroachDB only support CONNECT, CREATE, DROP, ZONECONFIG, BACKUP, RESTORE, and ALL — data-access privileges (SELECT, INSERT, UPDATE, DELETE) live at the schema or table level. Pair GRANT ... ON ALL TABLES IN SCHEMA (covers existing tables) with ALTER DEFAULT PRIVILEGES (covers future tables created by the listed grantors) so new tables inherit the intended access.

-- Read-only role for analysts
CREATE ROLE analyst_reader;
GRANT CONNECT ON DATABASE <app_db> TO analyst_reader;
GRANT USAGE ON SCHEMA <app_db>.public TO analyst_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA <app_db>.public TO analyst_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA <app_db>.public GRANT SELECT ON TABLES TO analyst_reader;

-- Application service role (read + write, no DDL)
CREATE ROLE app_service;
GRANT CONNECT ON DATABASE <app_db> TO app_service;
GRANT USAGE ON SCHEMA <app_db>.public TO app_service;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <app_db>.public TO app_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA <app_db>.public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_service;

-- Schema management role (DDL only)
CREATE ROLE schema_manager;
GRANT CREATE ON DATABASE <app_db> TO schema_manager;

-- Monitoring role (read-only system visibility)
CREATE ROLE monitoring;
GRANT SYSTEM VIEWACTIVITYREDACTED TO monitoring;

-- Operations role (triage + cancel, no data access)
CREATE ROLE ops_triage;
GRANT SYSTEM VIEWACTIVITYREDACTED, CANCELQUERY TO ops_triage;

4. Reassign Users to Purpose-Specific Roles

-- Assign users to their appropriate roles
GRANT analyst_reader TO analyst_user;
GRANT app_service TO payment_service, order_service;
GRANT schema_manager TO migration_user;
GRANT monitoring TO monitoring_user;
GRANT ops_triage TO oncall_sre;

5. Revoke Excessive Grants

Revoke admin from users who no longer need it:

-- Revoke admin from specific users
REVOKE admin FROM analyst_user;
REVOKE admin FROM payment_service;
REVOKE admin FROM monitoring_user;

Revoke PUBLIC role data grants:

-- Revoke SELECT from PUBLIC on existing tables, plus the default-privilege grant
REVOKE SELECT ON ALL TABLES IN SCHEMA <app_db>.public FROM public;
ALTER DEFAULT PRIVILEGES IN SCHEMA <app_db>.public REVOKE SELECT ON TABLES FROM public;

-- Revoke all data privileges from PUBLIC on specific tables
REVOKE ALL ON TABLE <sensitive_table> FROM public;

Revoke unnecessary system privileges:

-- Revoke system privileges from users who don't need them
REVOKE SYSTEM MODIFYCLUSTERSETTING FROM <username>;
REVOKE SYSTEM CREATEDB FROM <username>;

6. Verify Changes

-- Confirm admin count is reduced
SELECT COUNT(*) AS admin_count FROM [SHOW GRANTS ON ROLE admin];

-- Confirm PUBLIC privileges are minimal
SELECT database_name, privilege_type
FROM [SHOW GRANTS FOR public]
WHERE privilege_type NOT IN ('USAGE');

-- Verify specific user's effective privileges
SHOW GRANTS FOR <username>;

Application testing: After revoking grants, verify that all applications still function correctly. Test:

• Read operations (SELECT)
• Write operations (INSERT, UPDATE, DELETE)
• Schema operations (CREATE, ALTER, DROP) — only for schema management accounts
• Connection and authentication

Safety Considerations

Revoking grants can break applications. Applications that depend on admin, PUBLIC, or specific grants will fail with permission errors if those grants are revoked.

Mitigation steps:

1. Audit before revoking: Document which users/apps depend on which grants
2. Create replacement roles first: Assign purpose-specific roles before revoking admin
3. Test in staging: Revoke grants in a staging environment first and test all application flows
4. Revoke incrementally: Revoke one user/grant at a time and test
5. Monitor for errors: Watch application logs for permission-denied errors after changes

Do not revoke admin from:

• The last remaining admin user (you'll lose the ability to manage the cluster)
• Automation accounts that manage schema migrations (unless you've created a schema_manager role)
• The root user (built-in, cannot be revoked)

Rollback

If an application breaks after revoking a grant:

-- Re-grant admin (emergency)
GRANT admin TO <username>;

-- Re-grant specific privileges
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA <app_db>.public TO <username>;

-- Re-grant PUBLIC privileges
GRANT SELECT ON ALL TABLES IN SCHEMA <app_db>.public TO public;

Best practice: Keep a record of all grants before revoking so you can restore them if needed:

-- Snapshot current grants before changes
SELECT * FROM [SHOW GRANTS FOR <username>];
SELECT * FROM [SHOW GRANTS FOR public];
SELECT * FROM [SHOW SYSTEM GRANTS];

References

Skill references:

• SQL queries for privilege hardening

Related skills:

• auditing-cloud-cluster-security — Run a full security posture audit
• configuring-audit-logging — Set up audit logging for privilege-sensitive operations

Official CockroachDB Documentation:

• Authorization Overview
• GRANT
• REVOKE
• CREATE ROLE
• SHOW GRANTS
• System Privileges