hardening-user-privileges

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