hardening-user-privileges

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

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.

通过识别权限过高的用户、减少管理员授权、限制PUBLIC角色权限、创建特定用途角色并应用最小权限原则，审计并收紧CockroachDB的基于角色的访问控制（RBAC）。

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

减少拥有管理员角色的用户数量
移除PUBLIC角色的过度权限（SELECT、INSERT、UPDATE、DELETE）
创建特定用途角色以替代宽泛的管理员授权
响应关于过度权限的安全审计发现
为生产集群实施RBAC最佳实践
将集群纳入最小权限访问模型

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:

sql

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

SQL访问权限：拥有管理员角色（修改授权和角色成员身份所需）
用户清单：了解哪些用户/应用需要何种级别的访问权限
应用测试计划：撤销授权可能会破坏依赖这些权限的应用

检查你的访问权限：

sql

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

Steps

步骤

1. Audit Current Users and Roles

1. 审计当前用户和角色

sql

-- 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.

sql

-- 列出所有用户及其角色成员身份
SELECT
  username,
  options,
  member_of
FROM [SHOW USERS]
ORDER BY username;

-- 统计管理员角色成员数量
SELECT COUNT(*) AS admin_count
FROM [SHOW GRANTS ON ROLE admin];

-- 列出所有管理员用户
SELECT member AS admin_user
FROM [SHOW GRANTS ON ROLE admin]
WHERE is_admin = true
ORDER BY member;

查看SQL查询参考获取更多审计查询语句。

2. Identify Over-Privileged Users

2. 识别权限过高的用户

Admin role review:

sql

-- 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:

sql

-- 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:

sql

-- 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;

管理员角色审核：

sql

-- 管理员用户——每个用户都应有获得管理员访问权限的书面理由
SELECT member AS admin_user
FROM [SHOW GRANTS ON ROLE admin]
WHERE is_admin = true
ORDER BY member;

评估每位管理员用户：

保留管理员权限：集群操作员、数据库管理员（DBA）、确实需要完全访问权限的自动化账户
降级权限：仅需要特定权限的开发人员、分析师、应用服务账户

PUBLIC角色审核：

sql

-- 检查PUBLIC角色的权限范围（这些权限适用于所有用户）
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;

系统权限审核：

sql

-- 拥有敏感系统权限的用户
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

3. 创建特定用途角色

Replace broad admin grants with targeted roles:

sql

-- Read-only role for analysts
CREATE ROLE analyst_reader;
GRANT SELECT ON DATABASE <app_db> TO analyst_reader;

-- Application service role (read + write, no DDL)
CREATE ROLE app_service;
GRANT SELECT, INSERT, UPDATE, DELETE ON DATABASE <app_db> 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;

用针对性角色替代宽泛的管理员授权：

sql

-- 分析师只读角色
CREATE ROLE analyst_reader;
GRANT SELECT ON DATABASE <app_db> TO analyst_reader;

-- 应用服务角色（读写权限，无DDL权限）
CREATE ROLE app_service;
GRANT SELECT, INSERT, UPDATE, DELETE ON DATABASE <app_db> TO app_service;

-- 架构管理角色（仅DDL权限）
CREATE ROLE schema_manager;
GRANT CREATE ON DATABASE <app_db> TO schema_manager;

-- 监控角色（只读系统可见性）
CREATE ROLE monitoring;
GRANT SYSTEM VIEWACTIVITYREDACTED TO monitoring;

-- 运维分流角色（分流+取消权限，无数据访问权限）
CREATE ROLE ops_triage;
GRANT SYSTEM VIEWACTIVITYREDACTED, CANCELQUERY TO ops_triage;

4. Reassign Users to Purpose-Specific Roles

4. 将用户重新分配到特定用途角色

sql

-- 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;

sql

-- 将用户分配到对应的角色
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

5. 撤销过度授权

Revoke admin from users who no longer need it:

sql

-- 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:

sql

-- Revoke SELECT from PUBLIC on application databases
REVOKE SELECT ON DATABASE <app_db> FROM public;

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

Revoke unnecessary system privileges:

sql

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

从不再需要管理员权限的用户处撤销管理员角色：

sql

-- 从特定用户处撤销管理员权限
REVOKE admin FROM analyst_user;
REVOKE admin FROM payment_service;
REVOKE admin FROM monitoring_user;

撤销PUBLIC角色的数据权限：

sql

-- 撤销PUBLIC角色对应用数据库的SELECT权限
REVOKE SELECT ON DATABASE <app_db> FROM public;

-- 撤销PUBLIC角色对特定表的所有数据权限
REVOKE ALL ON TABLE <sensitive_table> FROM public;

撤销不必要的系统权限：

sql

-- 从不需要的用户处撤销系统权限
REVOKE SYSTEM MODIFYCLUSTERSETTING FROM <username>;
REVOKE SYSTEM CREATEDB FROM <username>;

6. Verify Changes

6. 验证更改

sql

-- 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

sql

-- 确认管理员数量已减少
SELECT COUNT(*) AS admin_count FROM [SHOW GRANTS ON ROLE admin];

-- 确认PUBLIC角色权限已最小化
SELECT database_name, privilege_type
FROM [SHOW GRANTS FOR public]
WHERE privilege_type NOT IN ('USAGE');

-- 验证特定用户的有效权限
SHOW GRANTS FOR <username>;

应用测试： 撤销授权后，验证所有应用仍能正常运行。测试内容包括：

读取操作（SELECT）
写入操作（INSERT、UPDATE、DELETE）
架构操作（CREATE、ALTER、DROP）——仅针对架构管理账户
连接与认证

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:

Audit before revoking: Document which users/apps depend on which grants
Create replacement roles first: Assign purpose-specific roles before revoking admin
Test in staging: Revoke grants in a staging environment first and test all application flows
Revoke incrementally: Revoke one user/grant at a time and test
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)

撤销授权可能会破坏应用。 依赖管理员权限、PUBLIC角色权限或特定授权的应用，在这些权限被撤销后会因权限错误而失败。

缓解措施：

撤销前审计： 记录哪些用户/应用依赖哪些权限
先创建替代角色： 在撤销管理员权限之前，先分配特定用途角色
在预发布环境测试： 先在预发布环境撤销权限并测试所有应用流程
逐步撤销： 一次只撤销一个用户/权限并进行测试
监控错误： 更改后查看应用日志中的权限拒绝错误

请勿从以下账户撤销管理员权限：

最后一位剩余的管理员用户（你将失去集群管理能力）
管理架构迁移的自动化账户（除非你已创建schema_manager角色）
```
root
```
用户（内置账户，无法撤销）

Rollback

回滚

If an application breaks after revoking a grant:

sql

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

-- Re-grant specific privileges
GRANT SELECT, INSERT, UPDATE ON DATABASE <app_db> TO <username>;

-- Re-grant PUBLIC privileges
GRANT SELECT ON DATABASE <app_db> TO public;

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

sql

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

如果撤销授权后应用出现故障：

sql

-- 重新授予管理员权限（紧急情况）
GRANT admin TO <username>;

-- 重新授予特定权限
GRANT SELECT, INSERT, UPDATE ON DATABASE <app_db> TO <username>;

-- 重新授予PUBLIC角色权限
GRANT SELECT ON DATABASE <app_db> TO public;

最佳实践： 撤销前记录所有授权，以便在需要时恢复：

sql

-- 在更改前快照当前授权
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:

技能参考：

权限强化SQL查询

相关技能：

auditing-cloud-cluster-security — 运行完整的安全态势审计
configuring-audit-logging — 为权限敏感操作设置审计日志

官方CockroachDB文档：