Update to CockroachDB

{
  "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.",
  "included_files": [
    {
      "relative_path": "references/sql-queries.md",
      "size_in_bytes": 4767
    }
  ],
  "name": "hardening-user-privileges",
  "skill_md_contents": "---\nname: hardening-user-privileges\ndescription: 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.\ncompatibility: Requires admin role to modify grants and role membership.\n---\n\n# Hardening User Privileges\n\nAudits 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.\n\n## When to Use This Skill\n\n- Reducing the number of users with admin role\n- Removing excessive PUBLIC role privileges (SELECT, INSERT, UPDATE, DELETE)\n- Creating purpose-specific roles to replace broad admin grants\n- Responding to a security audit finding about excessive privileges\n- Implementing RBAC best practices for a production cluster\n- Onboarding a cluster to a least-privilege access model\n\n## Prerequisites\n\n- **SQL access** with admin role (required to modify grants and role membership)\n- **User inventory:** Understanding of which users/applications need which level of access\n- **Application testing plan:** Revoking grants can break applications that depend on them\n\n**Check your access:**\n```sql\nSELECT member FROM [SHOW GRANTS ON ROLE admin] WHERE member = current_user();\n```\n\n## Steps\n\n### 1. Audit Current Users and Roles\n\n```sql\n-- List all users and their role memberships\nSELECT\n  username,\n  options,\n  member_of\nFROM [SHOW USERS]\nORDER BY username;\n\n-- Count admin role members\nSELECT COUNT(*) AS admin_count\nFROM [SHOW GRANTS ON ROLE admin];\n\n-- List all admin users\nSELECT member AS admin_user\nFROM [SHOW GRANTS ON ROLE admin]\nWHERE is_admin = true\nORDER BY member;\n```\n\nSee [SQL queries reference](references/sql-queries.md) for additional audit queries.\n\n### 2. Identify Over-Privileged Users\n\n**Admin role review:**\n```sql\n-- Admin users — each should have a documented reason for admin access\nSELECT member AS admin_user\nFROM [SHOW GRANTS ON ROLE admin]\nWHERE is_admin = true\nORDER BY member;\n```\n\nEvaluate each admin user:\n- **Keep admin:** Cluster operators, DBAs, automation accounts that genuinely need full access\n- **Downgrade:** Developers, analysts, application service accounts that only need specific permissions\n\n**PUBLIC role review:**\n```sql\n-- Check what PUBLIC can do (these apply to ALL users)\nSELECT\n  database_name,\n  schema_name,\n  object_name,\n  object_type,\n  privilege_type\nFROM [SHOW GRANTS FOR public]\nWHERE privilege_type NOT IN ('USAGE')\n  AND schema_name = 'public'\nORDER BY database_name, object_name;\n```\n\n**System privilege review:**\n```sql\n-- Users with sensitive system privileges\nSELECT grantee, privilege_type\nFROM [SHOW SYSTEM GRANTS]\nWHERE privilege_type IN (\n  'MODIFYCLUSTERSETTING',\n  'CANCELQUERY',\n  'CANCELSESSION',\n  'VIEWACTIVITY',\n  'CREATEDB',\n  'CREATELOGIN'\n)\nORDER BY privilege_type, grantee;\n```\n\n### 3. Create Purpose-Specific Roles\n\nReplace broad admin grants with targeted roles. Database-level grants in\nCockroachDB only support `CONNECT`, `CREATE`, `DROP`, `ZONECONFIG`, `BACKUP`,\n`RESTORE`, and `ALL` — data-access privileges (`SELECT`, `INSERT`, `UPDATE`,\n`DELETE`) live at the schema or table level. Pair `GRANT ... ON ALL TABLES IN\nSCHEMA` (covers existing tables) with `ALTER DEFAULT PRIVILEGES` (covers\nfuture tables created by the listed grantors) so new tables inherit the\nintended access.\n\n```sql\n-- Read-only role for analysts\nCREATE ROLE analyst_reader;\nGRANT CONNECT ON DATABASE <app_db> TO analyst_reader;\nGRANT USAGE ON SCHEMA <app_db>.public TO analyst_reader;\nGRANT SELECT ON ALL TABLES IN SCHEMA <app_db>.public TO analyst_reader;\nALTER DEFAULT PRIVILEGES IN SCHEMA <app_db>.public GRANT SELECT ON TABLES TO analyst_reader;\n\n-- Application service role (read + write, no DDL)\nCREATE ROLE app_service;\nGRANT CONNECT ON DATABASE <app_db> TO app_service;\nGRANT USAGE ON SCHEMA <app_db>.public TO app_service;\nGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <app_db>.public TO app_service;\nALTER DEFAULT PRIVILEGES IN SCHEMA <app_db>.public\n  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_service;\n\n-- Schema management role (DDL only)\nCREATE ROLE schema_manager;\nGRANT CREATE ON DATABASE <app_db> TO schema_manager;\n\n-- Monitoring role (read-only system visibility)\nCREATE ROLE monitoring;\nGRANT SYSTEM VIEWACTIVITYREDACTED TO monitoring;\n\n-- Operations role (triage + cancel, no data access)\nCREATE ROLE ops_triage;\nGRANT SYSTEM VIEWACTIVITYREDACTED, CANCELQUERY TO ops_triage;\n```\n\n### 4. Reassign Users to Purpose-Specific Roles\n\n```sql\n-- Assign users to their appropriate roles\nGRANT analyst_reader TO analyst_user;\nGRANT app_service TO payment_service, order_service;\nGRANT schema_manager TO migration_user;\nGRANT monitoring TO monitoring_user;\nGRANT ops_triage TO oncall_sre;\n```\n\n### 5. Revoke Excessive Grants\n\n**Revoke admin from users who no longer need it:**\n```sql\n-- Revoke admin from specific users\nREVOKE admin FROM analyst_user;\nREVOKE admin FROM payment_service;\nREVOKE admin FROM monitoring_user;\n```\n\n**Revoke PUBLIC role data grants:**\n```sql\n-- Revoke SELECT from PUBLIC on existing tables, plus the default-privilege grant\nREVOKE SELECT ON ALL TABLES IN SCHEMA <app_db>.public FROM public;\nALTER DEFAULT PRIVILEGES IN SCHEMA <app_db>.public REVOKE SELECT ON TABLES FROM public;\n\n-- Revoke all data privileges from PUBLIC on specific tables\nREVOKE ALL ON TABLE <sensitive_table> FROM public;\n```\n\n**Revoke unnecessary system privileges:**\n```sql\n-- Revoke system privileges from users who don't need them\nREVOKE SYSTEM MODIFYCLUSTERSETTING FROM <username>;\nREVOKE SYSTEM CREATEDB FROM <username>;\n```\n\n### 6. Verify Changes\n\n```sql\n-- Confirm admin count is reduced\nSELECT COUNT(*) AS admin_count FROM [SHOW GRANTS ON ROLE admin];\n\n-- Confirm PUBLIC privileges are minimal\nSELECT database_name, privilege_type\nFROM [SHOW GRANTS FOR public]\nWHERE privilege_type NOT IN ('USAGE');\n\n-- Verify specific user's effective privileges\nSHOW GRANTS FOR <username>;\n```\n\n**Application testing:** After revoking grants, verify that all applications still function correctly. Test:\n- Read operations (SELECT)\n- Write operations (INSERT, UPDATE, DELETE)\n- Schema operations (CREATE, ALTER, DROP) — only for schema management accounts\n- Connection and authentication\n\n## Safety Considerations\n\n**Revoking grants can break applications.** Applications that depend on admin, PUBLIC, or specific grants will fail with permission errors if those grants are revoked.\n\n**Mitigation steps:**\n1. **Audit before revoking:** Document which users/apps depend on which grants\n2. **Create replacement roles first:** Assign purpose-specific roles before revoking admin\n3. **Test in staging:** Revoke grants in a staging environment first and test all application flows\n4. **Revoke incrementally:** Revoke one user/grant at a time and test\n5. **Monitor for errors:** Watch application logs for permission-denied errors after changes\n\n**Do not revoke admin from:**\n- The last remaining admin user (you'll lose the ability to manage the cluster)\n- Automation accounts that manage schema migrations (unless you've created a schema_manager role)\n- The `root` user (built-in, cannot be revoked)\n\n## Rollback\n\nIf an application breaks after revoking a grant:\n\n```sql\n-- Re-grant admin (emergency)\nGRANT admin TO <username>;\n\n-- Re-grant specific privileges\nGRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA <app_db>.public TO <username>;\n\n-- Re-grant PUBLIC privileges\nGRANT SELECT ON ALL TABLES IN SCHEMA <app_db>.public TO public;\n```\n\n**Best practice:** Keep a record of all grants before revoking so you can restore them if needed:\n```sql\n-- Snapshot current grants before changes\nSELECT * FROM [SHOW GRANTS FOR <username>];\nSELECT * FROM [SHOW GRANTS FOR public];\nSELECT * FROM [SHOW SYSTEM GRANTS];\n```\n\n## References\n\n**Skill references:**\n- [SQL queries for privilege hardening](references/sql-queries.md)\n\n**Related skills:**\n- [auditing-cloud-cluster-security](../audit-cloud-security/SKILL.md) — Run a full security posture audit\n- [configuring-audit-logging](../configuring-audit-logging/SKILL.md) — Set up audit logging for privilege-sensitive operations\n\n**Official CockroachDB Documentation:**\n- [Authorization Overview](https://www.cockroachlabs.com/docs/stable/security-reference/authorization.html)\n- [GRANT](https://www.cockroachlabs.com/docs/stable/grant.html)\n- [REVOKE](https://www.cockroachlabs.com/docs/stable/revoke.html)\n- [CREATE ROLE](https://www.cockroachlabs.com/docs/stable/create-role.html)\n- [SHOW GRANTS](https://www.cockroachlabs.com/docs/stable/show-grants.html)\n- [System Privileges](https://www.cockroachlabs.com/docs/stable/security-reference/authorization.html#supported-privileges)\n"
}